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.
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 IfCalling 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 secondsMaximum 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
Mike Wolfe
Mike Wolfe
Image by PublicDomainPictures from Pixabay
