This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time.
You may also want to read:
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)
|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.