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.
Application.OnTime
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.
Syntax
Application.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
The Application.OnTime method has 4 parameters which includes 2 optional inputs:
| 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 | The latest time at which the procedure can be run. If this argument is omitted, Excel will wait until the procedure can be run. |
| Schedule | Optional | Variant |
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
