VBA Wait

VBA Wait Function

Similar to sleep function Wait function in VBA makes a code to wait or pause for a few seconds. How such functions are used that we need to make some specific code to be on pause or hold to let some other program work first and then resume the other program. It is also called as Application.Wait method in VBA.

As the name suggests VBA Wait function in VBA is used to hold a program to a specific time so that other code can work during that time. The background applications keep running and only the current module waits.

Syntax for Wait Function in Excel VBA

The syntax to use Application.Wait function in VBA is as follows:

This function returns a Boolean value.

So if we need a code to wait till 2:00 PM today the syntax for the code will be as follows:

Application.Wait (“14:00:00”)

The above code will make the code to stop until 2:00 PM and the code will resume only after 2:00 PM. This is not optimal to make a code wait for 2 PM, in general, we want to wait for a certain code for another 10 seconds and then run. If I need to make code to wait for 10 seconds the code will be as follows:

Application.Wait (Now + TimeValue (“0:00:10”))

Now is the function which will take the current time and we are adding 10 seconds to the current time through time value function so that the code can wait or pause for 10 seconds.

How to Use Excel VBA Wait Function?

Currently, the time in my laptop is 3:50 pm and I want the code to resume at 4:00 PM. We will use simple addition to some variables but display the result after 4:00 PM. We want the code to wait for that period.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab and then click on Module.

Step 3: Once the code window opens up declare a sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Step 4: Declare three variables A B and C as an integer.

Code:

Sub Sample()

Dim A, B, C As Integer

End Sub

Step 5: Assign random values to A and B variable.

Code:

Sub Sample()

Dim A, B, C As Integer 
A = 10
B = 15

End Sub

Step 6: Use an application.wait function to make the function wait until 4:00 PM.

Code:

Sub Sample()

Dim A, B, C As Integer 
A = 10
B = 15
Application.Wait ("16:00:00")

End Sub

Step 7: Use the addition of A and B and store the output in C.

Code:

Sub Sample()

Dim A, B, C As Integer 
A = 10
B = 15
Application.Wait ("16:00:00")
C = A + B

End Sub

Step 8: Use a Msgbox function to display the value of C.

Code:

Sub Sample()

Dim A, B, C As Integer 
A = 10
B = 15
Application.Wait ("16:00:00")
C = A + B
MsgBox C

End Sub

Step 9: Run the above code. Once we run the code we see that code does not display a message until 4:00 pm but after 4:00 pm I get the following output.

I had to wait until 4:00 pm to see this result. The code of the Msgbox function had to wait actually.

VBA Wait Function – Example #2

Let us see the code with some personalized messages that the code will wait for 10 seconds and after 10 seconds have passed it will display that wait time has expired and displayed the final result.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

Step 3: Declare three variables A B and C as an integer.

Code:

Sub Sample1()

Dim A, B, C As Integer

End Sub

Step 4: Assign Random values to A and B variables.

Code:

Sub Sample1()

Dim A, B, C As Integer
A = 2
B = 10

End Sub

Step 5: Use a Msgbox function to display that wait time is about to start.

Code:

Sub Sample1()

Dim A, B, C As Integer
A = 2
B = 10
MsgBox "The Wait Time Is Started"

End Sub

Step 6: Use Wait function to wait for the code for 10 Seconds.

Code:

Sub Sample1()

Dim A, B, C As Integer
A = 2
B = 10
MsgBox "The Wait Time Is Started"
Application.Wait (Now + TimeValue("0:00:10"))

End Sub

Step 7: In Variable C it stores the value of B/A and displays a personalized message that wait period is over and then display the value of C.

Code:

Sub Sample1()

Dim A, B, C As Integer
A = 2
B = 10
MsgBox "The Wait Time Is Started"
Application.Wait (Now + TimeValue("0:00:10"))
C = B / A
MsgBox "The Wait time is Over"
MsgBox C

End Sub

Step 8: We run the code and see the first message like the following.

Step 9: Press OK to see the next result after 10 seconds have passed.

Step 10: When we press ok we get the final result.

In the above example, we made the code to wait for 10 seconds and then display the result of the value of C.

VBA Wait Function – Example #3

Now let us rename worksheets sheet 1 and sheet 2 as Anand and Aran Respectively but we want a gap of 5 seconds between both sheet being renamed.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

Step 3: Activate the first worksheet.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

Step 4: Rename the first worksheet as Anand.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"

End Sub

Step 5: Display a message that sheet 1 has been renamed and the code will pause for five seconds.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed now code will pause for 5 Seconds"

End Sub

Step 6: Use Wait function to make the code wait for five seconds.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed now code will pause for 5 Seconds"
Application.Wait (Now + TimeValue("0:00:05"))

End Sub

Step 7: Now rename the second sheet 2 as Aran.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed now code will pause for 5 Seconds"
Application.Wait (Now + TimeValue("0:00:05"))
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Name = "Aran"

End Sub

Step 9: Display a message that sheet 2 has been renamed and the wait has been passed.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Name = "Anand"
MsgBox "Sheet 1 renamed now code will pause for 5 Seconds"
Application.Wait (Now + TimeValue("0:00:05"))
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Name = "Aran"
MsgBox "The wait time is over and sheet 2 is also renamed"

End Sub

Step 10: Now run the code to see the result.

We can see that sheet 1 has been renamed as Anand and we get the message to wait for five seconds. Click on OK and wait for five seconds for the second message.

From the above examples, it is clear on how to use the Wait Function in VBA.

Things to Remember

There are a few things we need to remember about Excel VBA Wait Function:

• It is similar to Sleep function.
• The wait function is inbuilt in VBA unlike sleep function which is a windows function.
• Wait function takes Time as an argument.

Recommended Articles

This has been a guide to VBA Wait Function. Here we discussed how to use Excel VBA Wait Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA TIMER
2. VBA XML
3. VBA Time
4. VBA IsError