1

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?

plantrob
  • 33
  • 5

1 Answers1

0

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.

Parker.R
  • 88
  • 8
  • Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem. – plantrob Nov 11 '18 at 14:37