[VBA] How to Pause Macro Execution in Milliseconds (ms) using Sleep API

Introduction

When executing a macro in VBA, there are situations where “the processing is too fast to visually track what is happening” or “you want to insert a very brief weight (waiting time) between specific processes.”

VBA has a built-in waiting command called Application.Wait, but it can only specify time in seconds. This makes it unsuitable for precise control, such as stopping the process for only 0.1 seconds.

To achieve pauses in milliseconds (1/1000th of a second), we use the Windows API Sleep function. In this article, I will explain how to accurately suspend VBA execution for a specified number of milliseconds using the Sleep API.

VBA Sample Code: Pausing in Milliseconds

The Declare statement must be placed at the very top of the module (before any Sub or Function procedures).

This macro implements a simple animation-like effect that highlights cells in Column B one by one at intervals of 0.2 seconds (200 milliseconds).

Complete Code

'--- Declare the API function at the very top of the module ---

' Support for both 64-bit and 32-bit VBA
#If VBA7 Then
    ' API function to suspend execution for a specified number of milliseconds
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If


' Highlight cells at 0.2-second intervals
Sub HighlightCellsWithDelay()
    Dim i As Long
    
    For i = 2 To 10
        '--- Highlight row i in Column B ---
        With Cells(i, "B")
            .Interior.Color = vbYellow
            .Font.Bold = True
        End With
        
        '--- Suspend processing for 200 milliseconds (0.2 seconds) using the Sleep function ---
        Sleep 200
        
        '--- Reset the highlight ---
        With Cells(i, "B")
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
    Next i
    
    MsgBox "Processing complete."
End Sub

Explanation of the Code

1. Declare PtrSafe Sub Sleep …

This is the declaration required to make the Windows API Sleep function available within VBA.

• Lib “kernel32”: Indicates that this function is contained within the kernel32.dll library, which houses basic Windows functionality.
• ByVal dwMilliseconds As Long: Defines that the Sleep function receives the desired pause duration as a Long type integer in milliseconds.

2. Sleep 200

This is the core line that actually pauses the process.

• Sleep: Calls the API function.
• 200: By passing 200 as an argument, you are commanding: “Stop program execution for 200 milliseconds.”

By placing this single line inside a For...Next loop, a 0.2-second weight is added to each iteration, allowing you to visually follow the changes in cell colors.

Summary

In this article, I explained how to precisely pause macro execution in milliseconds using the Windows API Sleep function.

• Declare the Sleep API and call it by specifying the duration in milliseconds as an argument.
• 1 second = 1000 milliseconds.

The Sleep API is extremely effective when Application.Wait is too long or when you need a very short waiting period. Please use this technique when you want to visually demonstrate process progress to users or when a brief wait is required for communication with external systems.