Stopping the OnTime event

Question

I have come across posts which explain "To cancel a pending OnTime event, you must provide the exact time that it is scheduled to run".

Should I supply the time at which the event was run the very first time or should I supply the time at which the event will be triggered next?

I have tried both version of StopTimer. Both give me

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

I made changes to procedure TEST below and now I am using third version of STOPTIMER but my code gives me same error.

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

I've had an error on trying to stop the Ontime event, it was strange to me because I've had 3 timers, and 2 were working normal, and the third sometimes failed to stop. My observation was the following: the error appeared when I tried to call StopTimer from the sub which was scheduled/repetitive itself or from any subroutine which were called from initial scheduled sub. The StopTimer was working normally only if I called it from different sub. On TS example command to stop the scheduled sub "TheSub" is run from the body of "TheSub" itself. Here is my example:

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

When I called StopRepeat1 and StopRepeat2 from another subs - button with StopRepeat1 and StopRepeat2 or from userform 'close' button where I stopped subs together or one only - it stopped normally without error. So thus I've avoided an error, but I still don't know why I can't stop my Repeat1 from itself (because the only purpose of it - is to start the Repeat2, and when Repeat2 is started I don't need Repeat1 to continue running). Maybe the above information will be helpful for somebody.

Answer 2

To cancel the OnTime event, you need to advise the time at which it is scheduled to run.

Your first attempt was telling it to cancel a scheduled event that is no longer scheduled - it may have actually occurred several hours ago.

Your second attempt is telling it to cancel a scheduled event that is due to occur 5 seconds after you decided that you wanted to cancel the event. You might be lucky and have managed to decide to cancel it so soon after you set it that 5 seconds is the right time, but you probably won't be. (It depends on how accurate the clock is, and how fast your computer is executing the code.)

What you need to do is tell it to cancel the event with the same time you set it for, so your code needs to say:

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

That version will use the same time (runwhen) in the cancellation as was used when you set the time in the Test subroutine.

---

Update re new code:

The original version of your code would have worked (with version 3 of StopTimer), but your new version fails because you have changed it to set runwhen when you shouldn't.

Let's step through what is happening in the new version of the code. Assume that you open your workbook at 6:00:00 AM, and that your CPU is very slow so that we can assign various times to various events.

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)

The failure occurs because you update the value of runwhen (at 06:00:30.004 in my example), but then don't set the OnTime event. You then go to cancel the event but it isn't there to cancel.

You should set runwhen only when you are setting the OnTime event, and then you will be able to use that variable to cancel the event.

I recommend you change your entire code to be:

'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

If set out that way, you don't need a StopTimer subroutine, because you only start the timer the number of times you want it to run.

However, you are probably trying to design a system where the user will decide when to stop the timer, perhaps by some button click. If so, you would simply need to include in the button's code the following statement to stop the timer:

Application.OnTime runwhen, "TheSub", , False

Answer 3

Your code works fine on my machine. Here I use the second version of StopTimer sub-routine and put all of your codes in the standard code module. I think the culprit in your code is you don't declare the global variables:

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

on the top of your code module. Put them right below Option Explicitin order to make them work properly.

FWIW, I fix your first version of StopTimer sub-routine to make it work properly:

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

Now both of those sub-routines can be used interchangeably.