Nrthugu

I'm trying to animate a parametric curve in Excel by generating its ranges in an iterative loop. I'm using the code:

Public Sub playswirl()

    Range([a8], [a8].End(xlDown)).Clear

    Set cell = Range("a8")

    Do While cell.Offset(0, 1) <> ""

       cell.Offset(-1, 0).Copy cell

       ActiveSheet.ChartObjects(2).Chart.Refresh

       DoEvents

       Sleep (50)

       Set cell = cell.Offset(1, 0)

    Loop

End Sub

The chart is cleared in the first instruction, then rebuilt one item at a time in the loop. While the code is running, I see the values in the spreadsheet changing one at a time, but the chart never updates (I see the full curve, i.e. the state before the first line of code is executed). When I ctrl-break and put the code in debug state, the chart updates to the point where the code was interrupted.
I thought that the chart.refresh would have done the trick - and added the doevents in for good measure when that didn't work - but no luck. Changing the sleep call to the Excel-native Application.Wait call doesn't help either (and is too slow in any case).
Any ideas?

I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:

• Try adding the "DoEvents" to your loop like explained here.


• Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.

Let me know how those go.