The OnTime event

The OnTime event occurs at a specified time of day. The following example demonstrates how to program Excel so that it beeps and then displays a message at 3 p.m.:

Sub SetAlarm()

Application.OnTime TimeValue("15:00:00"), "DisplayAlarm" End Sub

Sub DisplayAlarm() Beep

MsgBox "Wake up. It's time for your afternoon break!" End Sub

In this example, the SetAlarm procedure uses the OnTime method of the Application object to set up the OnTime event. This method takes two arguments: the time (3 p.m., in the example) and the procedure to execute when the time occurs (DisplayAlarm in the example). After SetAlarm is executed, the DisplayAlarm procedure will be called at 3 p.m., bringing up the message in Figure 19-8.

Microsoft Office Excel


Wake u|>. It's

time for your ill


temoon break!

Figure 19-8: This message box was programmed to display at a particular time of day.

Figure 19-8: This message box was programmed to display at a particular time of day.

If you want to schedule an event relative to the current time — for example, 20 minutes from now —you can write an instruction like this:

Application.OnTime Now + TimeValue("00:20:00"), "DisplayAlarm"

You can also use the OnTime method to schedule a procedure on a particular day. The following statement runs the DisplayAlarm procedure at 12:01 a.m. on April 1, 2004:

Application.OnTime DateSerial(2004, 4, 1) + _ TimeValue("00:00:01"), "DisplayAlarm"

The OnTime method has two additional arguments.If you plan to use this method,you should refer to the online help for complete details.

The two procedures that follow demonstrate how to program a repeated event. In this case, cell A1 is updated with the current time every five seconds. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event re-runs the UpdateClock procedure. To stop the events, execute the StopClock procedure (which cancels the event). Note that NextTick is a module-level variable that stores the time for the next event.

Dim NextTick As Date

Sub UpdateClock()

' Updates cell A1 with the current time

ThisWorkbook.Sheets(1).Range("A1") = Time ' Set up the next event five seconds from now NextTick = Now + TimeValue("00:00:05") Application.OnTime NextTick, "UpdateClock" End Sub

Sub StopClock()

' Cancels the OnTime event (stops the clock) On Error Resume Next

Application.OnTime NextTick, "UpdateClock", , False End Sub

The OnTime event persists even after the workbook is closed. In other words, if you close the workbook without running the StopClock procedure, the workbook will reopen itself in five seconds (assuming that Excel is still running).To prevent this,use a Workbook_BeforeClose event procedure that contains the following statement: Call StopClock

To see an example of a repeating OnTime event,see the analog clock example in Chapter 18.

0 0

Post a comment