How to Add Delay During Code Execution Using VBA

1. Example of Adding Delay Time in VBA With the wait Command
2. Implement an Auto-Upate Scheme Using the wait Command

When dealing with complex calculations and processes using VBA, the programmer should keep in mind that some algorithms are too heavy that they need some time to process. The problem arises when the current process is still executing, but then the VBA compiler proceeds with the next process. In that case, overflows and errors would occur.

A typical application of the delay is when dealing with web scraping tools. The website should be fully loaded first before scraping data. Otherwise, an error will occur. Another application of this is when we need to run a macro regularly for a defined time frame.

This article will demonstrate how to add delay time using the wait command.

Syntax:

Application.Wait(Now + [delay time])

Where:

Example of Adding Delay Time in VBA With the wait Command

Sub DelayMe()
'Print the current time
Debug.Print Now

'Delay for 10 seconds start here
Application.Wait(Now + TimeValue("00:00:10"))

'Print the current time
Debug.Print Now

End Sub

Output:

12/21/2021 4:41:25 PM 
12/21/2021 4:41:35 PM 

Implement an Auto-Upate Scheme Using the wait Command

The code block below will demonstrate implementing auto-update schemes using the wait command. It will call the GetTimeNow subroutine every 10 seconds.

Sub AutoUpdate()

'Main loop where the code execution takes place 
Do

'Add a delay for every call
Application.Wait(Now + TimeValue("00:00:10"))
'Calls the GetTimeNow subroutine
Call GetTimeNow
Loop

End Sub

Sub GetTimeNow

'Print the time at the time of the call.
Debug.Print "The current time is: " & Now

End Sub

Output:

The current time is: 12/21/2021 4:59:30 PM
The current time is: 12/21/2021 4:59:48 PM
The current time is: 12/21/2021 5:00:01 PM
The current time is: 12/21/2021 5:00:16 PM