停止OnTime事件

Question

我遇到过这样的帖子:"要取消一个OnTime事件，你必须提供它计划运行的确切时间"。

应该提供事件第一次运行的时间，还是应该提供下一次触发事件的时间?

我试过两个版本的StopTimer。都给我

object _Application failed的OnTime方法

Option Explicit
Private Sub Workbook_Open()
    count = 1
    Call test
End Sub

Public runwhen As Double
Public Const runwhat As String = "TheSub"
Public firstrunTime As Double
Public count As Integer

Sub test()
    If count = 1 Then
        runwhen = Now + TimeSerial(0, 0, 5)
        firstrunTime = runwhen
    Else
        runwhen = Now + TimeSerial(0, 0, 5)
    End If
    Application.OnTime runwhen, "TheSub"
End Sub

Sub TheSub()
    MsgBox "Hi!!!!!!"
    count = count + 1
    Call test
    If count = 5 Then
        StopTimer
    End If
End Sub

'First Version of StopTimer
Sub StopTimer()
    Application.OnTime firstrunTime, "TheSub", , False
End Sub

'Second Version of StopTimer
Sub StopTimer()
    runwhen=now+TimeSerial(0,0,5)
    Application.OnTime runwhen, "TheSub", , False
End Sub

我对下面的过程TEST进行了更改，现在我使用的是STOPTIMER的第三个版本，但我的代码给了我同样的错误。

Sub test()
    If count = 1 Then
        runwhen = Now + TimeSerial(0, 0, 5)
        firstrunTime = runwhen
    Else
        runwhen = Now + TimeSerial(0, 0, 5)
    End If
    If count <> 5 Then
        Application.OnTime runwhen, "TheSub"
    Else
        Call StopTimer
    End If
End Sub

Answer 1

要取消OnTime事件，需要通知该事件计划运行的时间。

您的第一次尝试是告诉它取消一个不再安排的计划事件-它可能实际上发生在几个小时前。

您的第二次尝试是告诉它取消预定的事件，该事件将在您决定要取消事件后5秒发生。您可能很幸运，并且在设置后很快就决定取消它，所以5秒是正确的时间，但您可能不会。(这取决于时钟有多精确，以及计算机执行代码的速度。)

你需要做的是告诉它取消事件与你设置的相同的时间，所以你的代码需要说:

'Third Version of StopTimer
Sub StopTimer()
    Application.OnTime runwhen, "TheSub", , False
End Sub

该版本将在取消中使用与您在Test子例程中设置时间时使用的相同的时间(runwhen)。

---

更新新代码:

您的代码的原始版本将工作(与StopTimer版本3)，但您的新版本失败，因为您已将其更改为设置runwhen时，你不应该。

让我们逐步了解新版本的代码中发生了什么。假设您在早上6:00:00打开工作簿，并且您的CPU非常慢，因此我们可以为不同的事件分配不同的时间。

06:00:00.000 - Workbook opens
06:00:00.001 - Subroutine Test is called
06:00:00.002 - Count is 1, so first If statement executes the first section
06:00:00.003 - runwhen is set to 06:00:05.003
06:00:00.004 - firstruntime is set to 06:00:05.003
06:00:00.005 - Count is 1, not 5, so second If statement executes the first section
06:00:00.006 - OnTime is set to run TheSub at 06:00:05.003
06:00:00.007 - Subroutine Test finishes and control returns to TheSub
06:00:00.008 - Count is 1, not 5, so If statement is not executed
06:00:00.009 - Subroutine TheSub finishes and execution of macro stops
06:00:05.003 - OnTime event triggers
06:00:05.004 - Subroutine TheSub is called
06:00:05.005 - MsgBox is displayed
               The user is very slow to press the button this time. (Mainly because I had
               written a lot of the following times, and then realised my Count was out
               by 1, and I didn't want to have to rewrite everything - so I just added
               a very slow response here.)
