Can't refresh a chart in Excel VBA (Excel 2016)

Question

My son (15 y) is studying pendulum at school, physics lessons. I am trying to help him with a simulated pendulum in Excel, using 2 points chart, one fix (where the "cord" of the pendulum is attached, one moving in time (the pendulum).

Computations are ok, but I can't figure out why the chart is not refreshing: the "pendulum" does not move... I read the multiple posts on stack overflow over refreshing an Excel chart, but none of them could help me solving it... Any idea?

Here is the code (OK, I am not a IT pro...!)

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Const g = 9.80665 'gravity constant
Const PI = 3.14159265358979

Sub TestSimple()

'Declare variables
Dim l 'lenght of pendulum
Dim Tô1 'start angle
Dim Tôi 'angle of pendulum at time i
Dim x 'coordonnates of pendulum on x axis
Dim y 'coordonnates of pendulum on y axis
Dim Omega 'Sqr(g / l)
Dim t 'time
Dim s, phi


'-1- Initialization of variables
l = 0.7   'length is 70 cm
Omega = Sqr(g / l)
Tô1 = PI / 6 '30 degrees at start

'**** NOTE ****
'Equation of pendulum angle in function of time is
'Tô(t)=Tô1*cos(Wo.t) were Wo = sqrt(g/l) = Omega
'**************


'Calculation of time needed for 1 calculation
s = Timer
t = 2
Tôi = Tô1 * Cos(Omega * t)
x = l * Sin(Tôi)
y = 1 - l * Cos(Tôi)

'paste x and y value into spreadsheet
Worksheets(1).Range("A3").Value = x
Worksheets(1).Range("B3").Value = y
'refresh chart
ActiveSheet.ChartObjects("Chart 1").Chart.Refresh

phi = Timer - s


'-2- Simulation for 10 seconds
For t = 0 To 10 Step phi

    'position of pendulum in function of time
        Tôi = Tô1 * Cos(Omega * t)
        x = l * Sin(Tôi)
        y = 1 - l * Cos(Tôi)

    'paste x and y in spreadsheet
    Worksheets(1).Range("A3").Value = x
    Worksheets(1).Range("B3").Value = y
    'refresh of chart

    ActiveSheet.ChartObjects("Chart 1").Chart.Refresh

    Sleep (50) 'let machine sleeps for 50 milliseconds, for a smoother look
Next t


End Sub

score 1 · Accepted Answer · answered Feb 08 '17 at 12:57

1

Sounds like a DoEvents problem. Just put DoEvents in your code like so:

...
ActiveSheet.ChartObjects("Chart 1").Chart.Refresh
DoEvents
Sleep (50)
...

answered Feb 08 '17 at 12:57

Preston

7,399
8
54
84

@FredC No worries, don't forget to hit the green tick next to the answer. Hope it helps your son! – Preston Feb 09 '17 at 13:43

score 0 · Answer 2 · edited Aug 10 '20 at 07:36

It's 2020 and am running Excel 2016 for MAC. I put this subroutine in my code (combined various items I found on the web) and got the chart to update each time the sub is called (be sure to define a cell in your worksheet as "stepper" so the counter data can be stored and updated). My chart was auto-named "chart 8" by excel:

Sub ChartAnimation3()
  Dim i As Double
  For i = 0 To 1000 Step 500
    ActiveSheet.Range("Stepper") = i
    DoEvents
    DoEvents
  Next

ActiveSheet.ChartObjects("Chart 8").Chart.Refresh
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Refresh
DoEvents

End Sub

There might be some items not needed in this code, but I'm a novice, and it worked, so I am satisfied.

Can't refresh a chart in Excel VBA (Excel 2016)

2 Answers2

Linked