VBA:如何真正停止Application.onTime()? [英] VBA: How do I really stop Application.onTime()?

问题描述

[已解决]:请查看解决方案的答案.谢谢@DaveU.

[SOLVED]: Please see answers for solution. Thanks @DaveU.

我已经阅读了一些问题/线索(例如此或此)有关如何停止VBA Application.OnTime程序，但是我无法停止它！

I have already read some questions/threads (eg this or this) about how to stop the VBA Application.OnTime procedure, but I just can't get it to stop!

我正在使用它每x秒提取一些数据.我了解，当我调用OnTime()方法时，我需要将用于安排事件的相同时间值传递给它.

I am using it to pull some data every x seconds. I understand that when I call the OnTime() method I need to pass to it the same time value that was used to schedule the event.

我也试图引入多个命令(例如试图引起错误的命令)来停止执行，但是它仍然不起作用！该程序一直在运行...这就是我的代码的样子:

I have also tried to introduce multiple commands (that try to cause an error for example) to stop the execution but it still doesn't work! The program just keeps running... This is how my code looks:

在工作表代码中，我有:

In Worksheet code I have:

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

在一个单独的模块中，我有:

and in a seperate module i have:

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

我知道我可能已经引入了一些额外的措施来停止执行，但似乎没有一个起作用！

I understand that I may have introduced a few extra measures to stop the execution but none seem to work!

推荐答案

这是因为您的开始&停止例程引用了不同的宏.将sMacro定义为Public，然后它将起作用

It's because your start & stop routines are referring to different macros. Define sMacro as Public, then it will work

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

这篇关于VBA:如何真正停止Application.onTime()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！