excel - 停止 OnTime 事件

Question

我遇到过解释“要取消挂起的 OnTime 事件，您必须提供它计划运行的确切时间”的帖子。

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

我已经尝试了两个版本的 StopTimer。两个都给我

Method OnTime of object _Application failed

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 事件。然后您去取消该事件，但它不能取消。

你应该设置 runwhen 仅限 当您设置 OnTime 事件时，您将能够使用该变量来取消事件。

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

'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