Hack 82 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 how to kick off the OnTime method. You can do this using the Workbook_Open event in the private module of the Workbook object.

On Windows, the fastest way to get to the private module of the Workbook object ( ThisWorkbook ) is to right-click the Excel icon next to File and select View Code. (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.

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 right-click the Excel icon next to File, select View Code, 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), 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 .