If you need Excel to run some VBA at a specific time, or repeatedly at set intervals, you can use the Application.OnTime method.
A basic call to Ontime requires that you supply a time when you want the code to run, and the name of the macro you want to run.
The argument EarliestTime is called this because Excel will actually execute the Procedure no earlier than EarliestTime, but possibly later.
There's no guarantee that your scheduled macro will run exactly when you want because Excel may be busy doing something else.
It may be executing some other VBA, or you may be entering data into a sheet (Enter mode). If Excel is not in Ready, Copy, Cut or Find mode, execution of your scheduled macro may be delayed.
There are two optional arguments, LatestTime and Schedule.
As we've just seen, Excel may not run your macro exactly at the time you want. By specifying LatestTime, we're giving Excel a window, between EarliestTime and LatestTime, during which you want it to run your VBA.
If your scheduled code is delayed, and Excel is only ready to execute the macro after LatestTime, then it won't run it at all.
The value of Schedule indicates whether your are setting a task (True), or cancelling one (False).
The default value is True so there's no need to specify Schedule unless you are cancelling a previously set task.
Cancelling OnTime
If you want to cancel an OnTime task once you've scheduled it, you can do so like this
Cancelling OnTime requires that you specify the name of the macro, and exact time it is set to run. This means you need to store both of these somewhere.
Excel doesn't provide any way for you to check what macros are scheduled to run, so you must keep track of these things yourself.
The easiest way would be to store the values in public variables/constants, or on a worksheet. Although you could store them in the registry if you want.
Download Example Workbook
Enter your email address below to download the sample workbook.
Running Code at Set Intervals
If you want to repeatedly run the same macro at set periods you make the macro call itself.
First, write the macro that sets the OnTime schedule, I'm calling it SetOnTime. This macro will set a schedule for a macro called MyCode.
Then write the MyCode macro, which has the code you want to execute, and a call to SetOnTime.
When you run SetOnTime it sets the OnTime call, which when it runs, calls MyCode.
MyCode then calls SetOnTime again, and so on ........
Specifying the Time
Time in Excel is a funny old thing and often causes a lot of confusion.
If you understand the way time is stored as serial number you can set an OnTime task like this
5 Seconds from Now
1 Hour from Now
1 Day from Now
Or you can use TimeValue and TimeSerial to set a task for a particular time from now.
TimeValue : 1 hour 15 mins From Now
TimeSerial : 45 seconds From Now
If you want to set a macro to run at a specific time, use Timevalue.
TimeValue : Run at 8.30pm.
Storing Time in a Public Constant
Of course if you are setting something to run at intervals you can use a public constant and store the interval in that.
I've declared a public constant called Interval and set it to 5.
By using TimeSerial I can set the interval that the macro runs at to every 5 seconds.
Multiple OnTime Tasks
You can set multiple tasks to run using OnTime. But remember that you need to keep a record of them if you want to be able to programatically unschedule them.
Running a Macro from a Closed Workbook
If you schedule a macro and then close the workbook containing the macro, Excel will try to open that workbook before running the macro.
In this scenario, if the workbook is not in a trusted location, you may find that macros in the workbook are disabled, and the scheduled macro will not run.
When Scheduled Tasks Won't Run
A scheduled macro won't run if the Excel application is closed.
A scheduled task will not execute in break mode. If you are debugging your VBA, like stepping through code, or a VBA routine has caused an error and you have started to debug it, scheduled tasks won't execute and you'll get an error telling you so.
If another VBA routine has caused an unhandled error and halted, scheduled tasks won't execute until that error is acknowledged and Ended.
thanks a lot
You’re welcome.
Regards
Phil
Thanks for very good post. It’s clear and worable.
Great!
Thanks Kampol
Hi Philip
Thank you for all that you teach us. I have been trying to learn more about time based codes with Excel.
I would like to run a userform as soon as my workbook opens and have it run every 30 mins.
Now when i try to type in Public Const….
I get a Compile error: Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.
Hi,
We need to see your code to debug it. Please start a topic on our forum and attach your file(s)
Regards
Phil
Hi Phil.
first of all, thanks for the guardians on running a macro. Please I have a problem with my macro. I will like the macro to activate at 6 – Production start, 8 – break time, 8.10 – break is now over, 10 – break time, 10.10 – break is now over, 12 – Lunch time, 12.20 – Lunch is now over, 14 – break time, 14.10 – break is now over
below is my coding
Sub runspeech()
If clockOn = True Then
If Format(Now(), “DDD”) = “MON” Or Format(Now(), “DDD”) = “TUE” Or Format(Now(), “DDD”) = “WED” Or Format(Now(), “DDD”) = “THU” Then
Application.OnTime TimeValue(Range(“H52”).Text), “Speechstart”
Application.OnTime TimeValue(Range(“H53”).Text), “SpeechBreak”
Application.OnTime TimeValue(Range(“H54”).Text), “SpeechBreakend”
Application.OnTime TimeValue(Range(“H55”).Text), “SpeechBreak”
Application.OnTime TimeValue(Range(“H56”).Text), “SpeechBreakend”
Application.OnTime TimeValue(Range(“H57”).Text), “SpeechLunch”
Application.OnTime TimeValue(Range(“H58”).Text), “SpeechLunchend”
Application.OnTime TimeValue(Range(“H59”).Text), “SpeechBreak”
Application.OnTime TimeValue(Range(“H60”).Text), “SpeechBreakend”
Application.OnTime TimeValue(Range(“H61”).Text), “SpeechCleanup”
Application.OnTime TimeValue(Range(“H62”).Text), “Speechend”
End If
If Format(Now(), “DDD”) = “FRI” Then
Application.OnTime TimeValue(Range(“H66”).Text), “Speechstart”
Application.OnTime TimeValue(Range(“H67”).Text), “SpeechBreak”
Application.OnTime TimeValue(Range(“H68”).Text), “SpeechBreakend”
Application.OnTime TimeValue(Range(“H69”).Text), “SpeechBreak”
Application.OnTime TimeValue(Range(“H70”).Text), “SpeechBreakend”
Application.OnTime TimeValue(Range(“H71”).Text), “SpeechLunch”
Application.OnTime TimeValue(Range(“H72”).Text), “SpeechLunchend”
Application.OnTime TimeValue(Range(“H73”).Text), “SpeechCleanup”
Application.OnTime TimeValue(Range(“H74”).Text), “Speechend”
End If
End If
End Sub
Sub SpeechStart()
Application.Speech.Speak Range(“I52”).Cells
End Sub
Sub SpeechBreak()
Application.Speech.Speak Range(“I53”).Cells
End Sub
Sub SpeechBreakend()
Application.Speech.Speak Range(“I54”).Cells
End Sub
Sub SpeechLunch()
Application.Speech.Speak Range(“I57”).Cells
End Sub
Sub SpeechLunchend()
Application.Speech.Speak Range(“I58”).Cells
End Sub
Sub SpeechCleanup()
Application.Speech.Speak Range(“I61”).Cells
End Sub
Sub Speechend()
Application.Speech.Speak Range(“I62”).Cells
End Sub
but it did now work automatically when I called on Thisworkbook. Please can you help
Hi,
Hard to see what happens, we cannot see what you have in H52 for example. OnTime needs Date and Time, not TimeValue only. Usually, you should use Now()+TimeValue.
Do you have the main code launched in Workbook_Open event?
Please open a new topic on our forum with your file attached, so we can see what you really have, to help you make it work.
Hello Catalin,
Thanks for the reply. Below are the information
Monday – Thursday
6:00 Production is now starting
8:00 Break time
8:10 Break time is now over
10:00 Break time
10:10 Break time is now over
12:00 Lunch time
12:20 Lunch time is now over
14:00 Break time
14:10 Break time is now over
15:55 Time to clean up your work stations
16:00 Production is now over. Have a good day.
Friday
6:00 Production is now starting
8:00 Break time
8:10 Break time is now over
10:00 Break time
10:10 Break time is now over
12:00 Lunch time
12:20 Lunch time is now over
13:55 Time to clean up your work stations
14:00 Production is now over. Have a good weekend.
Thanks.
Hi Ade,
Please upload on our forum a sample file with your setup and codes, I can’t see the details I need in your description.
Thank you
Catalin
Thanks for the reply. I created an account but I don’t know how to load my file for you to review.
Hi Ade,
Just create a new topic with our file and a description of the problem.
You will have there tools to attach a file to the message.
Hi Catalin,
Happy New Year. Please I haven’t received a reply after posting my file. What do I need to do please.
Hi Ade,
I have replied to the forum topic on December 21, you should check your email. The forum notifies you by mail when a reply is added.
However, if you don’t get notified for some reason, you can check the forum topic from time to time.
Here is your topic link: https://www.myonlinetraininghub.com/excel-forum/vba-macros/macro-application-at-specific-time#p18077
We can continue the discussion on forum, if you have other questions related to this subject.
Regards,
Catalin
when I use “application ontime timevalue(“earliest time”), “procedure”, excel running multiple times instead of one time? I added another “Sub”: application.ontime timevalue(“latettime”), “procedure”, schedule:= False, the VBA still running two or more times. How comes that?
Hi Gary,
It’s a bit confusing without seeing your actual code (you can attach this to a question in the forum if you need to) – but it sounds like you are creating multiple OnTime calls and these are still active, causing Excel to stay open or open multiple times.
You’ll need to explicitly cancel any OnTime calls to prevent this.
regards
Phil
hi,
Thanks for an interesting article.
I have a question regarding application.ontime with a time value: run at an absolute time – is it possible to pause this if an excel userform was running, until the userform is completed and closed?
On my workbook_open sub, I have a number of macros which are designed to e-mail a supervisor and are called using application.ontime, with a specified time based on when a certain task must be completed by an operator. This is working for me 99% of the time. However, the operator completes the task using userforms; if they are in the middle of completing the userform at the specified time that the application.ontime macro runs, it interrupts the userform and the operator is reporting that the “screen freezes.” I would like to have the application.ontime run only after all opened userforms have been completed/closed – the userforms are opened with .show vbmodal.
Any help would be greatly appreciated
Hi Averan,
An easy way is to declare a public boolean parameter, like IsFormOpen.
At form Initate event, set this parameter to true, and to false when the form is closed.
All you have to do is to update your ontime macro to check the IsFormOpen parameter and exit if true.
Thanks Catalin, that has worked a treat!
Great, thanks for feedback !
I am trying to run following vba code for clearing contents of validation at specific time
Yesterday at 8:17 PM
Add bookmark
#1
I am trying to write a VBA code to clear contents of data validation at particular point of time. Please find below the code. The second code is for clear content with default value “Pending”
Sub Reset()
‘Run ClearContents1 at 6.00am
Application.OnTime TimeValue(“6:00:00”), “ClearContents1”
End Sub
Sub ClearContents1()
Dim xCell As Range
Dim xRg As Range
Dim xAcCell As Range
Dim xScreen As Boolean
On Error Resume Next
Set xAcCell = Application.ActiveCell
Set xRg = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
End If
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each xCell In xRg
If xCell.Validation.Type = 3 Then
If xCell.Value = “” Then xCell.Value = “Pending”
End If
Next
xAcCell.Select
Application.ScreenUpdating = xScreen
End Sub
Hi Chetan,
Are you having some issues? You haven’t actually said.
If you are please start a topic on the forum and attach your workbook and data there.
Thanks
Phil
I am trying to run Application.OnTime code. There are following two codes in my file
Sub DropDownListinVBA()
Range(“C2:C10″).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=”Pending, Completed”
End Sub
Sub Reset()
Application.OnTime TimeValue(“16:00:00”), Range(“C3:C10”).ClearContents, True
End Sub
The problem is that the Application.OnTime doesnt run at 16:00 and clears contents. If i manually run macros it works but automatically it doesnt work.
Request your help.
Hi Chetan,
The variable that specifies the code to run must be a string so you need to do something like this
Application.OnTime TimeValue(“16:00:00”), “MyCode”
then create the sub MyCode that contains the code to clear the range.
You also don’t need the True parameter.
Regards
Phil
I want to perform on multiple excel sheets that will execute daily at a particualr time automatically. How to do that??? Can you please help me!!!
Hi,
You need Excel open to execute any VBA so perhaps that isn’t what you are after and may need another solution for the scheduling.
If you can post a qs on the forum with a clear, detailed explanation of what you are trying to do, we may be able to help.
Regards
Phil
I want to perform a task that will start from particular time and will execute in every one minute… How to do that…???
Hi Hitesh,
You need to use 2 subs that call each other like so
Sub MyWorker()
‘ Code to do your tasks here
MyScheduler
End Sub
Sub MyScheduler()
Application.OnTime Now + TimeValue(“00:01:00”), “MyWorker”
End Sub
You will need an initial call to MyScheduler to start the process.
Regards
Phil
Hello Philip,
Thanks for your Interesting article!
Is there a way to schedule a task more than once a second? For example once every quarter of a second?
Hi Marcel,
Sorry, OnTime only works with whole seconds. But you could make multiple OnTime calls for the same task for the same time?
If you definitely need to schedule things to fractions of a second you’ll need a different approach. Perhaps using Sleep might work for you
https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop
Regards
Phil
Thank you Phil.
No worries
Thank you
Simple and easy to understand
Using to get live date from a web site that changes every 90 seconds
Glad it was helpful Glen
I downloaded this file a couple of times, but the file is being stripped of the macros. Can you email me the file? See email address below. Thanks!
Hi Dean,
Can you right click the link and choose Save Link As, and choose a macro enabled excel type?
I was reading the article with great interest, because I thought it was telling me something I had previously thought was impossible, until I came to “A scheduled macro won’t run if the Excel application is closed.” It would have helped me to have this statement closer to the beginning of the macro, or as a caveat like “IF EXCEL is open you can schedule a Macro”.
Alternatively, you could add to the article by pointing to using Windows Scheduler to open an Excel workbook, then have the macro triggered to fire from the Workbook open event. You can also have a trigger for Windows Scheduler, such as Unlocking the Workstation. Lots of possibilities.
Thx John. I might do a separate article about alternative ways to schedule things i.e. Windows Scheduler.
is the Application.OnTime constantly running. i.e. using system resources?
Thanks,
Jim
Hi Jym,
Excel needs to be open for scheduled OnTime tasks to execute, so yes they do use system resources, but only as much as Excel does.
Regards
Phil