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