excelvbascheduled-tasksrtdontime

Button to stop subs with OnTime Macro doesn't stop


I have a button assigned to the StopRecordingData sub to cancel both subs and it doesn't. The schedule False doesn't seem to cancel the scheduled subs in que.

Dim NextTime As Double

Sub RecordData()
    Dim Interval As Double
    Dim cel As Range, Capture As Range

    Application.StatusBar = "Recording Started"
    Set Capture = Worksheets("Dashboard").Range("C5:K5") 'Capture this row 
    of data
    With Worksheets("Journal") 'Record the data on this worksheet
    Set cel = .Range("A2") 'First timestamp goes here
    Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
    cel.Value = Now
    cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Capture.Value
    End With
    NextTime = Now + TimeValue("00:01:00")
    Application.OnTime NextTime, "CloseWB"
End Sub

Sub CloseWB()
    Application.OnTime NextTime, "RecordData"
    ThisWorkbook.Close True
End Sub

Sub StopRecordingData()
    Application.StatusBar = "Recording Stopped"
    Application.OnTime NextTime, "RecordData", False
    Application.OnTime NextTime, "CloseWB", False
End Sub

Solution

  • You must either

    as the syntax of OnTime method has 4 arguments and last 2 are optional.

    Application.OnTime EarliestTime, Procedure, [LatestTime], [Schedule]

    Also, for time related variables, prefer DATE over DOUBLE. Hence use this at the top.

    Dim NextTime as Date
    

    Sub StopRecordingData()
        Application.StatusBar = "Recording Stopped"
        Application.OnTime NextTime, "RecordData",, False
        Application.OnTime NextTime, "CloseWB",, False
    End Sub
    

    Sub StopRecordingData()
        Application.StatusBar = "Recording Stopped"
        Application.OnTime EarliestTime:=NextTime, Procedure:="RecordData", Schedule:=False
        Application.OnTime EarliestTime:=NextTime, Procedure:="CloseWB", Schedule:=False
    End Sub