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

VBA Sleep in Excel — The Windows API Call, the 64-bit PtrSafe Trap, and Wait vs Sleep

|

VBA Sleep in Excel — The Windows API Call, the 64-bit PtrSafe Trap, and Wait vs Sleep

TL;DR — Sleep is not part of VBA. It is a Windows API function that pauses your macro for a number of milliseconds, and you have to declare it before you can call it. On 64-bit Excel that declaration must carry the PtrSafe attribute, wrapped in a #If VBA7 guard so it still compiles everywhere:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub PauseQuarterSecond()
    Sleep 250            ' 250 milliseconds = a quarter of a second
End Sub

People reach for Sleep when Application.Wait isn't enough — when they need to pause for a fraction of a second, not a whole one. And the very first thing that happens is a compile error, because Sleep is not a VBA command at all: it lives in Windows. This guide is built on that one idea — Sleep is a millisecond pause you borrow from the operating system — because it explains the declaration, the 64-bit trap, and why Sleep still can't give you a responsive pause.

What you'll learn

  • The mental model — Sleep is a Windows kernel32 function you borrow, not a VBA keyword
  • The 64-bit PtrSafe trap — the exact compile error and the exact #If VBA7 fix
  • Milliseconds, not seconds — the sub-second precision that is the whole reason to use Sleep
  • Why "milliseconds" is not "precise" — the OS scheduler floor of ~15 ms
  • Why Sleep still freezes Excel, and when to use Wait or a DoEvents loop instead

The mental model: a pause you borrow from Windows

Application.Wait is Excel's own tool. Sleep is not — it belongs to Windows, in a system library called kernel32. To use it, you reach outside VBA with a Declare statement that says, in effect, "there is a function called Sleep over in kernel32; here is its shape; let me call it." Only then can you write Sleep 250.

That "borrowing from Windows" is the mental model, and everything awkward about Sleep follows from it. A native VBA keyword would just work. A borrowed API function has to be declared, has to match the operating system's calling convention, and — crucially — has to be declared differently on 32-bit and 64-bit Windows. That last point is where almost everyone gets stuck.

The 64-bit PtrSafe trap: the error and the fix

This is the number-one Sleep problem, and it is the reason a macro that "worked on the old computer" suddenly refuses to run. The classic declaration you'll find in old code and old forum posts is:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)   ' pre-2010 style

Run that in modern 64-bit Excel and, before a single line executes, VBA stops with:

Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.

The fix has two parts. First, add the PtrSafe keyword, which tells VBA the declaration has been reviewed for 64-bit pointer safety. Second, wrap it in a #If VBA7 conditional-compilation block so the same file still compiles on ancient Excel versions that predate PtrSafe:

#If VBA7 Then
    ' Excel 2010 and later - 64-bit safe
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    ' Excel 2007 and earlier - no PtrSafe keyword exists
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

#If VBA7 is checked at compile time, not run time, so each Excel version only ever sees the one declaration it understands. Put this block at the top of a standard module, above any procedure, and Sleep compiles and runs on every modern Excel. If you only ever target 64-bit Excel 365, the PtrSafe line alone is enough — but the guarded version is the safe copy-paste.

Milliseconds, not seconds — the reason Sleep exists

Sleep takes milliseconds. Sleep 250 is a quarter of a second; Sleep 1000 is one second; Sleep 50 is a fiftieth. This is the entire reason to prefer Sleep over Application.Wait, which can only pause in whole seconds. If you need to throttle a loop to a few times a second, space out API requests, or wait a short beat for something to settle, Sleep is the tool with the right resolution.

Do
    ' ... check whether the export file has appeared ...
    If Dir(exportPath) <> "" Then Exit Do
    Sleep 200        ' poll five times a second instead of hammering the disk
Loop

But "milliseconds" is not "precise"

Do not mistake fine units for fine accuracy. Windows schedules threads on a tick of roughly 15.6 milliseconds, so Sleep 1 does not sleep for one millisecond — it sleeps until the next scheduler tick, typically around 15 ms. Sleep guarantees "at least this long," never "exactly this long," and the real pause is rounded up to the scheduler's granularity. That is fine for throttling and pacing, where you just want "roughly this often." It is the wrong tool if you need precise timing or accurate measurement — for measuring elapsed time, use Timer; for high-precision timing you would drop to QueryPerformanceCounter.

