vba - 在运行宏时更新动态图表 - Excel 中的动画图表

Question

我有一个动态图表，可以根据单元格值更新每小时数据。该单元格由滚动按钮控制或在另一个单元格中手动输入日期。看看下面的 gif 以获得洞察力；



我想要做的是使用 VBA 更新图表以使其像动画一样。让我们在下面有这个(伪)代码；

Option Explicit

#If VBA7 And Win64 Then
    '64 bit Excel
    Public Declare PtrSafe Sub Sleep Lib "kernel32" ( _
        ByVal dwMilliseconds As Long)
#Else
    '32 bit Excel
    Public Declare Sub Sleep Lib "kernel32" ( _
        ByVal dwMilliseconds As Long)
#End If

Sub Animate()

On Error GoTo Error ErrorHandler    

Dim wsh As Worksheet
Dim i As Integer

Set wsh = ThisWorkbook.Worksheets("AChart")   
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

For i = 1 to 3000
    Sleep 50
    wsh.Range("K4").Value = i
    DoEvents
    Application.Wait Now() + TimeSerial(0, 0, 1)
Next i

Exit Sub
ErrorHandler:
MsgBox Err.Description & " Procedure Animated Graph"

End Sub

这(特别是 DoEvents )不会使屏幕上的图形更新。我想知道是否有办法实现这一点。

Answer 1

Application.OnTime 可用于此目的。其他类似 DoEvents或 Sleep仍然会运行宏，并且会阻止更新图表(在屏幕上)。
如何制作动态图？
首先，为了完整起见，这是一个如何制作动态图的链接:
Dynamic Excel Charts
在这里，您将学习引用单元格并使用偏移量(或类似方法)来更改图表上显示的数据。
如何自动更改图表？
下面的代码是我早期尝试自动更新图表数据(所谓的“动画”)的尝试之一。 Start和 Finish是两个 Command Buttons在表格中，用户可以通过他们控制“动态图”。你可以玩弄Pause , 等等，但这会给你一个关于如何实现这个的原始想法:

Public laststop As Boolean
Public i As Integer

Sub start()
    laststop = True 'This controls schedule in Application.Ontime

    i = 1 'Reset the date to the first entry each time you press start
    Call Animate 'calls the main sub
End Sub

Sub finish()
    laststop = False 'Set the schedule to false to stop the macro
End Sub

'XXXXXXXXXXXXXXXXXXXX
'XXX MAIN PROGRAM XXX
'XXXXXXXXXXXXXXXXXXXX

Sub Animate()

On Error GoTo ErrorHandler

Application.OnTime Now() + TimeSerial(0, 0, 2), "Animate", Schedule:=laststop

If Not laststop Then GoTo nextline

Dim wsh As Worksheet

Set wsh = ThisWorkbook.Worksheets("Dynamic_Graph")
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

wsh.Range("I34").Value = i + 1
DoEvents

i = i + 1

If i > 200 Then laststop = False 'last data entry
   

Exit Sub

ErrorHandler:

   'Sometimes after running the finish macro these errors are happening that _
   'we don't want users to see them as they don't affect the process

If Err.Description = "The specified dimension is not valid for the current chart type" _
Or Err.Description = "Method 'OnTime' of object '_Application' failed" _ 
Then GoTo nextline

MsgBox Err.Description & " #Procedure: Animated Graph"

nextline:
         laststop = False

End Sub

感谢 @A.S.H建议 OnTime .