无法在Excel VBA中刷新图表（Excel 2016） [英] Can't refresh a chart in Excel VBA (Excel 2016)

问题描述

我的儿子（15岁）在学校学习摆锤和物理课程。我正在尝试使用2点图，一个修正（附加钟摆的绳索，一个随时间变化的钟摆）在Excel中模拟摆锤，以帮助他。

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).

计算还可以，但是我不知道为什么图表没有刷新：摆锤没有移动...我在刷新Excel图表时阅读了堆栈溢出的多个帖子，但没有其中的一个可以帮助我解决它...任何想法吗？

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?

这是代码（好的，我不是IT专业人员...！）

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

推荐答案

听起来像 DoEvents 问题。只需像这样将DoEvents放入代码中即可：

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

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

这篇关于无法在Excel VBA中刷新图表（Excel 2016）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！