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.
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
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:
For more information about 32-bit and 64-bit VBA compatibility, refer to Philipp Stieffel's authoritative article on the topic.