OnTime Method

An OnTime event handler runs at a specified time in the future — either at a specified time of day or after a specified period of time has passed. This event handler runs only if Microsoft Excel is running and the workbook containing the OnTime event handler is loaded. You can use the arguments to the OnTime method of the Application object to specify the time you want the handler to run and the name of the handler. For example, to accumulate and print a series of reports every day at noon, you might use the following code.

'Initialize trapping using OnTime method
Sub TrapTime()
    'Set OnTime arguments
    Application.OnTime _
        earliestTime := TimeValue("12:00:00"), _
        procedure := "DoReports"
End Sub

Sub DoReports()                'OnTime handler
    AssembleReports
    PrintReports
End Sub

The TrapTime procedure initializes trapping of the OnTime event; at the first noon after TrapTime runs, Microsoft Excel runs the DoReports handler. To run the procedure only once, set the earliestTime argument to a date and a time rather than using the time alone.

If another procedure is running when the event occurs, Microsoft Excel waits for an additional interval specified by the latestTime argument of the OnTime method. For example, the following version of the TrapTime procedure waits for half an hour for Microsoft Excel to become free, and then it stops the event.

Sub TrapTime()                    'Initialize OnTime trapping.
    Application.OnTime _
        earliestTime := TimeValue("12:00:00"), _
        procedure := "MyOnTimeHandler", _
        latestTime := TimeValue("12:30:00")
End Sub

If you don't supply the latestTime argument, the procedure runs when Microsoft Excel next becomes available. You can schedule multiple OnTime handlers to run at different times; if you schedule more than one OnTime handler for the same time, the last-scheduled procedure runs first.

Tip

The initialization of an OnTime event isn't stored from session to session. To achieve the effect of its being stored, you can place in the startup folder a workbook that contains both an Auto_Open procedure and the OnTime handler itself. The Auto_Open procedure must initialize OnTime trapping for a specific time. Then, whenever Microsoft Excel is started, trapping of that event is reinitialized.

To remove a pending OnTime event, you must specify the exact time of the event and the name of its handler, and you must set the schedule argument to False. For example, if there are two OnTime events scheduled for noon — one handled by DoReports and the other one handled by a procedure called CleanUp — you can stop the CleanUp handler request with the following code.

Application.OnTime _
    earliestTime := TimeValue("12:00:00"), _
    procedure := "CleanUp", _
    schedule := False

Note

With the OnTime method, the user can work until an OnTime event occurs; with the Wait method, the user cannot interact with Microsoft Excel until the wait is over. To suspend all activity except for printing and recalculation, use the Wait method as shown in the following statement.

'Wait 15 seconds
Application.Wait Now + TimeValue("00:00:15")

For more information about the Wait method, see "Wait method" in Help.