The OnTime event

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

Sub

SetAlarm()

Application.OnTime 0.625,

"DisplayAlarm"

End

Sub

Sub

DisplayAlarm()

Beep

MsgBox "Wake up. It's time

for your afternoon break!"

End

Sub

In this example, I use the OnTime method of the Application object. This method takes two arguments: the time (0.625 or 3:00 p.m.) and the code to execute when the time occurs (DisplayAlarm).

This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set an OnTime event to remind yourself.

Most people (this author included) find it difficult to think of time in terms of the Excel numbering system. Therefore, you may want to use the VBA TimeValue function to represent the time. TimeValue converts a string that looks like a time into a value that Excel can handle. The following statement shows an easier way to program an event for 3:00 p.m.:

Application.OnTime TimeValue("3:00:00 pm"), "DisplayAlarm"

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

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

You can also use the OnTime method to run a VBA procedure on a particular day. You must make sure that your computer keeps running and that the workbook with the procedure is open. The following statement runs the DisplayAlarm procedure at 5:00 p,m, on December 31, 2005:

Application.OnTime DateValue("12/31/2005 5:00 pm") "DisplayAlarm"

This particular code line could come in handy to warn you that you need to go home and get ready for the New Year's Eve festivities.

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

0 0

Post a comment