VBA：如何真正停止Application.onTime()？

Question

我已经阅读了一些关于如何停止 VBA Application.OnTime 过程的问题/线程（例如 this 和 this），但我就是无法让它停止！

我使用它每隔x秒拉取一些数据。我知道当我调用 OnTime() 方法时，我需要传递与计划事件使用的相同的时间值。

我还尝试引入多个命令（例如尝试引起错误）来停止执行，但仍然不起作用！程序仍然在运行...这是我的代码：

在工作表代码中：

Public TimerActive As Boolean
Public tick As String
Public idx As Long
Public counter As Long

Public Sub UpdateOff_Click()

    TimerActive = False
    tick = "Off"
    counter = 1
    idx = 1
    
    Call StopTimer(idx, counter, tick, TimerActive)
    End ' force quit??

End Sub

Public Sub UpdateOn_Click()

    TimerActive = True
    tick = "live"
    counter = 1
    idx = 1
    
    Call StartTimer(idx, counter, tick, TimerActive)
    
End Sub

而在另一个模块中，我有：

Public fireTime As Date

Sub StartTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)

    fireTime = Now + TimeValue("00:00:05")
    sMacro = "  'pullData " & idx & " , " & counter & ", " & Chr(34) & tick & Chr(34) & ", " & TimerActive & "'"
    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=True

End Sub

Sub StopTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
   
    sMacro = "cause error" ' cause an error (by giving false sub name so program stops?

    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=False
    End

End Sub


Public Sub pullData(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
DoEvents
If TimerActive = True Then
    
    ' pull the data do some stuff, print the data, etc...

    idx = idx + 1
    counter = counter + 1
    tick = tick + " ."
    If counter = 6 Then
        counter = 1
        tick = "live"
    End If

    Call startTimer(idx, counter, tick, TimerActive)

End If

End Sub

我知道我可能增加了几个额外的措施来阻止执行，但似乎都没有奏效！

- rex

Answer 1

这是因为您的启动和停止过程引用了不同的宏。将sMacro定义为Public，然后它就会起作用。

Public sMacro As String
Public fireTime As Date

Sub startTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
    fireTime = Now + TimeValue("00:00:05")
    sMacro = "  'pullData " & idx & " , " & counter & ", " & Chr(34) & tick & Chr(34) & ", " & TimerActive & "'"
    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=True
End Sub

Sub StopTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=False
End Sub

- DaveU

---

谢谢DaveU，我会尝试并发布结果。 - rex

嗨，DaveU，谢谢，这只有在我还删除了StopTimer（）子程序内的sMacro重新定义后才起作用。当我按下停止按钮时，我的程序似乎仍然会抛出一些错误，但至少现在它可以停止了！谢谢！ - rex

为什么stoptimer有那么多参数？ - Patrick Lepelletier

您需要原始时间和宏名称来停止已经启动的“StartTime”。 - DaveU

Answer 2

这可以更简单地完成。为计时器名称使用一个固定的字符串，但确保在下次调用计时器的确切时间停止计时器。

Private NextCallTimer As Double
Sub CallRepeatingTimer()
   NextCallTimer = Now + TimeValue("00:00:05") 
   Application.OnTime EarliestTime:= NextCallTimer, Procedure:="MyTimer"
End Sub

Sub InterruptRepeatingTimer()
   Application.OnTime EarliestTime:= NextCallTimer, Procedure:="MyTimer", Schedule:=False
End Sub

- Rian Rizvi