VBA Wait

VBA Wait Function

Some of the codes require sometime before progress to the next line of code due to other tasks to be completed. In these cases, we need to stop the code to be executed and pause for some time then proceed with the execution. We can pause the code to be executed in two ways, the first one is “Sleep” method and the second one is “Wait” method. In our earlier article, we have discussed the “VBA Sleep” method to pause the VBA code, in today’s article we will see how to use the excel “VBA Wait” Function.

What is Wait in VBA Excel?

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

The syntax of the VBA WAIT Function is as follows.

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

Until the specified time arrived it says FALSE and the moment specified time arrived it returns TRUE.

This is unlike SLEEP function because WAIT is a built-in function where SLEEP is a Windows Function. Before we access the SLEEP function we need to mention the below code at the top of the module. But WAIT doesn’t 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

Examples

Below are the examples of Excel VBA Wait.

Example #1

Assume you are working in an excel mid-day at 14:30:00 and you want your code to be paused 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 refer this in your code?

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

Just to remind you NOW () function returns current date and time as per your computer system. 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.

To stop or pause your code from being executed from the current time to next 10 minutes you can use below code.

Code:

Sub Wait_Example2()

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

End Sub

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

Example #2

Wait for 10 Seconds Every time Loop Runs

Wait method in VBA is well used with loops. There are situations where you may require to wait for 10 seconds every time loop runs. For an 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. 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 the completion of the first line, it will wait for 10 seconds before it calculates the next line.

VBA Sleep vs VBA Wait

There are some differences in VBA Sleep & VBA Wait. Below are some of them.

VBA SLEEP		VBA WAIT
Sleep is not a VBA built-in function, needs a special code to access this function.		The wait is a VBA built-in function, doesn’t require any special code to access this function.
Sleep requires milliseconds as the time frame.		Wait requires regular time frame.
We can delay the code in milliseconds		We can delay only in whole seconds.

Recommended Articles

This has been a guide to VBA Wait Function. Here we discuss how to use wait method in excel VBA with the help of practical examples and downloadable excel sheet. You can learn more about VBA from the following articles –

• Comment VBA Block using REM Keyword
• Examples of Excel VBA Intersect
• Use VBA ChDir Function to Change Directory
• Examples to  Hide Columns in Excel VBA
• Rename Sheet in Excel VBA
• Split Function in VBA
• InStr VBA examples
• Free VBA Course
• Operators in VBA