Excel 2010 Bible

Part VI: Programming Excel with VBA

884

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 (0.625, or 3 p.m., in the

example) 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.

Most people find it difficult to think of time in terms of Excel’s time 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 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 minutes

from now — you can write an instruction like this:

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.

Using the OnKey event

While you work, Excel constantly monitors what you type. As a result, you can set up a keystroke

or a key combination that — when pressed — executes a particular procedure.

The following example uses the OnKey method to set up an OnKey event. This event essentially

reassigns the PgDn and PgUp keys. After the Setup_OnKey procedure executes, pressing PgDn

executes the PgDn_Sub procedure, and pressing PgUp executes the PgUp_Sub procedure. The

next effect is that pressing PgDn moves down one row, and pressing PgUp moves up one row.

Sub Setup_OnKey()

Application.OnKey “{PgDn}”, “PgDn_Sub”

Application.OnKey “{PgUp}”, “PgUp_Sub”

End Sub

Sub PgDn_Sub()

On Error Resume Next

ActiveCell.Offset(1, 0).Activate

End Sub

Sub PgUp_Sub()

On Error Resume Next

ActiveCell.Offset(-1, 0).Activate

End Sub