Chapter 29: Timed Events

Overview

When you write an application, you often might want a procedure to be executed based on a regular time interval. For example, you might want to emulate the autosave add-in in Excel and call a save routine every five minutes or recalculate the workbook every five minutes. If the workbook is large and full of complex formulas, you should use manual calculation to ensure every five minutes that every calculation is up to date.

If you have used Visual Basic for Windows, then you are aware that there is a timer control available. All you have to do is to set the timer interval and then put your code on the timer event. Unfortunately, there is nothing like this in VBA, and it is a complicated procedure to make a timed event happen. You can do it, however, by using the OnTime method. The code in this method provides a time in the future and a subroutine to call when the current time equals the time you have set. Try entering the following code into a module:

 Private Sub time_set 
 Dim w As Workbook 
 Application.OnTime Now + TimeValue("00:05:00"), "time_set" 
 For Each w In Application.Workbooks 
 w.Save 
 Next w 
 End Sub 

This procedure needs to be called only once. You can call it from the Workbook_Open event when your workbook is first loaded.

In the preceding code, first a variable w is dimensioned as a workbook. Next, the OnTime statement states that in five minutes (the current time defined by Now , plus a time value of five minutes) the same routine of time_set is to be called again. The code then cycles through and saves each loaded workbook (using a For..Each loop). Five minutes later, the routine is called again. It then sets itself up to be called in another five minutes and saves all the workbooks again.

You have so far used only two of the parameters in OnTime ‚ EarliestTime and Procedure . You can also specify the LatestTime , which is the latest time the procedure should run. Once the current time has passed the latest time you specify, the OnTime procedure will no longer be called. The Schedule parameter can be set to True or False. This switches the OnTime function on or off. If Schedule is set to False, it will not run.

You can also specify the latest time at which you want the timed event to stop:

 Application.OnTime Now + TimeValue("00:05:00"),  _ 
 "time_set", TimeValue("22:00:00") 

In this example, the procedure time_set is called every five minutes until 10:00 p.m. To stop the automated procedure, you set the Schedule parameter to False:

 Application.OnTime Now + TimeValue("00:05:00"), "time_set", , False 

If you are thinking of writing VBA macros for the shareware industry, this is a great way to introduce ‚“nag ‚½ message boxes to get the user to register the product! You can also nag the user to do something every x minutes.