However, I'm experiencing some performance issues that I've tried to solve. So far I'm seeing a 50% reduction in runtime on my example.
I'm using Excel 2007 with only few real calculations and about 60 sparklines (BulletChart,RevBulletChart,LineChart).
The calculation time (F9) was originally around 8 secs which is now about 4 secs. Still quite painful...
It seems that it's still doing a lot of useless redrawing, so maybe something can be done about my formulas such that sparklines that have unchanged data are not redrawn all the time. Alternatively, I guess some kind of caching could be built into the Chart classes.
My change is quite simple and is contained to a few functions in the Utility module, see BELOW.
The optimization works by avoiding a lot of redundant iterations in the ShapeDelete function.
Kind regards,
Thomas
Public Sub ShapeDelete(rngSelect As Range)
' do nothing, i.e. neutralize the call from each Chart object
End Sub
' Renamed version of ShapeDelete - this is called from DrawCharts
Public Sub ShapeDelete(rngSelect As Range)
Dim rng As Range, shp As Shape, blnDelete As Boolean
For Each shp In rngSelect.Worksheet.Shapes
blnDelete = False
Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect.MergeArea)
If Not rng Is Nothing Then If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
If blnDelete Then shp.Delete
Next
Set rng = Nothing
End Sub
Sub DrawCharts()
Dim ...
' NEW code inserted here:
For i = colQueue.Count To 1 Step -1
Set obj = colQueue(i)
ShapeDeleteNew obj.Destination
Next
' Existing code continues here:
For i = colQueue.Count To 1 Step -1
Set obj = colQueue(i)
If TypeOf obj Is CascadeChartClass Then
Set ChtCascade = obj
On Error Resume Next
ChtCascade.DrawCascadeChart
...
...
...
Next
End Sub
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hmm, curious. You're using the same code to delete the shapes in a destination range, but now call it from DrawCharts instead of from the Chart classes, right? Weird that just calling it from somewhere else should make it run so much faster.
Then again, excel is weird and excel 2007 is very weird indeed. And dog slow when it comes to shapes.
BTW, which sparklines version are you using?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm using 3.0 - the only version released for 2007 (AFAIK). BTW, is it possible to use the newest code in 2007 by importing all the .cls, .bas files from Sparklines-xl2003-3.3_alpha2.zip?
Yeah, it's quite weird. However calling ShapeDelete from each class instance creates twice the number of iterations in the loop "For Each shp In rngSelect.Worksheet.Shapes".
When you do Draw, Delete, Draw, Delete, Draw, Delete,... you'll get N iterations per instance (N=number of shapes on the sheet - charts or other shapes).
If you do Delete, Delete, Delete, ..., Draw, Draw, Draw, ... you'll get N, N-1, N-2, ... iterations for each instance, i.e. N/2 on average, because each time you delete a shape there will be one less to iterate over.
In fact there's a (quite harmless) bug in ShapeDelete if you have manually drawn another shape *inside* the same cell as the chart shape: this shape will get deleted as well (not very likely).
I’ve also tried to delete the shapes by their name (thus having zero iterations) and even by a reference to their shape (kept in a Dictionary). This doesn’t really help, so I guess the reason that the optimization is working is more related to the order of Delete/Draw. Maybe some refresh method is being called in an inefficient way when doing it the “wrong” way…?
/Thomas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks for the explaination.
I did what you suggested, but I found that XL is less stable now ...
I a surprised that the dictonnary solution does not bring any help. I have a macro to list all sparklines in a sheet, that I will post here. It might give you some ideas.
Regarding the import of .bas files, it should work most the time
No heavy debbuging expected. The tricky part should lie in the Utility module as it calls other procedures
There is a message on the blog from Gustavo... he seems about to release a 2007 version of the add-in.. lets wait
Regards
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Oomph, that's old :)
Try Sparklines-xl2003-3.3_alpha2.zip. That version work ok on xl2007 although it won't install as an .xlam add-in. I haven't tried installing it on xl2007, but the code for the sparklines works on xl2007 afaik.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
First of all, thanks for a great tool.
However, I'm experiencing some performance issues that I've tried to solve. So far I'm seeing a 50% reduction in runtime on my example.
I'm using Excel 2007 with only few real calculations and about 60 sparklines (BulletChart,RevBulletChart,LineChart).
The calculation time (F9) was originally around 8 secs which is now about 4 secs. Still quite painful...
It seems that it's still doing a lot of useless redrawing, so maybe something can be done about my formulas such that sparklines that have unchanged data are not redrawn all the time. Alternatively, I guess some kind of caching could be built into the Chart classes.
My change is quite simple and is contained to a few functions in the Utility module, see BELOW.
The optimization works by avoiding a lot of redundant iterations in the ShapeDelete function.
Kind regards,
Thomas
Public Sub ShapeDelete(rngSelect As Range)
' do nothing, i.e. neutralize the call from each Chart object
End Sub
' Renamed version of ShapeDelete - this is called from DrawCharts
Public Sub ShapeDelete(rngSelect As Range)
Dim rng As Range, shp As Shape, blnDelete As Boolean
For Each shp In rngSelect.Worksheet.Shapes
blnDelete = False
Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect.MergeArea)
If Not rng Is Nothing Then If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
If blnDelete Then shp.Delete
Next
Set rng = Nothing
End Sub
Sub DrawCharts()
Dim ...
' NEW code inserted here:
For i = colQueue.Count To 1 Step -1
Set obj = colQueue(i)
ShapeDeleteNew obj.Destination
Next
' Existing code continues here:
For i = colQueue.Count To 1 Step -1
Set obj = colQueue(i)
If TypeOf obj Is CascadeChartClass Then
Set ChtCascade = obj
On Error Resume Next
ChtCascade.DrawCascadeChart
...
...
...
Next
End Sub
Thanks a lot for pointing this out Thomas,
I knew the damn ShapeDelete routine was slowing down the process...
One more thing to implement in the nex release
Feel free to share with us your improvements...
I give a try to your solution and let you know if any trouble
Hmm, curious. You're using the same code to delete the shapes in a destination range, but now call it from DrawCharts instead of from the Chart classes, right? Weird that just calling it from somewhere else should make it run so much faster.
Then again, excel is weird and excel 2007 is very weird indeed. And dog slow when it comes to shapes.
BTW, which sparklines version are you using?
nixnut_xl,
I'm using 3.0 - the only version released for 2007 (AFAIK). BTW, is it possible to use the newest code in 2007 by importing all the .cls, .bas files from Sparklines-xl2003-3.3_alpha2.zip?
Yeah, it's quite weird. However calling ShapeDelete from each class instance creates twice the number of iterations in the loop "For Each shp In rngSelect.Worksheet.Shapes".
When you do Draw, Delete, Draw, Delete, Draw, Delete,... you'll get N iterations per instance (N=number of shapes on the sheet - charts or other shapes).
If you do Delete, Delete, Delete, ..., Draw, Draw, Draw, ... you'll get N, N-1, N-2, ... iterations for each instance, i.e. N/2 on average, because each time you delete a shape there will be one less to iterate over.
In fact there's a (quite harmless) bug in ShapeDelete if you have manually drawn another shape *inside* the same cell as the chart shape: this shape will get deleted as well (not very likely).
I’ve also tried to delete the shapes by their name (thus having zero iterations) and even by a reference to their shape (kept in a Dictionary). This doesn’t really help, so I guess the reason that the optimization is working is more related to the order of Delete/Draw. Maybe some refresh method is being called in an inefficient way when doing it the “wrong” way…?
/Thomas
Thomas,
thanks for the explaination.
I did what you suggested, but I found that XL is less stable now ...
I a surprised that the dictonnary solution does not bring any help. I have a macro to list all sparklines in a sheet, that I will post here. It might give you some ideas.
Regarding the import of .bas files, it should work most the time
No heavy debbuging expected. The tricky part should lie in the Utility module as it calls other procedures
There is a message on the blog from Gustavo... he seems about to release a 2007 version of the add-in.. lets wait
Regards
Oomph, that's old :)
Try Sparklines-xl2003-3.3_alpha2.zip. That version work ok on xl2007 although it won't install as an .xlam add-in. I haven't tried installing it on xl2007, but the code for the sparklines works on xl2007 afaik.