Quest: Use the Speedtest and make it go faster, than it does right now.
Info:
I have a chart of the type Column. (My Pc is quiete fast.)
Each month in a 4 year period is represented in the chart multiplied by 2 because there are two different modes. Making up 8 Series for each Data displayed. (This is kinda inevitable)
There are about 25 different Data displayed this way and filtered by the user using buttons using the Is.Filtered funciton of the chart in a smart way for nice displaying.
[The Chart]
(https://i.sstatic.net/XWxYAWBc.png)
Problem: The Chart is too slow adding all the Data.
Question1: Is there a way to disable the chart from reloading all the time when adding a new series?
Question2: Is it possible to define the SeriesCollection beforehand and then add it in once, causing the chart to also only refresh once?
What have I tried:
- Application.EnableEvents = False, ScreenUpdate and other don’t speed up the process at all
- I tried creating a new SeriesCollection, or copying one from a existen chart and paste it in to another but I guess you can’t do Chart1.SeriesCollection = Chart2.SeriesCollection. It says it is not supported. Using
set
for the new variables makes it connect to the chart keeping it reloading - I tried on the creation of the new series to filter it instantly. This only takes even longer
Here is a debug function that adds series to a chart for speed testing my result is about 650ms:
Sub TestDiagramPerformance()
With Tabelle2.ChartObjects("TestDiagram").Chart
'Delete ALL Data (For Retesting)
For Each s In .FullSeriesCollection: s.Delete: Next s
Call DebugTimer(True, "Test") 'Start Timer
'Add Series
For fi = 1 To 250:
.SeriesCollection.NewSeries
.SeriesCollection(fi).XValues = 500 'THIS TAKES TOO LONG!
.SeriesCollection(fi).Values = 10 'THIS TAKES TOO LONG!
Next
End With
Call DebugTimer(False, "Test") 'Finish Timer and display Time
End Sub
'This is the DebugTimer (works Fine)
'Public Sub DebugTimer(StartOrEnd As Boolean, Optional AdditionalName = 1, Optional Priority = 1)
'If ReadName("DebugSettings_IsTimerEnabled") = False Then Exit Sub
'If ReadName("DebugSettings_EnabledTimerPriorities") < Priority Then Exit Sub
' Name = "DebugTimer_" & AdditionalName
' Select Case StartOrEnd
' Case True
' Application.Names.Add _
' Name:=Name, _
' RefersTo:=Timer 'No Procces Time Since is Last Line
' Case False
' nv = Application.Names(Name).Value
' n = Split(Mid(nv, 2, Len(nv) - 1), ".")
' If (UBound(n) > 0) Then
' XN = n(0) & "," & n(1)
' Debug.Print Name, Format(((Timer - XN)) * 1000, "#####.## ms")
' Else
' Debug.Print "An Error Occured in the DebugTimer"
' End If
' Application.Names(Name).Delete 'Process Time consuming circa 4 seconds on my device
' End Select
'End Sub
Oliver Hildebrandt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.