🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Timer in Excel — Measure How Long Your Macro Takes (and Why It Is Not a Scheduler)

|

VBA Timer in Excel — Measure How Long Your Macro Takes (and Why It Is Not a Scheduler)

TL;DR — The Timer function does not pause anything and does not run anything on a schedule. It returns the number of seconds elapsed since midnight as a decimal, and you read it before and after a block of code to measure how long that code took:

Sub HowLongDoesItTake()
    Dim t As Double
    t = Timer                     ' start the stopwatch
    ' ... the code you want to measure ...
    Range("A1:A100000").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
    Debug.Print "Took " & Format(Timer - t, "0.00") & " seconds"
End Sub

Timer is the most misnamed function in VBA. Its name promises a countdown or a scheduler, so people search for "vba timer" wanting "run this every five minutes" or "wait, then fire" — and Timer does neither. It is a stopwatch: a way to answer "how long did that take?" This guide is built on that one idea, because once you see Timer as a stopwatch, its real job (benchmarking) and its two traps (it's-not-a-scheduler, midnight rollover) fall straight out.

What you'll learn

  • The mental model — Timer is a stopwatch (measures elapsed time), not a countdown or scheduler
  • Its real job — benchmarking, and how it proves a performance switch actually helped
  • The number-one wrong expectation — if you want "run every N minutes," you want Application.OnTime
  • The midnight-rollover bug that produces negative elapsed times, and the guard for it
  • Its resolution — about a hundredth of a second — and what that is and isn't good for

The mental model: a stopwatch you read twice

A stopwatch does not make anything happen. It does not ring, it does not pause the race, it does not start the runners. It just tells you the time on its face, and you get a duration by reading it twice and subtracting. Timer is exactly that: calling it returns "seconds since midnight" as a Single (so Timer at 00:01:30 returns 90.0), and you measure how long code took by reading it before and after:

Dim t As Double
t = Timer               ' read the face: e.g. 43521.14
' ... work ...
Debug.Print Timer - t   ' read again and subtract: the elapsed seconds

There is no "pause" here and no "fire later." Timer never changes what your macro does — it only tells you how long a part of it lasted. If you were hoping Timer would delay your code, that is Sleep or Application.Wait, not Timer.

Its real job: proving a change actually made the macro faster

The stopwatch model points straight at what Timer is for: benchmarking. When you turn off Application.ScreenUpdating, switch Application.Calculation to manual, or replace a cell-by-cell loop with an array, you should not believe it got faster — you should measure it. Timer is how.

Sub ProveTheSwitchHelps()
    Dim t As Double

    t = Timer
    Application.ScreenUpdating = True         ' the slow way, on purpose
    WriteTenThousandRows
    Debug.Print "ScreenUpdating on:  " & Format(Timer - t, "0.00") & "s"

    t = Timer
    Application.ScreenUpdating = False        ' the fast way
    WriteTenThousandRows
    Application.ScreenUpdating = True
    Debug.Print "ScreenUpdating off: " & Format(Timer - t, "0.00") & "s"
End Sub

This is the honest complement to every performance tip: ScreenUpdating, Calculation, and the rest are worth doing, but Timer is how you know they helped this workbook instead of repeating folklore. It is also how you find the slow line: wrap a few suspects in Timer reads and let the numbers, not your intuition, tell you where the time goes.

The number-one wrong expectation: Timer is not a scheduler

This is the misconception that sends most people to a search engine. They want the macro to run itself every 5 minutes, or to do something after a delay, and they assume a thing called Timer must be it. It is not. Timer only reports elapsed time; it never triggers anything.

Name what you actually want:

  • Run a macro later, or on a repeating schedule ("refresh every 5 minutes," "run at 9 AM") — Application.OnTime. It hands Excel a time and a macro name and schedules the call.
  • Pause the macro for a fixed delay — Sleep (milliseconds) or Application.Wait (whole seconds).
  • Measure how long something took — Timer. This one, and only this one.

If you catch yourself trying to build a "repeat every N seconds" loop out of Timer, stop: you want OnTime. Timer in a loop just burns the CPU reading the clock.

The midnight-rollover bug

Because Timer measures seconds since midnight, it resets to 0 at midnight. So a macro that starts at 23:59:58 and finishes at 00:00:03 computes Timer - t as roughly 3 - 86398 = -86395 — a negative elapsed time. It is rare, but it is real, and it has produced more than one baffling "my macro took minus twenty-four hours" bug report.

If a run could ever straddle midnight, guard for it, or measure with Now instead:

Dim elapsed As Double
elapsed = Timer - t
If elapsed < 0 Then elapsed = elapsed + 86400   ' add a day's worth of seconds

For anything that might run for hours, prefer date-based timing (Now, or a Date + Timer combination) so a midnight crossing can't corrupt the result. For the common case — timing a block that lasts seconds or minutes — plain Timer - t is perfect.

Resolution: a hundredth of a second

Timer updates roughly every 10 milliseconds (about a hundredth of a second) on Windows. That is plenty to answer the questions Timer is meant for: "is version A faster than version B?", "how long does this import take?", "which of these three lines is the slow one?" It is not enough for microbenchmarks — timing something that runs in well under a hundredth of a second will just report 0. When you need that precision, loop the operation thousands of times and divide, or drop to the QueryPerformanceCounter API. For everyday "make it faster and prove it" work, Timer is exactly right.

How ExcelMaster helps

Timer is simple, but using it well is a small bundle of habits: read it into a Double, subtract in the right order, Format the result so it's readable, guard the midnight case on long runs, and — the part people skip — actually wrap the right code so the measurement means something. And when the goal is "run this on a schedule" or "pause here," Timer is the wrong tool entirely.

ExcelMaster picks the right tool for what you describe. Ask it to "time how long my macro takes" and it drops in a clean Timer-based benchmark with a formatted Debug.Print; ask it to "run this every five minutes" and it wires up Application.OnTime instead; ask it to "pause a moment" and it reaches for Wait or Sleep. You get the measurement you wanted, not a Timer loop pretending to be a scheduler.

Frequently asked questions

How do I measure how long a macro takes in VBA?

Read the Timer function before and after the code you want to measure, then subtract: t = Timer at the start, and Debug.Print Timer - t at the end. Timer returns seconds since midnight as a decimal, so the difference is the elapsed time in seconds. Wrap it with Format(..., "0.00") for a readable result.

Does the VBA Timer function pause my code or run it on a schedule?

No. Timer only reports the seconds elapsed since midnight — it never pauses your macro and never triggers anything. To pause, use Sleep or Application.Wait. To run a macro later or on a repeating schedule, use Application.OnTime. Timer is purely a stopwatch for measuring elapsed time.

Why is my VBA elapsed time negative?

Because Timer resets to zero at midnight. If your macro starts just before midnight and ends just after, Timer - t subtracts a large "before" value from a small "after" value and goes negative. Guard it by adding 86400 (a day's seconds) when the result is negative, or measure with Now for runs that can cross midnight.

What is the difference between Timer and Application.OnTime?

Timer measures elapsed time — you read it to find out how long code took. Application.OnTime schedules a macro to run at a specific time or after a delay. People often search for "vba timer" wanting a scheduler; that job belongs to Application.OnTime, not Timer.

How accurate is the VBA Timer function?

Timer resolves to about a hundredth of a second (roughly 10 ms) on Windows. That is accurate enough to compare two approaches or time an import, but operations faster than ~10 ms will report 0. For microbenchmarks, run the operation many times and divide, or use the QueryPerformanceCounter Windows API.

Tested in

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

Related guides: VBA Wait · VBA Sleep · VBA ScreenUpdating · VBA Calculation · VBA DoEvents