Excel VBA Wait Function

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 SleepVBA SleepVBA Sleep is a windows function, present under the Windows DLL Files, which pauses the program execution for a specific time period (even in milliseconds).read more” method to pause the VBA codePause The VBA CodeVBA Pause helps to pause a code from executing for a particular period. You can pause the VBA code for a specific period by using two functions, and those functions are “Wait” & “Sleep.”read more.

“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:

Examples to use Excel VBA Wait Function

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.

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 NOWVBA NOWNOW is a date and time function in VBA which is used to get the current system date and time. VBA Now function does not takes any arguments and the return output for this function is date.read more 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.

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.

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.

VBA Sleep vs. VBA Wait

This article has been a guide to VBA Wait Function. Here, we discuss how to use the Wait method to pause the code from running with the help of practical examples and downloadable Excel sheets. You can learn more about VBA from the following articles: –

  • VBA IntersectSplit Function in VBAOperators in VBA