vba · DOCS

Wait and Delay Methods

Maintained on

When you need to pause execution for a specific duration in VBA, three common methods are available: Sleep, Application.Wait, and DoEvents. This article explains each method’s usage, features, advantages, and disadvantages.

Comparison of Methods

Here’s a quick comparison of the three approaches:

MethodUnitPrecisionProsCons
SleepMillisecondsHighHigh precision, simple codeRequires API declaration, blocks other operations
Application.WaitSecondsMediumEasy to use, built into ExcelLower precision, blocks other operations
DoEventsLoop-dependentLoop-dependentKeeps the UI responsive while waitingMore complex code, higher CPU usage

Each method has different characteristics for precision, ease of use, and UI responsiveness. Let’s examine each in detail.

Sleep: Using Windows API

Usage

The Sleep function uses the Win32 API to wait for a specified number of milliseconds:

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub WaitWithSleep()
    Sleep 3000 ' Wait 3000 milliseconds (3 seconds)
End Sub

Characteristics

  • Unit: Milliseconds
  • Precision: High (actual delay depends on the OS timer resolution)
  • Requirement: Win32 API declaration (kernel32.dll)

Advantages

  • High precision: Allows millisecond-level timing
  • Simple code: Short and straightforward implementation

Disadvantages

  • API declaration required: Not a built-in VBA feature; requires external Windows API
  • Blocks other operations: All VBA processing stops completely during the wait, and the Excel window may appear frozen
  • Windows only: kernel32 is not available in Excel for Mac
In preparation

The page you are looking for is currently under preparation. Please wait a moment.

Application.Wait: Excel’s Built-in Method

Usage

Application.Wait is an Excel VBA method that pauses until a specified time:

Sub WaitWithApplicationWait()
    Application.Wait (Now + TimeValue("00:00:05")) ' Wait 5 seconds
End Sub

Characteristics

  • Unit: Seconds (not suited to delays under 1 second)
  • Precision: Medium
  • Scope: Excel VBA only

Advantages

  • Easy to use: No API declarations needed; works directly in Excel VBA
  • Built-in feature: No external libraries or APIs required

Disadvantages

  • Lower precision: Only second-level accuracy
  • Blocks other operations: All VBA processing stops during the wait
In preparation

The page you are looking for is currently under preparation. Please wait a moment.

DoEvents: Processing Events While Waiting

Usage

DoEvents is used within a loop to process other events while waiting:

Sub WaitWithDoEvents()
    Dim endTime As Double
    endTime = Timer + 5 ' Set end time to 5 seconds from now
    Do While Timer < endTime
        DoEvents ' Process other events
    Loop
End Sub

Characteristics

  • Unit: Seconds (judged with the Timer function)
  • Precision: Depends on the loop
  • Concurrency: Processes events such as repainting and user input during the wait

Advantages

  • Keeps the UI responsive: Repainting and user input are processed during the wait. VBA is single-threaded, so other VBA code does not run in parallel

Disadvantages

  • More complex code: Requires a loop structure
  • Higher CPU usage: Continuous loop execution increases processor load
  • Midnight rollover: Timer resets to 0 at midnight, so a wait that spans midnight may never end
  • Re-entrancy: The user can trigger the same macro again (for example, by clicking a button) while it waits
In preparation

The page you are looking for is currently under preparation. Please wait a moment.

Choosing the Right Method

Use Sleep When:

  • You need millisecond precision
  • Blocking other operations is acceptable
  • You’re comfortable with API declarations

Use Application.Wait When:

  • You want the simplest implementation
  • Second-level precision is sufficient
  • You only need this in Excel VBA

Use DoEvents When:

  • The screen must keep repainting and responding to input during the wait
  • UI responsiveness is important
  • You’re willing to accept higher CPU usage

Practical Examples

Progress Bar with DoEvents

Sub ProgressBarExample()
    Dim i As Long
    Dim progress As Double

    For i = 1 To 100
        progress = i / 100

        ' Update progress bar (assuming UserForm with ProgressBar)
        Application.StatusBar = "Processing: " & Format(progress, "0%")

        ' Allow UI to update
        DoEvents

        ' Simulate work
        Sleep 50
    Next i

    Application.StatusBar = False
End Sub

Waiting for External Process

Sub WaitForFile()
    Dim filePath As String
    Dim timeout As Double
    Dim startTime As Double

    filePath = "C:\Temp\output.txt"
    timeout = 30 ' Maximum wait time in seconds
    startTime = Timer

    Do While Not FileExists(filePath)
        If Timer - startTime > timeout Then
            MsgBox "Timeout: File not found", vbExclamation
            Exit Sub
        End If
        DoEvents
        Sleep 500 ' Check every 500ms
    Loop

    MsgBox "File found!", vbInformation
End Sub

Function FileExists(path As String) As Boolean
    FileExists = (Dir(path) <> "")
End Function

Summary

VBA offers three main approaches for timed delays:

Use CaseRecommended Method
High-precision timingSleep
Simple delay in ExcelApplication.Wait
Responsive UI during waitDoEvents

Choose the method that best fits your specific requirements for precision, simplicity, and UI responsiveness.

  • #VBA
  • #Sleep
  • #Application.Wait
  • #DoEvents
  • #Timing