With Application.OnTime method, you can set Excel to run some VBA at a specific time, or repeatedly at set intervals. In this guide, we’re going to show you how to run macro at a specified time in Excel.
VBA’s Application.OnTime method is the direct answer of the topic. The method allows you to schedule a procedure to be run at a specified time in the future. The time can be either a specific time of day or a specific amount of time will pass.
Application.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
The Application.OnTime method has 4 parameters which includes 2 optional inputs:
|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||The latest time at which the procedure can be run. If this argument is omitted, Excel will wait until the procedure can be run.|
True to schedule a new OnTime procedure.
False to clear a previously set procedure.
The default value is True.
Examples to run macro at a specified time
Schedule a macro to run at 1 pm today
Supply 2 arguments to run the OnTime method in its basic form. To set a macro to run at a specific time, enter the time in TimeValue method as a string.
Application.OnTime TimeValue("13:00:00"), "BestMacroEver"
Schedule a macro to run 5 minutes from now
If would like to run a macro after a specific amount of time has passed, add the amount of time to the Now command, which returns the current time.
Application.OnTime Now + TimeValue("00:05:00"), "BestMacroEver"
Schedule a macro to run after midnight
To schedule a macro at a specific time after midnight, you have to specify the next day’s date. You can use the DateSerial method by adding 1 to the current day to populate next day’s date. The following code triggers the macro BestMacroEver after 5 minutes from midnight.
Application.OnTime DateSerial(Year(Now) + Month(Now), Day(Now) + 1) + TimeValue("00:05:00"), "BestMacroEver"
Running a code at set intervals
You can call OnTime method recursively to run a macro at set intervals. The following code makes subroutine CallMeMaybe call itself with 5-minute intervals, until 5 pm.
Sub CallMeMaybe() ‘Enter you code here Application.OnTime Now + TimeValue("00:05:00"), "CallMeMaybe", TimeValue("17:00:00") End Sub
Cancel the scheduled task
Set the Schedule argument as False to cancel a scheduled task. You can use their names to specify arguments to skip optional argument LatestTime.
Application.OnTime EarliestTime:=TimeValue("13:00:00"), Procedure:="BestMacroEver", Schedule:=False