OnTime method

This method is used to run a procedure at a specific time or after a specific amount of time has passed. The syntax is:

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Of course, the Procedure parameter is the name of the macro to run. The EarliestTime parameter is the time you want the macro to be run. To specify a time, we use the TimeValue function. For instance, the following code executes the macro test in the ThisWorkbook code module of the bookl workbook at 3:58 P.M.:

Application.OnTime TimeValue("3:58 PM"), "d:\excel\book1.xlslThisWorkbook.test"

LatestTime is an optional parameter that specifies the latest time at which the procedure can begin running. We can use the TimeValue function to specify a time for this parameter, or we can set LatestTime to EarliestTime plus some additional time. For instance, the following code requires that Excel run the macro no later than 30 seconds following 3:58 P.M.:

Application.OnTime TimeValue("3:58 PM"), "d:\excel\book1.xlslThisWorkbook.test", TimeValue("3:58 PM") + 3 0

The LatestTime parameter may be useful, since if Excel is busy (running another procedure, for instance), then execution of the macro denoted by Procedure will be delayed. If you do not want the macro to be run after a certain time, then set the LatestTime parameter.

If you want to clear a previously set OnTime macro, you can call the procedure with the Schedule parameter set to False. Otherwise, the parameter can be omitted, since its default value is True.

Note that the Now function returns the current time. Thus, to schedule a macro for a certain amount of time from the present, we can set EarliestTime to:

Now + TimeValue( time)

0 -1

Post a comment