VBA Wait Function

VBA Wait is a built-in function to pause the code from executing for a specified time. It is very similar to what we do in the Sleep command. To pause a code, we use the Application.Wait method.

Some codes require some time before progressing to the next line of code due to completing other tasks. In these cases, we need to stop the code from being executed, pause for some time, and then proceed with the execution. We can pause the code to execute in two ways: the sleep method and the Wait method. Our earlier article discussed the “VBA Sleep” method to pause the VBA code.

"Wait," as the name says, will hold the macro code to be executed to a specified time frame. Using this method, we need to specify when our code should pause. We will see examples next.

The syntax of the WAIT function is as follows.

We need to mention the amount of time our code should pause. As you can see in the end, it says Boolean. It means it returns the result as Boolean values: TRUE or FALSE.

Until the specified time arrives, it says FALSE. The moment the specified time arrives, it returns TRUE.

It is unlike the SLEEP function because WAIT is a built-in function, where SLEEP is a Windows Function. So, before we access the SLEEP function, we need to mention the code below at the module's top. But WAIT does not require this.

Code:

#If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) ‘For 64 Bit Systems #Else Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ‘For 32 Bit Systems End If

Example #1

Assume you are working in an excel midday at 14:30:00. You want your code to pause until the time becomes 14:40:00. You can use the below code.

Code:

Sub Wait_Example1() Application.Wait "14:40:00" End Sub

The code will stop your excel from working until the time reaches 14:40:00 in your operating system. Providing time like this is dangerous because we don't always work from 14:30:00. It keeps varying all the time.

Let's say whenever you are running the code. You want to wait for 2 minutes. How do you refer to this in your code?

So, we can use the VBA NOW function with the TIME VALUE function to enter the specified time from the current time.

The NOW () function returns the current date and time per your computer system. The TIMEVALUE function represents the time from 00:00:00 to 23:59:59 i.e. 11:59:59 P.M in 24 hours format. It converts the string value to a time value.

For example NOW () + TIMEVALUE (00:02:30) means Current Time + 2 min 30 sec.

If the current time is 14:25:30, then it becomes 14:28:00.

You can use the code below to stop your code from being executed from the current time to the next 10 minutes.

Code:

Sub Wait_Example2() Application.Wait (Now() + TimeValue("00:10:00")) End Sub

It is important to use a NOW () function for accurate pauses. Otherwise, there are chances your excel workbook will pause until midnight. However, we can come out of the pause method at any point in time by pressing the Esc key or the Break Key.

Example #2

Wait for 10 Seconds Every time Loop Runs

One may use the Wait method with loops. However, there are situations where you may require to wait for 10 seconds every time the loop runs. For example, look at the below data.

To calculate Profit = (Sales – Cost), you want to create a loop, and after every loop, you want to wait for 10 seconds to check whether the result is accurate or not. The below code will do that.

Code:

Sub Wait_Example3() Dim k As Integer For k = 2 To 9 Cells(k, 4).Value = Cells(k, 2) - Cells(k, 3) Application.Wait (Now() + TimeValue("00:00:10")) Next k End Sub

This code will calculate the profit column line by line. After completing the first line, it will wait 10 seconds before calculating the next line.