VBA OnTime in Excel — Run a Macro at a Set Time or Every N Minutes, and Actually Stop It

TL;DR — Application.OnTime does not start a timer. It books a reservation in Excel's queue: at this time, run this macro. Your code ends immediately and Excel stays usable; when the time comes and Excel is ready, it runs the macro. The reservation is identified by the exact time and the macro name, so to cancel it you must pass both again, which means storing the time in a module-level variable. For a repeating job, the macro books its own next run. Cancel in Workbook_BeforeClose, because closing the workbook does not cancel anything: Excel will reopen the file to keep the appointment.

Private mNextRun As Date                       ' the reservation you may need to cancel

Sub StartRefresh()
    mNextRun = Now + TimeSerial(0, 5, 0)       ' five minutes from now
    Application.OnTime mNextRun, "RefreshPrices"
End Sub

Sub StopRefresh()
    On Error Resume Next                       ' error 1004 if nothing is booked
    Application.OnTime mNextRun, "RefreshPrices", Schedule:=False
    On Error GoTo 0
End Sub

This is the second article in a cluster on macros that Excel runs for you. The shortcut key guide covers macros started from the keyboard, and the SendKeys guide covers faked keystrokes. The idea that connects them: none of them calls your macro directly. Each puts a request in Excel's queue, and Excel runs it when it is idle and ready. With OnTime, the request has a time attached, and it stays in the queue until it runs or until you remove it with exactly the same details.

What you'll learn

The mental model: a reservation, not a running timer
Running a macro at a time of day, and every N minutes
Why cancelling fails, and the variable that fixes it
The timer you can no longer stop after a code reset
Why closing the workbook makes Excel reopen it
Busy Excel, LatestTime, and a schedule that drifts
When OnTime is the wrong tool

The mental model: a reservation, not a timer

Compare OnTime with a loop that waits:

' Blocks Excel: nothing else can happen for five minutes
Application.Wait Now + TimeSerial(0, 5, 0)
RefreshPrices

' Books a reservation and returns at once: Excel stays usable
Application.OnTime Now + TimeSerial(0, 5, 0), "RefreshPrices"

Application.Wait and Sleep keep your macro running and Excel frozen. OnTime hands a note to Excel and finishes. Between now and the reservation, no VBA code is running at all; the user works normally.

Excel keeps a list of these reservations. Each entry has two keys, a date-time and a procedure name, and Excel finds an entry by matching both. Everything else in this article follows from that: you can book several, you cancel one by naming it exactly, and the list belongs to Excel, not to your workbook.

Run a macro at a time of day

Give OnTime a full date and time, so there is no question about which day you mean:

Sub BookDailyExport()
    Dim runAt As Date
    runAt = Date + TimeSerial(17, 0, 0)        ' today at 17:00
    If runAt <= Now Then runAt = runAt + 1     ' already past: tomorrow at 17:00
    Application.OnTime runAt, "DailyExport"
End Sub

Many examples pass only TimeValue("17:00:00"). Building the date yourself makes the rule visible in the code: if the time has passed, the next run is tomorrow, not now. The Now guide explains why Date plus a time gives a real date-time value, and TimeSerial keeps the arithmetic free of locale problems.

Run a macro every N minutes

There is no repeat option. A repeating job is a macro that books its own next run at the end:

Private mNextRun As Date
Private Const INTERVAL_MIN As Long = 5

Sub StartRefresh()
    mNextRun = Now + TimeSerial(0, INTERVAL_MIN, 0)
    Application.OnTime mNextRun, "RefreshPrices"
End Sub

Sub RefreshPrices()
    ThisWorkbook.Worksheets("Prices").Range("A1").CurrentRegion.Calculate
    ' ... the real work ...
    StartRefresh                               ' book the next run
End Sub

StartRefresh starts the chain and RefreshPrices keeps it going. To stop the chain you must remove the one reservation that is currently booked, and that is where most OnTime code fails.

Cancelling: the exact time, or error 1004

To cancel, call OnTime again with Schedule:=False. Excel then looks for an entry with the same time and the same procedure name. This looks right and does not work:

' Wrong: Now has moved on, so this time matches no reservation
Application.OnTime Now + TimeSerial(0, 5, 0), "RefreshPrices", Schedule:=False

There is no reservation at that new time, so Excel raises Run-time error 1004: Method 'OnTime' of object '_Application' failed, and the real one stays booked. That is why the time lives in a module-level variable (mNextRun above). Book with it, cancel with it, and the details always match.

The On Error Resume Next in StopRefresh is deliberate and covers one line: if nothing is booked, for example because the macro already ran, the cancel raises 1004 and there is nothing to do about it. The On Error guide explains keeping such a handler that narrow.

The timer you can no longer stop

The variable has a weakness. Module-level variables are cleared whenever the VBA project resets: when you press the Reset button, when an unhandled error ends the code, when code runs End, or when you edit code while it is paused. The global variable guide lists all of them.

