Search code examples
vbaexcelsparklines

VBA - Sparkline with Dynamic Range Not Updating in VBA

I have a question extremely similar to this one, where I have:

  1. Created a worksheet with a sparkline on it which references a dynamic range
  2. I have a macro that, via a loop, updates some cells (and therefore changes the sparkline with each loop iteration)
  3. Copies a large range (including the sparkline and other named ranges)
  4. Pastes this range in a second sheet (I couldn't figure out how to do it any other way, so I'm using the Range.CopyPicture method and pasting the range as a picture to the second sheet, but if there's a way you know to paste the sparkline itself such that it won't be updated when I update the raneg again, PLEASE let me know)

Now, my problem is that if I step through the code in debug mode, everything works out well, BUT if I let the macro run automatically, the sparkline never gets updated (so it always looks like it did the first time the macro runs).

As a simple example of my problem, suppose I have this in my worksheet:

Cells A1-A10: 
      1,2,3...,10 (what I'll use for the sparkline range)

Cell Named "StartCol": 
      =RANDBETWEEN(1,9)

Cell Named "Width":    
      =RANDBETWEEN(1,10-StartCol)

Dynamic range Named "SparkRange":  
      =OFFSET(Sheet1!$A$1,0,StartCol-1,1,Width)

Cell With a SparkLine within a larger range Named "ToCopy"

A Second Sheet Named "OutPut" with a named range "Output" where we'll start pasting to

Now, simplistic VBA code:

Sub Test()
    Sheets("Output").Select

    For i = 1 To 10
        Application.Calculate   ' Probably not needed, but putting it in to be safe
        Sheets("Sheet1").Range("ToCopy").CopyPicture

        Sheets("Output").Range("Output").Select
        Sheets("Output").Paste
        Sheets("Output").Names("output").RefersTo = Sheets("Output").Range("Output").Offset(Sheets("Sheet1").Range("ToCopy").Rows.Count + 2)
    Next i
End Sub

Now, this SHOULD paste in 10 different sparklines, but it rather pastes 10 copies of the same one. AND If I step through it in debug mode (or even set a breakpoint and then hit F5 in VBA, then it works!) Again, I looked at the question mentioned above and tried its tricks to no avail.... Any one know how to get this to work for sparklines????

Thanks SO MUCH!!!

Solution

  • When you are continuously doing a repetitive exercise which involves excel/system to process multiple events then it is advisable to separate them using DoEvents.

    For example in your case, you need to give enough time for Excel to copy the image into Clipboard and then paste it back.

    For example

    Sheets("Sheet1").Range("ToCopy").CopyPicture

Doevents '<~~ This gives enough time for the pic to be placed in Clipboard

Sheets("Output").Paste

DoEvents '<~~ This gives enough time for the pic to be pasted

    In more technical terms, DoEvents surrenders execution of the macro so that the operating system can process other events. The DoEvents function passes control from the application to the operating system.

    CAUTION: Judicious use of DoEvents is preferred else you will end up slowing your code unnecessarily.