How to Pause Your VBA Code For a Set Amount of Time

The most efficient way to pause execution of your VBA code is to use the Windows Sleep API function:

Suspends the execution of the current thread until the time-out interval elapses.

Usage

Declaring the function

As with all Windows API functions, before you can call the function from VBA you need to declare it using the VBA Declare statement.  

The following code should be placed in the header section of a standard module.  By including the Public keyword, you can call the function from any other module in your project.

#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

Calling the function

Whenever you want to pause execution of your code for a few seconds, you simply call Sleep and pass it the number of milliseconds you want your code to wait.  For example, to wait for 5 seconds, you would pass 5000 (1 second = 1,000 milliseconds):

Sleep 5000   'Pause execution for five seconds

Maximum compatibility

The above code works in all versions of Access from 95 through 365/2019, in both 32-bit and 64-bit flavors.

The PtrSafe keyword is what allows the call to work in both 32-bit and 64-bit environments.  This is especially important now that 64-bit is the default installation mode beginning with Access 2019/365.

However, the PtrSafe keyword was not introduced until VBA 7.  Thus, to avoid compile errors in older VBA versions, we include the conditional compilation directive #If VBA7.  Lines with a leading pound sign (#) are evaluated before the code is compiled.

The following code would raise a compile error in older versions of VBA (MS Access 2003 and earlier) because of the unsupported PtrSafe keyword:

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

For more information about 32-bit and 64-bit VBA compatibility, refer to Philipp Stiefel's authoritative article on the topic.

External references

Referenced articles

Image by PublicDomainPictures from Pixabay