Sleep still freezes Excel

Here is the trap Sleep shares with Application.Wait: while it pauses, Excel is frozen. Sleep parks Excel's single thread for the duration and does not pump the message queue, so the screen does not repaint, clicks are not handled, and a long enough Sleep — or many short ones in a loop — makes the window go "Not Responding." Sub-second precision does not change this; a pause is a pause, and a blocked thread is a frozen Excel.

So Sleep, like Wait, is the wrong tool when the point of the pause is to let the user see something or do something. A pause where Excel stays alive is a loop that yields with DoEvents:

' Responsive sub-second pacing - Excel stays alive between beats
Dim nextBeat As Double
nextBeat = Timer + 0.25
Do While Timer < nextBeat
    DoEvents
Loop

The honest verdict: Wait, Sleep, or a DoEvents loop

Line the three up by what you actually need:

  • A whole-second pause, no fuss — Application.Wait Now + TimeValue(...). No Declare, no PtrSafe, nothing to get wrong. Reach here first for "wait ~2 seconds."
  • A sub-second pause — Sleep, because Application.Wait can't go below a second. Accept the Declare/PtrSafe ceremony as the price of millisecond resolution.
  • A pause where Excel must stay responsive — a DoEvents loop, because both Wait and Sleep freeze the window. This is the one people most often need and least often reach for.

The dare: don't add a Sleep "just in case" to slow a macro down. If a macro is misbehaving without an artificial pause, the pause is usually hiding a real bug — a value read before it was ready, an event that fired twice — and the honest fix addresses that, not a Sleep 500 sprinkled on top.

How ExcelMaster helps

The Sleep declaration is exactly the kind of boilerplate that is easy to get wrong and tedious to get right: the Lib "kernel32" string, the ByVal argument, the PtrSafe attribute, the #If VBA7 guard, and then the judgment call of whether Sleep is even the tool you wanted instead of Application.Wait or a DoEvents loop.

ExcelMaster writes the correct, 64-bit-safe declaration for you and, more importantly, picks the right waiting construct for what you described. Ask for "poll for a file every 200 ms" and it gives you a guarded Sleep loop; ask for "pause but let me cancel" and it gives you a DoEvents loop instead — so you never ship the pre-2010 Declare that crashes on 64-bit Excel, and never freeze Excel when you meant to keep it alive.

Frequently asked questions

How do I use Sleep in Excel VBA?

Declare it once at the top of a standard module, then call it with a millisecond value. Use the 64-bit-safe form: #If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) (with a plain Declare in the #Else branch for old Excel). Then Sleep 250 pauses the macro for 250 milliseconds — a quarter of a second.

Why does my Sleep Declare cause a compile error on 64-bit Excel?

Because the old declaration style predates 64-bit Office. Modern 64-bit Excel requires the PtrSafe attribute on every Declare statement, and without it you get "The code in this project must be updated for use on 64-bit systems… mark them with the PtrSafe attribute." Add PtrSafe after Declare, and wrap the line in a #If VBA7 block so it still compiles on older Excel too.

What is the difference between Sleep and Application.Wait?

Sleep is a Windows API call you must declare; it pauses for a number of milliseconds and gives sub-second precision. Application.Wait is built into Excel, needs no declaration, and pauses until a wall-clock moment with whole-second resolution. Use Sleep when you need finer than one second, and Application.Wait for simple whole-second pauses. Both freeze Excel while they wait.

Does Sleep make Excel unresponsive?

Yes. Sleep blocks Excel's single thread for the whole pause and does not process messages, so the window cannot repaint or handle clicks and may show "Not Responding." If you need Excel to stay responsive during a pause — for progress or a Cancel button — use a loop that calls DoEvents instead of Sleep.

Is Sleep accurate to the millisecond?

No. Windows schedules threads on a tick of about 15.6 ms, so Sleep 1 actually pauses roughly 15 ms, and Sleep only guarantees "at least this long." It is fine for throttling and pacing, but not for precise timing. To measure how long code takes, use the Timer function instead.

Tested in

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

Related guides: VBA Wait · VBA Timer · VBA DoEvents · VBA On Error · VBA ScreenUpdating