VBA: How do I really stop Application.onTime()?

Question

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

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 separate 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!

Tremann

Answer 1

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

Spatter

Thanks DaveU, I will try this out and post results. – Tremann

Hi DaveU, thanks, this only worked once I also removed the redefinition of sMacro inside the StopTimer() sub. My program does seem to still be throwing some errors when i hit the stop off button but at least it stops now! Thanks! – Tremann

why does stoptimer have all those arguments ? – Squelch

You need the original time & macro name to stop a StartTimethat you've already started. – Spatter

Answer 2

This can be done more simply. Use a fixed string for the timer name, but ensure you stop the timer at the exact time it will be called next.

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

Fetiparous