Programmers love to make things more efficient. Pushing buttons just to make a code run is particularly annoying, especially when the code is supposed to run at the same time every time. Fortunately, Excel makes scheduled macro automation simple with the Application.OnTime method.
- The Application.OnTime Method
- Scheduling the Macro
- Specifying the Macro
- Automation and Recursion
- Continuity and Canceling
The Application.OnTime Method
The Application.OnTime method is part of the Application object, which means it lives inside Excel’s main application. If you’re using Intellisense, when you type
Application.OnTime, you should see this:
Application.OnTime(EarliestTime, Procedure, [LatestTime], [Schedule])
Notice there are four arguments, two of which are optional:
EarliestTimeis the time you want your specified macro to start running
Procedureis the name of the macro you want to call, which can be in the same workbook or another workbook
LatestTimeis optional, and it specifies the last time the macro can be run. It puts a limit or deadline on the latest time Excel will attempt to start the macro, and if the deadline is missed, the macro is not executed
Scheduleis used for canceling previously scheduled OnTime macros
Scheduling the Macro
There are many ways to schedule your macros to run using the OnTime method. The most common ways are via specific times (1am, 11:45, 19:30) or a time relative to the existing time. You can also make other scheduling decisions, like scheduling a macro to run relative to a future date based on some user input.
Again, the argument
If a task must be run every day at the same time, it makes sense to apply the specific time technique. Let’s say you have to run a macro every day at 7am before you come into the office. You would execute a code snippet like this to make sure your macro runs before you even arrive:
Application.OnTime "07:00:00", "early_morning_task"
In this example,
You could also use the
TimeSerial function or the CDate function if you were grabbing your times from user-input variables:
h = CInt(InputBox("What hour do you want to schedule the macro?")) m = CInt(InputBox("What minute do you want to schedule the macro?")) Application.OnTime TimeSerial(h, m, 0), "early_morning_task"
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
Notice the use of
CInt to ensure the value going into
TimeSerial is an integer. Also, note that this setup uses 24-hour format, and you will need to account for that by explicitly forcing users to use 24-hour formats, making an educated guess (programmatically!), or by offering the option of entering AM/PM and calculating the 24-hour format of the input.
You can also schedule macros to run at relative times. The most common way is to schedule it relative to the current time. You would do this by using the
Now function to grab the current time then adding some amount of time to it.
DateAdd function is a good option if you want to add two times together, and it plays well with the
Now function. The following snippet fires off the macro
nseconds = CInt(InputBox("How many seconds should elapse before running the macro?")) Application.OnTime DateAdd("s", nseconds, Now), "macro_to_schedule"
If you’re scheduling over longer time periods, you can change the “s” to an “m” for minutes or “h” for hours and adjust the prompt.
Remember that a macro is executed line-by-line, so if the
InputBox opens at 19:33:09 and the user waits 10 seconds then enters 15, the macro will fire at 19:33:34, not at 19:33:24.
Specifying the Macro
When you only have a single workbook, you can reference the macro directly by using its name. To write more robust code, you can specify the module to ensure no future changes to the workbook, like adding another module with an identically-named macro, will cause issues.
Application.OnTime "19:45:30", "Module2.macro_to_schedule"
In this snippet, we are running the
Application.OnTime line runs in
Scheduling Macros in Other Workbooks
Believe it or not, you can extend the Application.OnTime method to other workbooks, even if they have identically-named macros. I have plenty of workbooks that have subroutines named
Keep in mind that
Application.OnTime lives at the application level and schedules there, so you can run the OnTime method from one workbook and it will execute macros in another workbook as long as you properly specify the name of the other workbook. A call to a workbook named
Application.OnTime "03:03:03", "Other Workbook.xlsm!Module1.main"
Don’t forget that Excel uses
! to separate the workbook name from the Modules and Sheets but uses
. to separate the Module/Sheet from the macro.
Scheduling Macros in Closed Workbooks
This extends even further to closed workbooks. Since the scheduling happens at the application level, as long as the application is open, the scheduled macro should execute. A user can close all of the workbooks but leave the application running and it will be fine. In other words, all the workbooks can be closed but Excel must remain open for the OnTime method to operate.
To call a macro in a closed workbook, you need to supply the full filepath to the workbook. Other than this, it’s exactly the same as scheduling a macro in another workbook:
Application.OnTime Now + TimeSerial(0, 0, 3), "C:\Work Files\Other Workbook.xlsm!Module1.main"
Important: the workbook will open and try to execute the macro. If security settings are such that the opened workbook is not trusted, the macro cannot run until someone clicks “Enable Macros”. Make sure you set the workbook to be trusted before using this method or you won’t get much automation benefit.
Automation and Recursion
If you run the scheduler once, you will get a single scheduled event. However, if you need to run the macro with the scheduler to schedule it every day, you just shifted the burden from running the macro directly to running the scheduler.
There are times this is useful, like when you cannot determine programmatically when you will need the macro, but you will know before the end of the day and it must be run overnight. In that case, you could run the scheduling macro before you go home only on days you need it.
But if you know your macro will run every day no matter what, you can actually automate the scheduling itself. The best way to do this is write a recursive subroutine, which is a subroutine that calls itself.
There are two common ways to do this: call the scheduler macro from the task macro or roll everything into one and make the scheduler macro the same as the task macro.
This is a code block using two separate subroutines, which makes breaking them apart easier:
Sub scheduler() Application.OnTime "05:00:00", "task_sub" End Sub Sub task_sub() a = 5 b = 6 c = 7 MsgBox (a + b + c) scheduler End Sub
This will run
This method calls the scheduling method
Application.OnTime at the end of the macro, although you can make it the beginning or anywhere in between, if you’d like. The main drawback is you must run the full task every time. It also violates the etiquette of breaking code into more readable chunks with specific purposes. But nevertheless, it works just as well.
Sub task_sub_second_method() a = 5 b = 6 c = 7 MsgBox (a + b + c) Application.OnTime "05:00:00", "task_sub_second_method" End Sub
Continuity and Canceling
Application.OnTime to work, the Excel instance in which it was run must remain open. It is possible to open more than one instance of Excel, and that may be useful if you use Excel often and habitually close the main window. If the instance is closed, all scheduled information will be lost - even if Excel is reopened. This is very important to remember.
If you need to run a macro at a certain time each day even if Excel is closed, you’ll have to call the macro from a file, like a .vbs file, and schedule your macro using the Task Scheduler.
There is also the possibility that a scheduled task must be canceled. This can be done by quitting the instance of Excel in which it was created, but doing this will nullify all scheduled tasks. What if you only wanted to cancel one schedule macro? To keep the Excel instance open and schedule a particular task, the exact time and name of the scheduled subroutine must be used. Then the
This code block has a scheduler macro and a cancellation macro:
Sub schedule_macro() Application.OnTime "05:00:00", "task_sub" End Sub Sub cancel_macro() Application.OnTime "05:00:00", "task_sub", , False End Sub
To cancel a scheduled
Sub cancel_macro2() Application.OnTime EarliestTime:="05:00:00", Procedure:="task_sub", Schedule:=False End Sub
Because exact times are important, and sometimes different users might enter different times, you will need to store these scheduled times somewhere. One obvious place is in a cell that won’t be overwritten. Another, but more involved option, is to write the time and macro name variables to a simple text file. A third, even more involved option, is to write custom properties for the workbook in which the scheduler resides. However you do it, it is a good idea to store the times somewhere or risk having to close Excel to cancel a macro you “lost.”
Automation is great, but only if the code is stable and what you expected to happen actually happens. If your application is meant to run a macro at a certain time every day and you plan to automate it with recursion, it is very important that Excel remains stable. That could mean manual garbage collection, learning about Windows and how the operating system interacts with Excel, and error trapping.
In fact, error trapping is extremely important for rapid automation in VBA. If an application is designed to run every 5 minutes and there is no error trapping, you’re almost guaranteed to come back to a run-time error or useless output. This becomes particularly important when no humans are monitoring the program. Computers won’t fix themselves, so you’ll need a way to alert humans. One way to do this is to send yourself an email once an error is encountered.
Don’t neglect stability and error trapping, especially in unattended automation scenarios. Alert humans to the errors.
Application.OnTime makes scheduling macros easy, as long as Excel remains open. Since scheduling inherently involves some future time, the OnTime method can be called and your macro will run later. This makes recursion easy because the code is not executed immediately like most other VBA macros. That means automation becomes easy, and that’s the point of writing macros, right? It’s certainly one of the most prominent uses of
The most important caveats are that 1. Excel, the application, cannot be closed 2. cancelation requests require the exact naming and time used to schedule the task to properly cancel the task. 3. code and macros must be stable for long-term, reliable automation 4. error trapping is not to be dismissed
If you address these caveats satisfactorily, scheduling and automation in Excel using Application.OnTime can open many, many opportunities.
I hope you’ll take a minute to subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.