How to Pause VBA Code

Mike Wolfe -

The simplest way to add a pause to your VBA code is with the Windows Sleep API.

#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

Sample Usage

As this is an API call, it has to be declared in the header section of a module.  

I generally declare it as Public in the header section of a module that I include in every application (for me, that's my error logging module).  

Here's what it looks like in context.  Note that the amount of time to sleep is given in milliseconds, not seconds.  So, if you want to pause your code for three seconds, you would call Sleep 3000.

Option Explicit

#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 ThreeSecondSleepTest()
    Debug.Print "Start: "; Now
    
    Sleep 3000   'Pause for 3,000 milliseconds (i.e., 3 seconds)
    
    Debug.Print "End:   "; Now
End Sub

While your code is sleeping, msaccess.exe relinquishes its use of the processor so that other processes may execute their code.  This is a much more efficient and reliable way to pause your code than ugly hacks like a long-running DoEvents loop.

Image by 愚木混株 Cdd20 from Pixabay