My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Scheduling Macros in Excel With Application.OnTime

You are here: Home / Excel VBA / Scheduling Macros in Excel With Application.OnTime
scheduling macros in excel with application ontime
by

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.

basic ontime call

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.

ontime call with all arguments

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

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.

By submitting your email address you agree that we can email you our Excel newsletter.

 

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 ........

Running OnTime at set intervals

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

ontime 5 seconds from now

1 Hour from Now

ontime 1 hour from now

1 Day from Now

ontime 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

timevalue 1 hour 15mins from now

TimeSerial : 45 seconds 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.

timevalue run at specific time

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.

using public constant for interval

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.

break mode

If another VBA routine has caused an unhandled error and halted, scheduled tasks won't execute until that error is acknowledged and Ended.

end the code not debug

scheduling macros in excel with application ontime

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection
Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category:

Reader Interactions

Comments

  3. Smriti Singh

    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.

    Reply

  4. Ade

    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

    Reply

    • Catalin Bombea

      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.

      Reply

      • Ade Babarinde

        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.

        Reply

  5. Gary

    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?

    Reply

    • Philip Treacy

      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

      Reply

  6. Averan

    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

    Reply

    • Catalin Bombea

      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.

      Reply

  7. Chetan

    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

    Reply

    • Philip Treacy

      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

      Reply

      • Chetan

        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.

        Reply

        • Philip Treacy

          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

          Reply

  8. Akhila

    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!!!

    Reply

    • Philip Treacy

      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

      Reply

  9. Hitesh

    I want to perform a task that will start from particular time and will execute in every one minute… How to do that…???

    Reply

    • Philip Treacy

      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

      Reply

  10. Marcel

    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?

    Reply

  12. Dean Cornstubble

    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!

    Reply

    • Catalin Bombea

      Hi Dean,
      Can you right click the link and choose Save Link As, and choose a macro enabled excel type?

      Reply

  13. Jomili

    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.

    Reply

    • Philip Treacy

      Thx John. I might do a separate article about alternative ways to schedule things i.e. Windows Scheduler.

      Reply

    • Philip Treacy

      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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *