VBA - Sparkline with Dynamic Range Not Updating in VBA

Question

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

Created a worksheet with a sparkline on it which references a dynamic range
I have a macro that, via a loop, updates some cells (and therefore changes the sparkline with each loop iteration)
Copies a large range (including the sparkline and other named ranges)
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!!!

BIRDSVIEW: If it works in debug mode then try this. Add `Doevents` so that it gets enough time to place the image in the clipboard and then paste it? — Siddharth Rout, Sep 11 '14 at 13:56
@SiddharthRout... Just tried it... No luck :( Any other ideas???? - This one's hurting!! — John Bustos, Sep 11 '14 at 14:11
OMG - @SiddharthRout... I TAKE THAT BACK!!!! - I put it in another location **AND IT WORKED** — John Bustos, Sep 11 '14 at 14:15
**YOU ARE MY HERO!!!** - Please just post it as a solution so I can give you credit!! - I can't tell you how much fristration you saved me!!! — John Bustos, Sep 11 '14 at 14:22

score 1 · Accepted Answer · answered Sep 11 '14 at 14:29

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.

VBA - Sparkline with Dynamic Range Not Updating in VBA

1 Answers1