Wait and Delay Methods

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:

Each method has different characteristics for precision, ease of use, and concurrent processing. 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
• 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

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

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
• Precision: High
• Concurrency: Processes other events during wait

Advantages

• Allows concurrent processing: Other VBA operations can run during the wait
• High precision: Good accuracy at the second level

Disadvantages

• More complex code: Requires a loop structure
• Higher CPU usage: Continuous loop execution increases processor load

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:

• Other operations must continue 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:

Choose the method that best fits your specific requirements for precision, simplicity, and concurrent processing needs.