Example 1: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.

The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

‘Dim as a Public variable and it will be available to all Procedures in all modules.
Public rTime As Date

_____________________________________

Sub CellValueAutoIncr1()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.
‘The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

‘To run a procedure at a specific time, use TimeValue(time) viz. TimeValue(“20:30:00”) will run a procedure at 8.30 pm. To run a procedure at specified time intervals (say, from now), use Now + TimeValue(time) viz. Now + TimeValue(“00:00:05”) sets the time interval at 5 seconds, at which interval the procedure will run.
‘set the time interval at 3 seconds, at which interval the procedure will run.
rTime = Now + TimeValue(“00:00:03″)
‘procedure named CellValueAutoIncr1 will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime EarliestTime:=rTime, Procedure:=”CellValueAutoIncr1”, schedule:=True
‘increment the value in cell A1 (in Active Worksheet) by 5, for each time the Macro is repeated:
Cells(1, 1).Value = Cells(1, 1).Value + 5

‘If you attempt to close the workbook while a procedure is being run using Application.Ontime, Excel will re-open the workbook, and leave it open post completion of the procedure. Hence, you will need to cancel the

procedure at a certain point or time.

‘stop the procedure at a specified point – the procedure will stop after cell A1 value crosses 25 for the first time.

If Cells(1, 1).Value > 25 Then

‘If you need to cancel an OnTime, you must provide the exact time that the event was schedule to take place.

‘Therefore, you need to store the time at which the procedure is to run in a Public variable and use that variable’s value in calls to OnTime.

‘To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don’t pass the time to a variable, Excel will not know which OnTime method to cancel, as

Now + TimeValue(“00:00:03”) is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (Public

variable rTime in this example) and then use it to cancel the OnTime.

‘cancel the procedure by setting the Schedule argument to False:

Application.OnTime rTime, “CellValueAutoIncr1”, , False

End If

End Sub