VBA Wait and Sleep Commands to Pause and Delay

VBA Wait Command

In VBA, the WAIT command (method) helps you to put a wait on all the activities that you do in Excel for a particular time or up to a specific time. In simple words, you can make VBA wait for a few seconds, minutes, or even hours, or up to fix time. It has one argument that needs you to specify.

Steps to use VBA Wait

1. First, use the keyword “Application” and type a dot (.) to get the list of properties and methods.
2. After that, select or type the “Wait” method.
3. Now, specify the “Time” argument to tell VBA that how much time you want to wait.
4. In the end, run the code to put wait for all the activities in Excel.

In this code, you have used the NOW and TIMEVALUE (VBA Functions) to tell VBA to wait for ten seconds starting from the moment you run the code. So once the ten seconds passed the IF statement will test the condition and run showing you a message box with the message “Wait Over”.

Sub vba_wait_example()

If Application.Wait(Now + TimeValue("00:00:10")) = True Then
    MsgBox "Wait Over"
End If

End Sub

You can also use the Wait method to let Excel wait for all the activities up to a specific time. The following code waits until 01:00 PM. So, as it’s 12:52 in my system right now this code of line will make it wait for the next 8 minutes.

Application.Wait "13:00:00"

Note: With the wait method, you can only make the wait for a second not less than that.

VBA Sleep

Sleep is a Windows function (under windows DLL files; you need to import this function using a code statement) that can help you pause or add a delay while running a macro. In this function, you can specify the time in milliseconds, but you can’t stop the sleep function once it’s put everything on pause.

Use Sleep Function in VBA

1. First, you need to use the code statement to import the sleep function from the “kernel32 library”.
2. And then you need to make sure to append the “PtrSafe” statement if you are using 64 Bit Excel.
3. Next, you need to call the sleep function in the code.
4. In the end, specify the time (milliseconds) for which you want to delay the code.

If VBA7 Then
'For 64-Bit versions of Excel 
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Else
'For 32-Bit versions of Excel
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
End If

 Sub vba_sleep()
     Sleep (10000)
     'add code here
     MsgBox "Finished"
 End Sub

In the above code, you have 10000 milliseconds, which equals 10 seconds. When you run this code, it will delay the code for 10 seconds and then show a message box.

Note: When you use the sleep function, it stops everything in Excel for the time you have defined, you can’t even break it. Make sure to check out this link from Microsoft on Compatibility Between the 32-bit and 64-bit Versions.