Results 1 to 1 of 1

Thread: Chart Object does not load data

Threaded View

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    1

    Chart Object does not load data

    I calculate some data and using them later in this code snippet to create a chart. It worked completly fine until i changed the line weight from 3 to the calculation it is now. Reversing the change didn't help. The problem is that the chart does not have the data shown and gives an error when pointing on any data in the seriescollections like when creating the box at the bottom of the code. This can manuelly resolved by right clicking into the chart and "Select Data" and selecting another data series, but this process can't be done by VBA.
    Refresh and DoEvents do not help. Since this problem also occurs on other Charts with differenct Code, I assume it has something to do with the nature of data source in the chart. Changing all data to be double or integer variables doesn't help. Also it sometimes still work and sometimes not.

    The Code is just for Reference, but the pictures are more descriptive in this matter. The same problem is also when creating 3 bars in a bargraph with just 3 data but not given as range but as array.

    Code:
    Dim Punkte() As Double
    Dim AnzahlZwischenräume As Byte, Zwischenraum As Double
    Dim Gruppengrößenprozent_kum() As Double
    Dim Genauigkeit As Double
    Genauigkeit = 80
    ReDim linien_1(1 To Genauigkeit)
    ReDim linien_2(1 To Genauigkeit)
    ReDim Punkte(6)
    ReDim Gruppengrößenprozent_kum(3) 'ubound() is equal 2
    ReDim linien_p(1 To Genauigkeit)
    ReDim linien_g(1 To Genauigkeit)
    Zwischenraum = 5
    Gruppengrößenprozent_kum(UBound(Gruppengrößenprozent_kum)) = 0
    
    'prozu(2,2) is an Array from previous calculations
    'but for testing i give values:
    prozu(0,0) = 39
    prozu(0,1) = 16
    prozu(0,2) = 23
    prozu(1,0) = 16
    prozu(1,1) = 7
    prozu(1,2) = 9
    prozu(2,0) = 25
    prozu(2,1) = 17
    prozu(2,2) = 8
    'vgl(2) also
    'vgl(0) = 80
    'vgl(1) = 40
    'vgl(2) = 40
    
    For i = 1 To Genauigkeit
        linien_1(i) = Array(i, i, i)
        linien_2(i) = Array(none, none, 0, 0, 0, 0)
        linien_p(i) = 2
    Next i
    
    AnzahlZwischenräume = 2
    
    For g = 2 To 1 Step -1
    'in the end it looks like this:
    '  Gruppengrößenprozent_kum(1): 1
    '  Gruppengrößenprozent_kum(2): 0.5
    '  Gruppengrößenprozent_kum(3): 0
    
        Gruppengrößenprozent_kum(g) = Gruppengrößenprozent_kum(g) + vgl(g) / vgl(0) 'vgl(x) are just Integers from previous calculations
        
        For gg = g - 1 To 1 Step -1
        
            Gruppengrößenprozent_kum(gg) = Gruppengrößenprozent_kum(gg) + Gruppengrößenprozent_kum(g)
        
        Next gg
    
    Next g
    
    ii = 1
    For g = 1 To 2
        For p = 0 To 2
            For i = ii To ii + rund(prozu(p, g) / vgl(0) * Genauigkeit, 0) - 1
                If Genauigkeit - i + 1 > 0 Then linien_p(Genauigkeit - i + 1) = p
            Next i
            ii = i
        Next p
    Next g
    
    
    For g = 2 To 1 Step -1
                
        For i = rund(Genauigkeit * Gruppengrößenprozent_kum(g), 0) To rund(Genauigkeit * Gruppengrößenprozent_kum(g + 1) + 1, 0) Step -1
            
            
          
            linien_1(i)(2) = linien_1(i)(0) + rund((UBound(nameGruppe) + 1) / 2 - g, 0) * Zwischenraum
            linien_g(i) = g
            linien_2(i)(2) = linien_1(i)(2)
            linien_2(i)(3) = linien_2(i)(2)
            
            versatz = 0
            If g = 1 Then
                For p = 0 To linien_p(i) - 1
                    versatz = versatz + prozu(p, 2)
                Next p
            ElseIf g = 2 Then
                For p = linien_p(i) + 1 To UBound(prozu, 1)
                    versatz = versatz + prozu(p, 1)
                Next p
            End If
            versatz = rund(versatz / vgl(0) * Genauigkeit, 0)
            
            linien_2(i)(4) = linien_1(i)(0) + rund(UBound(prozu) / 2 - linien_p(i), 0) * (Zwischenraum + versatz) + (rund(UBound(prozu) / 2 - linien_p(i), 0) = 0) * (versatz + Zwischenraum / 2) * Choose(g, 1, -1)
            linien_2(i)(5) = linien_2(i)(4)
            
            
            
        Next i
        
    Next g
    
    
    
    
    Dim Form As Shape
    Dim Diagramm As Chart
    Dim Reihensammlung As SeriesCollection
    Dim Reihe As Series, lab As Shape
    Dim Box As Shape, TF As Shape
    
    Set Form = ausgabe.Shapes.AddChart(xlLine, 200, 100, 600, 300)
    
    Set Diagramm = ausgabe.ChartObjects(ausgabe.ChartObjects.Count).Chart
    
    With Diagramm
    
        Set Reihensammlung = .SeriesCollection
        
        For Each Reihe In Reihensammlung
            Reihe.Delete
        Next Reihe
        
        weig = CInt(rund(3 / Genauigkeit * 100, 0))
        
        With Reihensammlung
            
            For i = 1 To Genauigkeit
                Set Reihe = .NewSeries
                
                With Reihe
                    
                                                 
                    .Values = linien_1(i)
                     
                    
                    
                    .Format.Line.ForeColor.RGB = Choose(linien_g(i), RGB(75, 210, 80), RGB(100, 210, 80))
                    
                    .Format.Line.Weight = weig
                    .Smooth = True
                    
                End With 'Reihe
                
                
                Set Reihe = .NewSeries
                
                With Reihe
                    
                                                 
                    .Values = linien_2(i)
                                              
                
                    .Format.Line.ForeColor.RGB = Choose(linien_p(i) + 1, RGB(100, 200, 0), RGB(130, 200, 50), RGB(100, 200, 100))
                    
                    .Format.Line.Weight = weig
                    .Smooth = True
                    
                End With 'Reihe
                
                
            Next i
            
           
           
          
        End With 'Reihensammlung
        
        
        .Legend.Delete
        .Axes(xlCategory).Delete
        .Axes(xlValue).Delete
        .Axes(xlValue).MajorGridlines.Delete
        
        'Box code where first error occurs because the data is not there
        With Reihensammlung
        
            Set Box = Diagramm.Shapes.AddShape(msoShapeRectangle, .Item(1).Points(1).Left - 10, .Item(2 * Genauigkeit - 1).Points(1).Top - 3, 10, .Item(1).Points(1).Top - .Item(2 * Genauigkeit - 1).Points(1).Top + 6)
            
        End With
    End With
    So I would appreciate your help with this matter on how to fix this even when most likely the problem does not occur on your devices.

    Thanks in advance
    Sofi
    Attached Images Attached Images    

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width