Schedule a macro or subroutine to run later

OnTime Event

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

This event has four parameters:

1. EarliestTime
   The time when you want the procedure to be run. You can use the TimeValue and DateValue functions to specify time.
2. Procedure
   The name of the procedure (or macro) to run when the event occurs.
3. LatestTime
   It is an optional parameter, again you can use the TimeValue and DateValue functions to specify time.
   If the procedure could not run for some reason, Excel will try to run it again after the given time (the LatestTime). If this argument is omitted, Excel will wait until the procedure can be run.
4. Schedule (optional)
   You can clear a previously set procedure by assigning the False to this argument.

Example: Schedules a macro / procedure to be run at a specified time

To execute a procedure/macro in Excel after a specific time, we’ll use onTime, Now and the TimeValue function. First, press Alt + F11 to open the VBA, choose Insert > Module from the main menu to insert a new standard module and enter the following code in the module:

Option Explicit

'This macro can be used to schedule anything
Sub Alert()
 MsgBox "Testing OnTime Event"
End Sub

The above procedure needs to be called only once. You can call it from the Workbook_Open event when your workbook is first loaded. Open ThisWorkbook object code module and enter the following code to call the Alert macro at a specified time:

Option Explicit

Private Sub Workbook_Open()
 'The Alert procedure will execute at 9:00:30PM
 Dim sTime as Variant
 sTime = TimeValue("21:00:30")
 Application.OnTime EarliestTime:=sTime, Procedure:="Alert"
End Sub

Example: Schedules a macro / procedure to be run relative to the current time

For example, you want to run a procedure after 5-minute of opening the workbook:

Option Explicit

Private Sub Workbook_Open()
 Dim sTime As Variant
 sTime = Now + TimeValue("00:05:00")
 Application.OnTime EarliestTime:=sTime, Procedure:="Alert"
End Sub

Example: Run a macro on a specified date and time

The following example will run the Alert macro on November 14, 2020 at 11:18:45 PM.

Private Sub Workbook_Open()
 'Run a macro on a specified date and time
 Dim sTime As Variant
 sTime = DateValue("11/14/2020") + TimeValue("23:18:45")
 Application.OnTime EarliestTime:=sTime, Procedure:="Alert"
End Sub

Example: Run the macro every five minutes

If you want to run a macro on a regular basis, you can make the macro run itself as follows. Write the following code in the standard code. This code schedules a macro to run every five minutes:

Option Explicit
Dim sTime As Variant
Sub Alert()
 'Set a time interval
 sTime = Now + TimeValue("00:05:00")
 'Perform your task here
 MsgBox "The Alert macro will run again at " & sTime
 'Use OnTime method to schedule this procedure to run again
 Application.OnTime EarliestTime:=sTime, Procedure:="Alert"
End Sub
Sub CancelAlert()
  Application.OnTime EarliestTime:=sTime, Procedure:="Alert", Schedule:=False
 End Sub

Once you run Alert macro, it will keep scheduling itself to run every five minute. In order to stop the Alert macro, you need to know the scheduled time, so the module-level variable sTime is used to store the latest scheduled time. CancelAlert macro sets the Schedule parameter to False to cancel the scheduled run of Alert macro.

Using LatestTime Parameter

The OnTime method runs only when Excel is in following modes:

• Ready
• Copy
• Cut
• Find

If you start to edit a cell at 16:50:00 and keep that cell in Edit mode, Excel cannot run the Alert macro at 16:55:00, as directed. The Alert macro will run as soon as when Excel returned back to Ready mode. You can provide Excel with a window of time within which to make an update by using the LatestTime parameter:

Option Explicit
Dim sTime As Variant
Sub Alert()
 sTime = Now + TimeValue("00:05:00")
 MsgBox "The Alert macro will run again at " & sTime
 Application.OnTime EarliestTime:=sTime, Procedure:="Alert", LatestTime:=sTime + TimeValue("00:00:60")
End Sub