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.

Download Workbook

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