After a reset, mNextRun is 0. The reservation is still in Excel's list, but your code no longer knows its time, so StopRefresh cancels nothing. The macro keeps running every five minutes and books itself again each time.

Keep a copy of the time somewhere that survives a reset, such as a cell on a hidden settings sheet:

Sub StartRefresh()
    mNextRun = Now + TimeSerial(0, INTERVAL_MIN, 0)
    ThisWorkbook.Worksheets("Settings").Range("B2").Value = mNextRun
    Application.OnTime mNextRun, "RefreshPrices"
End Sub

Sub StopRefresh()
    If mNextRun = 0 Then mNextRun = ThisWorkbook.Worksheets("Settings").Range("B2").Value
    On Error Resume Next
    Application.OnTime mNextRun, "RefreshPrices", Schedule:=False
    On Error GoTo 0
    mNextRun = 0
End Sub

If a runaway timer happens anyway while you develop, closing Excel completely clears the list.

Closing the workbook does not cancel anything

The reservation belongs to Excel, not to the workbook. If the user closes your workbook and Excel stays open, then at the booked time Excel reopens the workbook to run the macro. To the user, a file they closed appears again on its own, and if the macro books the next run, it keeps happening.

So every workbook that books a run must cancel it on close:

' In ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopRefresh
End Sub

If the user then cancels the close at the save prompt, the workbook stays open with the timer stopped. If that matters, restart it in Workbook_Activate. The BeforeClose guide covers that case.

Busy Excel, LatestTime, and a schedule that drifts

The cluster rule applies here: Excel runs the reservation only when it is ready. If the user is typing in a cell, has a dialog open, or another macro is running at 17:00, the macro waits until that ends.

The third argument, LatestTime, sets a limit on that wait. If Excel cannot run the macro by then, the reservation is dropped:

' Run at 17:00, or not at all if Excel is still busy at 17:05
Application.OnTime runAt, "DailyExport", runAt + TimeSerial(0, 5, 0)

The waiting also explains why a repeating job drifts. Now + 5 minutes, measured at the end of each run, adds the run time and any delay to every interval. If you want runs at :00, :05, :10, book the next run from the previous reservation instead, and skip times that have already passed:

Sub BookNext()
    mNextRun = mNextRun + TimeSerial(0, INTERVAL_MIN, 0)
    Do While mNextRun <= Now                   ' Excel was busy: skip missed slots
        mNextRun = mNextRun + TimeSerial(0, INTERVAL_MIN, 0)
    Loop
    Application.OnTime mNextRun, "RefreshPrices"
End Sub

OnTime works to the second, not finer. For anything faster, it is the wrong tool; the Timer guide covers measuring short intervals.

The judgment call: OnTime is not a scheduler

OnTime runs a macro while this workbook is open in someone's Excel. It is the right tool for a dashboard that refreshes every few minutes, an auto-save reminder, or a status message that clears itself. It is the wrong tool for run the export every night at 2 a.m.: nobody's Excel is open then, a sleeping laptop misses the time, and one reset or crash ends the chain without a trace.

For jobs that must happen whether or not anyone is at the desk, use Windows Task Scheduler to open the workbook and let Workbook_Open do the work, as the Workbook_Open guide describes. Keep OnTime for timing inside a session, and give every reservation a way to be cancelled.

How ExcelMaster helps

OnTime bugs show up later and somewhere else: a closed file that reopens at noon, a refresh that will not stop, a timer that only misbehaves after someone pressed Reset.

ExcelMaster reads the workbook's code, finds every OnTime reservation, checks that each one has a matching cancel that uses the same stored time, and can add the Workbook_BeforeClose cleanup that stops the file from reopening itself.

Frequently asked questions

How do I run a macro every 5 minutes in Excel VBA?

Book the first run with Application.OnTime Now + TimeSerial(0, 5, 0), "MyMacro", and at the end of MyMacro book the next run the same way. Store each booked time in a module-level variable so you can cancel it.

How do I stop Application.OnTime?

Call Application.OnTime with the same time and procedure name you booked, and Schedule:=False. The time must match exactly, so use the stored variable, not a new Now calculation.

Why does OnTime give error 1004 when I cancel it?

No reservation matches the time and procedure name you passed, usually because the time was recalculated, or the reservation already ran. Cancel with the stored time, and wrap the cancel in a one-line On Error Resume Next.

Why does my workbook reopen by itself?

A reservation booked with OnTime was still in Excel's list when the workbook closed, so Excel reopened the file to run the macro. Cancel the reservation in Workbook_BeforeClose.

Does OnTime run if Excel is closed?

No. The reservation exists only in the running Excel. To run a macro when Excel is closed, schedule the workbook with Windows Task Scheduler and put the work in Workbook_Open.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-03.