Timer Process (Excel Macro)

To start the process, use a procedure called StartProcess, similar to the code shown below.

Sub StartProcess()
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"),procedure:="Call_Timer", schedule:=True
End Sub


This stores the date and time 1 minute from the current time in the RunWhen variable, and then calls the OnTime method to instruct Excel when to run the Call_Timer procedure.

Since is a string variable containing "Call_Timer", Excel will run that procedure at the appropriate time. Below is a sample procedure:

Sub Call_Timer()
StartTime
End Sub

Note : that the last line of Call_Timer calls the StartProcess procedure. This reschedules the procedure to run again. And when the Call_Timer procedure is called by OnTime the next time, it will again call StartProcess to reschedule itself. This is how the periodic loop is implemented.

Below is a procedure called StopProcess which will stop the pending OnTime procedure.

Sub StopProcess()
On Error Resume Next
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"),procedure:=Call_Timer, schedule:=False
End Sub