The VBA OnTime Method : How to Set Up Scheduled Tasks
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.
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. (“
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.
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