MS-Excel / General Formatting

---

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.

The Application.OnTime method can make macros run automatically, once you've done some setup. Suppose you have a macro that you want to run each day at 15:00 (3:00 p.m.). First you need to determine howto kick off the OnTime method. You can do this using the Workbook_Open event in the private module of the Workbook object.

In Windows, the fastest way to get to the private module of the Workbook object is to press Alt/Option-F11 and double-click ThisWorkbook (pre-2007, right-click the Excel icon next to File and select ViewCode). On a Macintosh, open the VBE and then open the module for the Workbook object from the Project window. Enter the following code:

    Private Sub Workbook_Open( )
     Application.OnTime TimeValue("15: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("15:00:00"), "MyMacro"
    'YOUR CODE

    End Sub

This will run the procedure MyMacro at 15:00 each day, so long as Excel is open.

Nowsuppose 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/Option-F11 and double-click ThisWorkbook (pre-2007, rightclick the Excel icon next to File and select ViewCode). 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), 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 howyou 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.