Excel VBA Application.OnTime Method to schedule event with timer

This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time.

You may also want to read:

Auto Open Excel with Windows Task Scheduler

Excel VBA Application.OnTime Method to trigger event at specified time

You can do the followings with Excel VBA Application.OnTime Method

– schedule a procedure to be run at a specified time in the future

– schedule a procedure to be run after a specific amount of time

– run a procedure repeatedly at specific time interval

Application.OnTime Method only works when Excel is opened, therefore it is best to work with Windows Task Scheduler, which can open Excel at specific time. You can write a Macro to auto execute Application.OnTime when Excel is opened.

Syntax of Application.OnTime Method

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Name	Required/Optional	Data Type	Description
EarliestTime	Required	Variant	The time when you want this procedure to be run.
Procedure	Required	String	The name of the procedure to be run.
LatestTime	Optional	Variant	If a Procedure is running and prevent another scheduled Procedure to run, Excel waits until the previous Procedure is complete. You can set the maximum waiting time when the scheduled Procedure do not run.
Schedule	Optional	Variant	True to schedule a new OnTime procedure. False to cancel a previously set procedure. The default value is True.

Example of Application.OnTime Method

Assume that you have written a Procedure called my_Procedure.

The following  code run my_Procedure after 10 seconds.

Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"

The following  code run my_Procedure at 1PM.

Application.OnTime TimeValue("13:00:00"), "my_Procedure"

Run a Procedure repeatedly

The above examples demonstrate how to run a Procedure once, but to run a Procedure infinitely, we need to loop the Application.OnTime Method in my_Procedure.

First create a my_Procedure to show the current time in A1.

Sub my_Procedure()
   Range("A1") = Format(Now(), "yyyy mmm d, hh:mm:ss")
End Sub

Then create another Procedure called my_onTime, this Procedure run my_Procedure every second.

Sub my_onTime()
    Application.OnTime Now + TimeValue("00:00:1"), "my_Procedure"
End Sub

Finally insert my_onTime in my_Procedure, so that two Procedures call each other

Sub my_Procedure()
    Range("A1") = Format(Now(), "yyyy mmm d, hh:mm:ss")
    my_onTime
End Sub

Now you have created a clock in Range A1, which updates current time every second, press Ctrl+Break to stop both Procedure.

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff196165.aspx

 

Excel VBA Application.OnTime Method to schedule event with timer