Execute A Procedure At A Specific Time

You can create a procedure that executes at a specific time by capturing the OnTime event. For example, you can set a reminder message that pops up at a specific time while editing a workbook.

Unlike most other events, the OnTime event is not associated with a specific object. You, therefore, must access this event using the OnTime method that is associated with the Application object.

There are four different parameters you use with the OnTime method, with only the first two being required: EarliestTime, Procedure, LatestTime, and Schedule. You use the EarliestTime parameter to specify the time when the procedure executes. You express the time using the Excel time-numbering system. You must use the Procedure parameter to indicate the name of the procedure to execute at the specified time. Remember to enclose the procedure name in quotes.

Use the optional LatestTime parameter to indicate the latest time when the procedure can run. If the procedure has not run by the time specified by this parameter, it does not run. The other optional parameter, Schedule, has a default value of True to schedule the OnTime procedure to run again at the specified time or False to clear a previously set procedure.

Because the OnTime event is not associated with a specific object, you can place your procedure containing the method for accessing the event in any code module. Of course, if you must place the OnTime method procedure in a standard code window, you need to run the corresponding macro before the OnTime event code activates. You can also consider placing the OnTime method within the Workbook_Open procedure so that it loads the event code as the workbook opens. See the section "Run a Procedure as a Workbook Opens" for more information.

EXECUTE A PROCEDURE AT A SPECIFIC TIME

EXECUTE A PROCEDURE AT A SPECIFIC TIME

ChangeHistoryDi 0 ConflictRescilutio 1 - ïlUserResol Date 1901 False DisplayDrawingC -4104 - xlDispIc EnableAutoReco True EnvelopeVisible False

'-n In the Projects window, double-click the ThisWorkbook object.

■ The code module opens for the ThisWorkbook object.

In the Workbook_Open procedure, type

Application.OnTime Now + Timevalue("00:01:00"), "ShowWelcome", replacing Now + TimeValue ("000:01:00") with a valid time expression and "ShowWelcome" with the procedure to run.

In the Workbook_Open procedure, type

Application.OnTime Now + Timevalue("00:01:00"), "ShowWelcome", replacing Now + TimeValue ("000:01:00") with a valid time expression and "ShowWelcome" with the procedure to run.

Note: See the section "Run a Procedure as a Workbook Opens" for information on the Workbook Open procedure.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

The EarliestTime and LatestTime parameters expect time values based on Excel's time numbering system, which stores all times as decimal values ranging from 0.0 to 0.99999999. For example, Excel stores 12:00 noon as 0.5 and 6:00 PM as 0.75. Because dealing with fractional times becomes a little mind-boggling, VBA provides the TimeValue function, which you can use to convert a standard time into the decimal equivalent required by the two parameters. To use this function, you simply place the time you want to convert within quotes. For example, TimeValue( "5:45 PM" ) converts 5:45 PM to the appropriate decimal value.

You can use any valid time string with the TimeValue function.

Another useful VBA time function is the Now function, which returns the current date and time. When you use the Now function in combination with a TimeValue function, you can specify a time within a specific amount of time from the current time. For example, to have an event take place in 30 minutes, you express the time as follows:

Example:

Notice that you use the addition sign (+) to join the numeric values returned by the two functions.

Q Open the workbook in Excel.

■ The Workbook_Open subroutine activates the OnTime method and the specified procedure executes at the appropriate time.

L0 Create a new subroutine with the same name as the procedure specified in step 2.

Note: See Chapter 3 for information on creating subroutines.

□ Type the VBA code to run when the subroutine executes.

0 Close Excel.

Q Open the workbook in Excel.

■ The Workbook_Open subroutine activates the OnTime method and the specified procedure executes at the appropriate time.

0 0

Post a comment