Performance optimization

  • Thomas

    Thomas - 2009-02-16

    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,

    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
        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

        ' 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


    End Sub

    • Fabrice Rimlinger

      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

    • nixnut

      nixnut - 2009-02-18

      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?

      • Thomas

        Thomas - 2009-02-20


        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

        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…?


        • Fabrice Rimlinger


          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


        • nixnut

          nixnut - 2009-02-20

          Oomph, that's old :)
          Try 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.


Log in to post a comment.