-
Feb 28th, 2024, 05:13 AM
#1
Thread Starter
New Member
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|