With Application.OnTime, you can schedule a macro (a procedure) to run at a specific time in the future. This is especially useful for setting up scheduled tasks which you want your computer to kick-off while you’re away from your computer, such as night jobs.

OnTime is very similar to the Application.Wait method — with both you can design your procedure to be run at specific time. One major difference between OnTime and Wait is that for the Wait method, Excel’s operation will be suspended, but OnTime will not freeze your computer. You can continue to work on your Excel workbooks after running a OnTime statement.

Syntax

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

The OnTime method has 4 input parameters. Only EarliestTime and Procedure are madatory inputs.

Parameter	Mandatory / Optonal	Data Type of Parameter	Description
EarliestTime	Mandatory	Variant	The time when you want the Procedure to be run.   Sometimes Excel could be busy with other tasks (e.g. any macro is running) at the time of EarliestTime. The procedure will be kicked off as soon as Excel is ready. (See also the parameter LatestTime below.)
Procedure	Mandatory	String	Name of the macro to run at the EarliestTime.
LatestTime	Optional	Variant	The latest time at which the procedure can be run. If the current time exceeds this time, the procedure won’t be run (and the OnTime instruction will be considered as expired).
Schedule	Optional	Boolean (True or False)	Setting this optional parameter to False can cancel a previously scheduled task. By default, this is True.

Example 1: Schedule a macro to run 30 seconds from now

Application.OnTime Now + TimeValue("00:00:30"), "myMacro"

Example 2: Schedule a macro to run at 9 pm today

You can use OnTime to schedule a macro to be kicked off at a specific time during the day.

Application.OnTime TimeValue("21:00:00"), "myMacro"

This is a case which is very similar to the Wait method, with which you can pause a macro until a certain time. The macro “mywait” waits until 9pm and runs “myMacro”.

Sub mywait()
    Application.Wait "14:00:00" 'Wait until 2:30PM today
    myMacro
End Sub

Example 3: Schedule a macro to run at 2 am after midnight

We can also schedule a macro to be kicked off after midnight. The way you define the “EarliestTime” is similar to Example 1. We first define the time, which is midnight of the day after today, and then we add the hour and minutes.

Sub LateNightJob()
Dim startTime
    'this is the midnight time 0:00:00 at midnight
    startTime = DateSerial(Year(Now) + Month(Now), Day(Now) + 1)
    'add 2 hours to become 2am
    startTime = startTime + TimeValue("00:02:00")
    Application.OnTime startTime, "myMacro"
End Sub

Example 4: Cancel the scheduled task

After running an OnTime statement to create a schedule task, you can cancel it by running an OnTime statement again with identical values for EarliestTime and Procedure, but set the Schedule parameter to False.

The statement below cancels the OnTime scheduled task in Example 2.

Application.OnTime EarliestTime:=TimeValue("21:00:00"), _ 
 Procedure:="myMacro", Schedule:=False

Note there are two differences from Example 2:

• The parameter “Schedule” is included in Example 4. (It did not appear in Example 2, because by default “Schedule” is True.)
• The name of every parameter is included in the statement in Example 4. (“EarliestTime:=“, “Procedure:=” and “Schedule:=“) When parameters are input sequentially, the names can be omitted. In Example 4, we input parameters 1, 2 and 4, with 3 “LatestTime” omitted. In such a case, the names of the parameters must be included in the VBA statement.

Example 5 (Advanced): Essential approach to refresh Bloomberg formulas in VBA

In the financial industry, especially for roles related to trading of financial instruments, Excel users use Bloomberg formulas in their Excel spreadsheets.

Bloomberg formulas retrieve data feeds from Bloomberg’s server into the workbook, and such communication takes time. If we run our macros in the normal way, the macros may encounter unexpected fatal errors because the cells with Bloomberg formulas may have not yet been fully refreshed. We need to find a way to pause the macros and wait for the data feed to complete before proceeding.

99.99% of VBA developers (even the most professional pros) think of using the VBA Calculate method to force the spreadsheet to refresh, e.g. with one of the 3 approaches below:

ActiveSheet.Calculate      'Force calculate the entire sheet
'or
Range("A1:D10").Calculate  'Re-calculate a specific range with formula
'or
Application.Calculate      'Re-calculate all workbooks

However, this simply won’t always work, especially if there are plenty of Bloomberg formulas to refresh. This is because there is external data communication to the Bloomberg servers and it takes time for all the Bloomberg formulas to be refreshed.  

When BBG formulas are pending refresh, they show “#N/A Requesting Data…” (see picture below) for a while. The delay for all the cells to be updated ranges from a few seconds to a few minutes, depending on the amount of data you’re requesting.

This means that your macro needs to be intelligent enough to wait for all the Bloomberg formulas in your worksheet/workbook to be fully refreshed before proceeding.

The Solution

In order to achieve this, here I’m introducing the “proper” approach in VBA, using the “OnTime” method.

The macro “run_part1” below is the macro you kick-off. That If-Then statement fires a little function to test whether the ActiveSheet has been fully refreshed. If yes (True), it’ll proceed to do the work inside the IF-Then statement. If no (False), it will schedule to re-calculate the ActiveSheet again in 5 seconds.

Sub run_part1()
If calc_sheet = True Then
    'Here do what you want to do after the sheet has been calculated
End If
End Sub

'This function returns TRUE when Activesheet is completely calculated
'If FALSE, will schedule to calculate again after 5 seconds
Function calc_sheet() As Boolean
Dim tmp
Dim c As Object
tmp = False
ActiveSheet.Calculate
ActiveSheet.Calculate
With ActiveSheet.UsedRange
    Set c = .Find("request", LookIn:=xlValues)
    If Not c Is Nothing Then
        Application.OnTime Now + TimeValue("00:00:05"), "run_part1"
        tmp = False
    Else
        tmp = True
    End If
End With
calc_sheet = tmp
End Function

Related articles: VBA Wait Method