VBA Stop OnTime Function

Question

Could someone please help me how to stop the OnTime Function? It doesnt work the way I tried it.

Option Explicit

Dim iTimerSet As Double

Public Sub FXKurs_Wahl()
    iTimerSet = Now + TimeValue("00:05:00")
    Application.OnTime iTimerSet, "FXKurs_Wahl"
End Sub


Public Sub EndeUhr()        
    On Error Resume Next
    Application.OnTime iTimerSet, "FXKurs_Wahl", , False    
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)            
    Call EndeUhr    
End Sub

Thank you!

Answer 1

As stated in the documentation of the Application.OnTime method

Set Schedule to False to clear a procedure previously set with the same Procedure and EarliestTime values.

Syntax is .OnTime(EarliestTime, Procedure, LatestTime, Schedule)

So if you used

Application.OnTime iTimerSet, "FXKurs_Wahl"

to start it you need to use

Application.OnTime EarliestTime:=iTimerSet, Procedure:="FXKurs_Wahl", Schedule:=False

to stop it. Note that iTimerSet needs to be the same value for both.

Make sure that if you run FXKurs_Wahl multiple times you would need to delete multiple OnTime procedures. Also make sure there is no other code manupulating the iTimerSet value.

---

If it can occur to run FXKurs_Wahl multiple times try removing the old one before adding a new OnTime

Public Sub FXKurs_Wahl()
    EndeUhr 'remove the old one before setting a new one
    iTimerSet = Now + TimeValue("00:05:00")
    Application.OnTime iTimerSet, "FXKurs_Wahl"
End Sub