06:00:12.000 - User presses OK
06:00:12.001 - Count is set to 2
06:00:12.002 - Subroutine Test is called
06:00:12.003 - Count is 2, not 1, so first If statement falls into Else portion
06:00:12.004 - runwhen is set to 06:00:17.004
06:00:12.005 - Count is 2, not 5, so second If statement executes the first section
06:00:12.006 - OnTime is set to run TheSub at 06:00:17.004
06:00:12.007 - Subroutine Test finishes and control returns to TheSub
06:00:12.008 - Count is 2, not 5, so If statement is not executed
06:00:12.009 - Subroutine TheSub finishes and execution of macro stops
06:00:17.004 - OnTime event triggers
06:00:17.005 - Subroutine TheSub is called
06:00:17.006 - MsgBox is displayed
06:00:18.000 - User presses OK
06:00:18.001 - Count is set to 3
06:00:18.002 - Subroutine Test is called
06:00:18.003 - Count is 3, not 1, so first If statement falls into Else portion
06:00:18.004 - runwhen is set to 06:00:23.004
06:00:18.005 - Count is 3, not 5, so second If statement executes the first section
06:00:18.006 - OnTime is set to run TheSub at 06:00:23.004
06:00:18.007 - Subroutine Test finishes and control returns to TheSub
06:00:18.008 - Count is 3, not 5, so If statement is not executed
06:00:18.009 - Subroutine TheSub finishes and execution of macro stops
06:00:23.004 - OnTime event triggers
06:00:23.005 - Subroutine TheSub is called
06:00:23.006 - MsgBox is displayed
06:00:24.000 - User presses OK
06:00:24.001 - Count is set to 4
06:00:24.002 - Subroutine Test is called
06:00:24.003 - Count is 4, not 1, so first If statement falls into Else portion
06:00:24.004 - runwhen is set to 06:00:29.004
06:00:24.005 - Count is 4, not 5, so second If statement executes the first section
06:00:24.006 - OnTime is set to run TheSub at 06:00:29.004
06:00:24.007 - Subroutine Test finishes and control returns to TheSub
06:00:24.008 - Count is 4, not 5, so If statement is not executed
06:00:24.009 - Subroutine TheSub finishes and execution of macro stops
06:00:29.004 - OnTime event triggers
06:00:29.005 - Subroutine TheSub is called
06:00:29.006 - MsgBox is displayed
06:00:30.000 - User presses OK
06:00:30.001 - Count is set to 5
06:00:30.002 - Subroutine Test is called
06:00:30.003 - Count is 5, not 1, so first If statement falls into Else portion
06:00:30.004 - runwhen is set to 06:00:35.004
06:00:30.005 - Count is 5, so second If statement executes falls into the Else portion
06:00:30.006 - Subroutine StopTimer is called
06:00:30.007 - Code attempts to cancel Ontime event scheduled for 06:00:35.004 (the value of runwhen),
               but fails because no such event is scheduled)

发生失败是因为您更新了runwhen的值(在我的示例中为06:00:30.004)，但随后没有设置OnTime事件。然后你去取消事件，但它不存在。

当你设置OnTime事件时，你应该只设置runwhen ，然后你将能够使用该变量来取消事件。

我建议您将整个代码更改为:

'In your Workbook module
Option Explicit
Private Sub Workbook_Open()
    count = 1
    Call StartTimer
End Sub
'In your main code module
Option Explicit
Public runwhen As Double
Public count As Integer
Sub TheSub()
    MsgBox "Hi!!!!!!"
    count = count + 1
    Call StartTimer
End Sub
Sub StartTimer()
    If count <> 5 Then
        runwhen = Now + TimeSerial(0, 0, 5)
        Application.OnTime runwhen, "TheSub"
    End If
End Sub

如果以这种方式设置，则不需要StopTimer子例程，因为您只需要启动计时器的次数。

然而，你可能正在尝试设计一个系统，用户将决定何时停止定时器，也许通过一些按钮点击。如果是这样，您只需要在按钮的代码中包含以下语句来停止计时器:

Application.OnTime runwhen, "TheSub", , False

Answer 2

您的代码在我的机器上运行良好。这里我使用第二个版本的StopTimer子例程，并将所有代码放在标准代码模块中。我认为你代码中的问题是你没有声明全局变量:

Public runwhen As Double
Public Const runwhat As String = "TheSub"
Public firstrunTime As Double
Public count As Integer

在你的代码模块的顶部。把它们放在Option Explicit的正下方，以使它们正常工作。

FWIW，我修复了你的第一个版本的StopTimer子程序，使其正常工作:

Sub StopTimer()
    On Error Resume Next
    Application.OnTime runwhen, "TheSub", , False
End Sub

现在这两个子例程可以互换使用了

Answer 3

我在试图停止Ontime事件时出现了错误，这对我来说很奇怪，因为我有3个计时器，2个正常工作，第三个有时无法停止。我的观察如下:当我试图从预定/重复本身的子程序或从初始预定子程序调用的任何子程序中调用StopTimer时，出现了错误。只有当我从不同的子程序调用它时，StopTimer才正常工作。在TS示例中，命令停止预定的sub"thessub"是从"thessub"本身的主体运行的。下面是我的例子:

public nextTime1 as Date
public nextTime2 as Date
sub StartTimer()
   call Repeat1 'the first scheduled sub
end sub
sub StopRepeat1
   Application.OnTime nextTime1, "Repeat1", , False
end sub
sub StopRepeat2
   Application.OnTime nextTime2, "Repeat2", , False
end sub
sub Repeat1()
 nextTime1 = Now + TimeSerial(0, 0, 5)
 if condition = true then
   call Repeat2
   call StopRepeat1 '< here gives error because I try to stop this sub from itself
 end if 
 Application.OnTime nextTime1, "Repeat1"  
end sub
sub Repeat2()
  nextTime2 = Now + TimeSerial(0, 0, 1)
  call StopRepeat1 '< also gives error because this sub Repeat2 was called from Repeat1, and thus I also try to stop Repeat1 from itself
  'do something
  Application.OnTime nextTime2, "Repeat2"
end sub

当我调用StopRepeat1和StopRepeat2从另一个subs按钮StopRepeat1和StopRepeat2或从用户表单的"关闭"按钮，我停止subs一起或只有一个-它正常停止，没有错误。因此，我避免了一个错误，但我仍然不知道为什么我不能停止我的Repeat1本身(因为它的唯一目的-是启动Repeat2，当Repeat2启动时，我不需要Repeat1继续运行)。也许以上信息会对某人有所帮助。