Application.OnTime

There may come a time when you need to run a procedure automatically at a particular time every day.
You may want to refresh the data in your database every 5 minutes.
This can be used to run a macro at a later time or to execute a procedure at regular time intervals.
Starts a background timer that runs a macro at a specified time.
Each application can only maintain one background timer set by OnTime.
If you start another timer before an existing timer runs, the existing timer is cancelled.
This method will only be executed if the file containing the procedure is loaded when this line of code is executed and if the file containing the procedure is currently loaded at the time it is meant to run.
Always use fully qualified macro names: Project.Module.SubName
In Excel there is also an Application.Wait


Excel

Application.OnTime (Earliesttime, Procedure [,Latesttime] [,Schedule]) 

EarliestTime - The time when you want this procedure to be run.
Procedure - The time when you want this procedure to be run.
Latesttime - The latest time at which the procedure can be run. If you do not specify a 'LatestTime' then the macro will run when Excel is available after the 'EarliestTime' has passed. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won't be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.
Schedule - True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.

Application.OnTime Earliesttime:="12:00pm", _ 
                   Procedure:=Project.Module1.Alarm, _
                   Schedule:=True

This will run as soon as the current procedure has finished.

Application.OnTime Earliesttime:=Now(), _ 
                   Procedure:=Project.Module1.Alarm, _
                   Schedule:=True

This will run 10 seconds from the time this line of code it executed.

Application.OnTime Earliesttime:=Now + TimeValue("00:00:10"), _ 
                   Procedure:=Project.Module1.Alarm, _
                   Schedule:=True

Cancelling a scheduled Procedure
It is possible to cancel a procedure that has been scheduled to run but you need to know the exact date and time it was scheduled for.
To cancel a scheduled procedure you must know the "EarliestTime" it was scheduled for.
Exactly the same syntax except you set the schedule parameter to False. This tells the application to cancel the schedule.

Application.OnTime Earliesttime:=dtTimeToRun, _ 
                   Procedure:=sProcedureName, _
                   Schedule:=False

Word

Application.OnTime(When, Name [,Tolerance]) 

When - The time at which the macro is to be run. Can be a string that specifies a time (for example, "4:30 pm" or "16:30"), or it can be a serial number returned by a function such as TimeValue or TimeSerial (for example, TimeValue("2:30 pm") or TimeSerial(14, 30, 00)). You can also include the date (for example, "6/30 4:15 pm" or TimeValue("6/30 4:15 pm")).
Name - The name of the macro to be run. Use the complete macro path to ensure that the correct macro is run (for example, "Project.Module1.Macro1"). For the macro to run, the document or template must be available both when the OnTime instruction is run and when the time specified by When arrives.
Tolerance - The maximum time (in seconds) that can elapse before a macro that wasn't run at the time specified by When is cancelled. Macros may not always run at the specified time. The macro will be delayed until Word has completed another task. If this argument is 0 (zero) or omitted, the macro is run regardless of how much time has elapsed since the time specified by When.

Application.OnTime When:="12:00pm", 
                   Name:=Project.Module1.Alarm, _
                   Tolerance:=300

This will run as soon as the current procedure has finished.

Application.OnTime When:=Now() 

This will run 10 seconds from the time this line of code is executed.

Application.OnTime When:=Now() + TimeValue("00:00:10") 

This example runs the macro named "Macro1" 15 seconds from the time the example is run.
The macro name includes the project and module name.

Application.OnTime When:=Now + TimeValue("00:00:15"), _ 
                   Name:="Project1.Module1.Macro1"

This example runs the macro named "Start" at 1:30 P.M.
The macro name includes the project and module name.

Application.OnTime When:=TimeValue("1:30 pm"), _ 
                   Name:="VBAProj.Module1.Start"

Cancelling a scheduled Procedure
A second call to the OnTime will cancel the first call


PowerPoint

The method Application.OnTime does not exist in PowerPoint.


Outlook

The method Application.OnTime does not exist in Outlook.


Returning the correct time
This can be done using the following function

Public Function BET_TimeToRun(ByVal iNoOfSeconds As Integer, _ 
                     Optional ByVal iNoOfMinutes As Integer = 0, _
                     Optional ByVal iNoOfHours As Integer = 0) As Time

   BET_TimeToRun = Now() + TimeSerial(iNoOfHours, iNoOfMinute, iNoOfSeconds)
End Function

Scheduled the Procedure

Public Sub BET_ScheduleProcedure(ByVal sProcedureName As String, _ 
                                 ByVal dtTimeToRun As Date)

'Excel code
Application.OnTime Earliesttime:=dtTimeToRun, _
                   Procedure:=sProcedureName, _
                   Schedule:=True

'Word code
Application.OnTime When:=dtTimeToRun, _
                   Name:=sProcedureName, _

End Sub

Important

The line of code to cancel a scheduled procedure will generate an error when you attempt to cancel a non existent procedure.
The "EarliestTime" parameter is the earliest time the procedure will be called and is not the definitive time. Another macro might currently be running.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext