Tag Archives | timing

How to run Excel VBA at specific times

I have been asked a few times now if it is possible to schedule VBA code to run at either regular intervals or at specific times. Using a little VBA code you can use the Application.OnTime method to run a procedure automatically.

The OnTime method requires two arguments to run. The first argument is a specific date and time at which the procedure should run and the second argument is the name of the procedure to run. It is worthwhile knowing that you must provide the date and time to run the procedure rather than an offset from the current time.; however, as you will see this can be worked around.

The following code will run the ProcessData procedure at 12:45

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData"
End Sub

 

If you need to cancel an OnTime method, you must provide the exact time that the event was scheduled to take place. The following code cancels the previous timed activity.

Sub RunTimedActivity()
    'Stop the ProcessData procedure to running at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData", _
        Schedule:=False
End Sub

 

If you don’t know the precise time, the only way to tell Excel to cancel the next OnTime event or to cancel all the pending OnTime events is to close down the whole application. Therefore, the best advice I can give is to either store the time at which the procedure is to run in a Public variable and use that variable’s value in calls to OnTime, or store the time in a cell that is not viewed normally. Also note that if the workbook containing the procedure to be executed by OnTime is closed before the procedure is run, Excel will open the workbook again before running the procedure and will leave the workbook open after the procedure is complete.

Scheduling a Procedure to Run Regularly

In theory, the OnTime method needs a precise date/time to run. So if you want to schedule a procedure to run on a regular basis then you need a recursive function call that calls itself again in the future. If you need to stop this regular activity make sure you record the next time somewhere. Either use an out of the way cell or a public variable. In the example below I have used cell IV1. The example runs a procedure every 2 minutes 30 seconds.

Sub RunTimedActivity()
    Dim datNextTime As Date
    Dim intHrs As Integer
    Dim intMins As Integer
    Dim intSecs As Integer
    Dim strThisProcedure As String

    'variables to hold elapsed time
    intHrs = 0
    intMins = 2
    intSecs = 30

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    NextTime = Time + TimeSerial(intHrs, intMins, intSecs)
    Range("IV1").Value = datNextTime

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
	Procedure:=strThisProcedure

    'Call the procedure you want to run
    ProcessData
End Sub

 

The procedure to cancel the above activity would be:

Sub CancelActivity()
    Dim datNextTime As Date
    Dim strThisProcedure As String

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    datNextTime = Range("IV1").Value 

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
         Procedure:=strThisProcedure, Schedule:=False
End Sub

 

One final important note is that Excel will only run OnTime method when it is in Ready, Copy, Cut or Find mode. Any OnTime method cannot run at its scheduled time if you are in the process of editing a cell at that time. An alternative in this situation is to specify a window of time for the OnTime method to run. The following code will rum at some point between 12:45 and 12:50. If Excel remains in Edit mode for the whole of that period the task will be skipped.

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run between 12:45 and 12:50
    Application.OnTime EarliestTime:="12:45:00", _
       Procedure:="ProcessData", LatestTime:="12:50:00"
End Sub