Excel 2019 Bible

Chapter 46: Working with Excel Events

46

Using the OnTime event

The OnTime event occurs at a specified time. The following example demonstrates how to
program Excel to beep and then display a message at 3 p.m.:

Sub SetAlarm()

Application.OnTime TimeSerial(15,0,0), "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 (the
TimeSerial function is an easy way to get a time, and the hour argument of 15 is 3 p.m.)
and the procedure to execute when the time occurs (DisplayAlarm in the example). In
the example, after SetAlarm executes, the DisplayAlarm procedure is called at 3 p.m.,
bringing up the message.

You could also use VBA’s 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 another way to program an event for 3 p.m.:

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

If you want to schedule an event that’s relative to the current time—for example, 20 min-
utes from now—you can write an instruction like either of these:

Application.OnTime Now + TimeSerial(0, 20, 0), "DisplayAlarm"

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

You also can use the OnTime method to schedule a procedure on a particular day. Of course,
you must keep your computer turned on, and Excel must be running.

To cancel an OnTime event, you must know the exact time the event was scheduled to run.
Then you use the schedule argument of OnTime set to False. OnTime works to the nearest
second. If you schedule something for 3 p.m., you can unschedule it with this code:

Application.OnTime TimeSerial(15, 0, 0), "DisplayAlarm", , False

If you scheduled something relative to the current time and want to cancel it, you need to
store that time. This code will schedule the event:

TimeToRun = Now + TimeSerial(0, 20, 0)

Application.OnTime TimeToRun, "DisplayAlarm"