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

There is no built-in function to pause code in VBA. The easiest and safest way to do it is to use the Sleep API function. Here's a quick how-to.

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)
This code is not safe in MS Access 2003 and earlier.

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


External references

Sleep function (synchapi.h) - Win32 apps
Suspends the execution of the current thread until the time-out interval elapses.
Declare statement (VBA)
Learn how to use Declare statement (VBA)
PtrSafe keyword (VBA)
If...Then...Else directive
Windows API declarations in VBA for 64-bit
How to convert your API Declarations to 64-bit. - Common myths debunked, key factors explained!

Referenced articles

Compile Errors
Compile errors are easy to keep out of deployed code (just remember to Debug > Compile). Which is good because they hard crash Access in Runtime mode.
Office 2019 Runs in 64-bit Mode By Default. Here’s What That Means for VBA Developers
Through Office 2016, default installs used the 32-bit version of the software. Now that 64-bit is the default, it’s time to bite the bullet and convert your VBA code.

Image by PublicDomainPictures from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0