The OnTime event
The OnTime event occurs when a particular time of day occurs. The following example demonstrates how to get Excel to execute a procedure when the 3 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, the OnTime method of the Application object is used. 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 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 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 p.m. on December 31, 2016:
Application.OnTime DateValue("12/31/2016 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 procedure. 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 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 StopClockThe OnTime method has two additional arguments. If you plan to use this method, you should refer to the Help system for complete details. If you’d like to see a rather complicated application, check out this analog clock application. 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 things up so a keystroke or a key combination executes 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, On Error Resume Next is used to ignore any errors that are generated. 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 keystroke. 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 executing 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.