VBA Programming Series...46

Events Not Associated with Objects

The events that I discuss previously in the articles are associated with either a workbook object or a worksheet object. Now, I discuss two types of events that are not associated with objects: time and keypresses.

Because time and keypresses aren’t associated with a particular object such as a workbook or a worksheet, you program these events in a normal VBA module.

The OnTime event

The OnTime event occurs when a particular time of day occurs. The follow-ing example demonstrates how to get Excel to execute a procedure when the 3:00 p.m. event occurs. In this case, a robot voice tells you to wake up, accompanied by a message box:

Sub SetAlarm()

Application.OnTime 0.625, “DisplayAlarm”

End Sub

Sub DisplayAlarm()

Application.Speech.Speak (“Hey, wake up”)

MsgBox “ 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 name of the Sub procedure to execute when the time event 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 kept open. The following statement runs the DisplayAlarm procedure at 5:00 p.m. on December 31, 2010:

Application.OnTime DateValue(“12/31/2010 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.

Here’s another example that uses the OnTime event. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event reruns the UpdateClock proce-dure. The net effect is that cell A1 is updated with the current time every five seconds. To stop the events, execute the StopClock procedure (which can-cels 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 work-book 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

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

If you’d like to see a rather complicated application, download a copy of my analog clock workbook, shown in Figure 11-6. The clock face is actually a chart, and the chart is updated every second to display the time of day. Useless, but fun.

Keypress events

While you work, Excel constantly monitors what you type. Because of this, you can set up a keystroke or a key combination to execute a procedure.

Here’s an example that reassigns the PgDn and PgUp keys:

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

After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row.

Notice that the key codes are enclosed in braces, not in parentheses. For a complete list of keyboard codes, consult the Help system. Search for OnKey.

In this example, I use On Error Resume Next to ignore any errors that are gen-erated. For example, if the active cell is in the first row, trying to move up one row causes an error that can safely be ignored. And if a chart sheet is active, there is no active cell.

By executing the following routine, you cancel the OnKey events:

Sub Cancel_OnKey()

Application.OnKey “{PgDn}”

Application.OnKey “{PgUp}”

End Sub

Using an empty string as the second argument for the OnKey method does not cancel the OnKey event. Rather, it causes Excel to simply ignore the key-stroke. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key:

Application.OnKey “%{F4}”, “”

Although you can use the OnKey method to assign a shortcut key for execut-ing a macro, you should use the Macro Options dialog box for this task.

If you close the workbook that contains the code and leave Excel open, the OnKey method will not be reset. As a consequence, pressing the shortcut key will cause Excel to automatically open the file with the macro. To prevent this from happening, you should include code in your Workbook_BeforeClose event code to reset the Onkey event.