Microsoft Excel —

Run a Macro at a Set Time

Many times it would be great to run a macro at a predetermined time or at specified intervals. Fortunately, Excel provides a VBA method that makes this possible.

  1. Schedules a procedure to be run at a specified time
  2. Clear Schedules
  3. Use Windows Task Scheduler to open a workbook at a specified time

Application.OnTime

Application.OnTime Time Procedure Schedule

The Application.OnTime method can make macros run automatically, once you've done some setup. This method required at-least two arguments:

  1. Time:
    The time when you want this procedure to be run.
  2. Procedure:
    The name of the Sub-procedure to execute when the time event occurs.
  3. Schedule: (Optional)
    Set it to False to clear a previously set procedure (see, how to cancel OnTime schedule.

Suppose you have a macro "MyMacro" that you want to run daily at 13:00 (1:00 P.M.). First you need to determine how to use the OnTime method. You can do this using the Workbook_Open event in the private module of the Workbook object.

Press ALT+F11 to open the VBE (Visual Basic Editor) and then open the ThisWorkbook module for the Workbook object from the Project window. Enter the following code:

Private Sub Workbook_Open( )
     Application.OnTime TimeValue("13:00:00"), "MyMacro"
End Sub

MyMacro should be the name of the macro you want to run. It should reside in a standard module and contain the OnTime method, as follows:

Sub MyMacro( )
    Application.OnTime TimeValue("13:00:00"), "MyMacro"
'YOUR CODE
End Sub

The previous code snippets will run the procedure MyMacro at 13:00 each day, so long as Excel is open.

Clear a Schedule

Now suppose you want to run MyMacro at 15-minute intervals after opening your workbook. Again you will kick it off as soon as the workbook opens, so press ALT+F11 to open the VBE and then open the module for the Workbook object from the Project window and enter the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.OnTime dTime, "MyMacro", , False
End Sub
Private Sub Workbook_Open( )
  Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"
End Sub

In any standard module (accessed by selecting Insert » Module in VBE), enter the following code:

Public dTime As Date
Sub MyMacro( )
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"
'YOUR CODE
End Sub

Note how you pass the time of 15 minutes to the public variable dTime. This is so that you can have the OnTime method cancelled in the Workbook_BeforeClose event by setting the optional Schedule argument to False.

The Schedule argument is True by default, so by setting it to False, you are telling Excel to cancel the OnTime method that is set to run at a specified time.

If you didn't pass the time to a variable, Excel would not know which OnTime method to cancel, as Now + TimeValue("00:15:00") is not static, but becomes static when passed to a variable. If you didn't set the optional Schedule argument to False, the workbook would open automatically every 15 minutes after you close it and run MyMacro.

Use Windows Task Scheduler to Schedule Open a Workbook

If you want to run a macro on a schedule, you need to keep Excel open. Otherwise, the macro will not execute at the specified time. A workaround is to use Windows Task Scheduler to launch the Excel workbook that contains the macro. This way, the macro will run automatically when the workbook opens.

Here's how to do it:

  1. Open Windows Task Scheduler:
    Press Win + S, type "Task Scheduler" and press Enter to open Task Scheduler.
  2. Create a Basic Task:
    In Task Scheduler, click on Create Basic Task... from the right-hand sidebar.
  1. Follow the wizard to name and describe your task.
  1. Choose a Trigger:
    Select "Daily" or "Weekly," depending on your preferred schedule.
  1. Set the start date and time to when you want the macro to run.
  1. Choose Action:
    Select "Start a Program" as the action.
  1. Browse for the Excel workbook:
    In the "Program/script" field, browse for the Excel workbook. For example: C:\Path\To\Your\File.xlsm.
  1. Complete the wizard, review your settings, and click Finish.

You can right-click on your task in Task Scheduler and select "Run" to test it immediately.

Now, Windows Task Scheduler will open Excel workbook and run your macro at the specified time.