If you need Excel to run some VBA at a specific time, or repeatedly at set intervals, you can use the Application.OnTime method.
A basic call to Ontime requires that you supply a time when you want the code to run, and the name of the macro you want to run.
The argument EarliestTime is called this because Excel will actually execute the Procedure no earlier than EarliestTime, but possibly later.
There's no guarantee that your scheduled macro will run exactly when you want because Excel may be busy doing something else.
It may be executing some other VBA, or you may be entering data into a sheet (Enter mode). If Excel is not in Ready, Copy, Cut or Find mode, execution of your scheduled macro may be delayed.
There are two optional arguments, LatestTime and Schedule.
As we've just seen, Excel may not run your macro exactly at the time you want. By specifying LatestTime, we're giving Excel a window, between EarliestTime and LatestTime, during which you want it to run your VBA.
If your scheduled code is delayed, and Excel is only ready to execute the macro after LatestTime, then it won't run it at all.
The value of Schedule indicates whether your are setting a task (True), or cancelling one (False).
The default value is True so there's no need to specify Schedule unless you are cancelling a previously set task.
If you want to cancel an OnTime task once you've scheduled it, you can do so like this
Cancelling OnTime requires that you specify the name of the macro, and exact time it is set to run. This means you need to store both of these somewhere.
Excel doesn't provide any way for you to check what macros are scheduled to run, so you must keep track of these things yourself.
The easiest way would be to store the values in public variables/constants, or on a worksheet. Although you could store them in the registry if you want.
Download Example Workbook
Enter your email address below to download the sample workbook.
Running Code at Set Intervals
If you want to repeatedly run the same macro at set periods you make the macro call itself.
First, write the macro that sets the OnTime schedule, I'm calling it SetOnTime. This macro will set a schedule for a macro called MyCode.
Then write the MyCode macro, which has the code you want to execute, and a call to SetOnTime.
When you run SetOnTime it sets the OnTime call, which when it runs, calls MyCode.
MyCode then calls SetOnTime again, and so on ........
Specifying the Time
Time in Excel is a funny old thing and often causes a lot of confusion.
If you understand the way time is stored as serial number you can set an OnTime task like this
5 Seconds from Now
1 Hour from Now
1 Day from Now
TimeValue : 1 hour 15 mins From Now
TimeSerial : 45 seconds From Now
If you want to set a macro to run at a specific time, use Timevalue.
TimeValue : Run at 8.30pm.
Storing Time in a Public Constant
Of course if you are setting something to run at intervals you can use a public constant and store the interval in that.
I've declared a public constant called Interval and set it to 5.
By using TimeSerial I can set the interval that the macro runs at to every 5 seconds.
Multiple OnTime Tasks
You can set multiple tasks to run using OnTime. But remember that you need to keep a record of them if you want to be able to programatically unschedule them.
Running a Macro from a Closed Workbook
If you schedule a macro and then close the workbook containing the macro, Excel will try to open that workbook before running the macro.
In this scenario, if the workbook is not in a trusted location, you may find that macros in the workbook are disabled, and the scheduled macro will not run.
When Scheduled Tasks Won't Run
A scheduled macro won't run if the Excel application is closed.
A scheduled task will not execute in break mode. If you are debugging your VBA, like stepping through code, or a VBA routine has caused an error and you have started to debug it, scheduled tasks won't execute and you'll get an error telling you so.
If another VBA routine has caused an unhandled error and halted, scheduled tasks won't execute until that error is acknowledged and Ended.