VBA Wait Function

What is Excel VBA Wait Function?

Excel VBA (Visual Basic for Applications) does not have a built-in “Wait” function, but you can create a delay or pause in your VBA code by using the Application.Wait method—the Application.Wait method suspends the execution of VBA code for a specified period. It is helpful for creating delays in your macros or for waiting for specific events to occur. Consider the following example:

A subroutine has been defined to pause all the Workbook processes by 1 second and print the clear message in the Immediate tab, as shown below.

How to Use Excel VBA Wait Function?

You can learn how to use the VBA Wait function by following the steps below.

Step 1: In the Excel Workbook, go to the Developer section in the toolbar and click “Visual Basic” from the far left-hand side.

In the VBA Module, in the title bar, click the “Insert” button and select the “Module” option.

Step 2: Name the subroutine you have created to call the VBA Wait function using a variable.

Step 3: Get the current hour with the VBA Now function and use the Hour() function in VBA to get out the hour. A similar is done for minutes and seconds in the next step.

Step 4: Similarly, store the minute part in a new variable.

Step 5: Store the seconds part of the time in another variable. But add the number of seconds you want to freeze the Workbook for.

Step 6: Using the VBA TimeSerial function, club the hour, minute, and second gotten from the earlier steps.

Step 7: Call the VBA Wait function and use the variable used earlier.

It will pause the workbook for 5 seconds.

Step 8: Print the message after the freeze has been completed in the Immediate tab.

Code:

Sub Var_eg()

    newHour = Hour(Now())

    newMinute = Minute(Now())

    newSecond = Second(Now()) + 5

    waittime = TimeSerial(newHour, newMinute, newSecond)

    Application.Wait waittime

    Debug.Print “5 seconds have gone by”

End Sub

Step 9: Click the “Run” icon on the activity bar in the Excel VBA Module to run the program.

Let’s see some interesting examples that use the VBA Wait function to tackle issues.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples to use Excel VBA Wait Function

See how the VBA Wait function is used in a developer’s life by viewing some simple examples.

Example #1

Learn how to view the step-by-step calculation of finding the sum of 5 consecutive numbers by pausing the process every second.

It is a crucial step. Developers perform debugging in their VBA code. They split apart the process and introduced delays to find out which component was causing the error.

To do so, you need to write a subroutine to do as such.

Step 1: Name the subroutine you want to perform the above task.

Step 2: Introduce two VBA variables to store the result of the additions and an iterative variable to loop around five times.

Step 3: Assign the result as 0.

Step 4: Initialize a FOR loop to add the result with the iterative variable.

Step 5: Pause the subroutine for 1 second.

Step 6: Print the sum values in the Immediate tab. And continue the loop.

This loop continues until i=5, and then the loop ends.

Code:

Sub add_5_numbers()

    Dim res As Double

    Dim i As Integer

    res = 1

    For i = 1 To 5

        res = res + i

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

        Debug.Print “Sum is: ” & res

    Next i

End Sub

Step 7: Press “F5” to run the code. The output is displayed as shown below:

Every 1 second, a new value is printed.

Example #2

Learn how to view multiple tasks as completed by pausing the routine at every step.

Hence, you’ll want to debug it. To do so, you’ll need to create delays so that you can follow the execution of a code, especially with multiple tasks. To do so, you’ll need to start with creating your subroutine.

Step 1: Declare a subroutine to multiply ten numbers.

Step 2: Declare a results and iterative variable.

Step 3: Declare the result value as 1. It is because we are trying to find the product of all ten numbers.

Step 4: Create a for loop to multiply the result with the iterative variable.

Step 5: After adding, wait for a second, then print the value in the immediate tab. Continue the loop till it ends.

Step 6: Declare another subroutine to simulate working with multiple processes.

Step 7: Call the subroutine to multiply ten numbers as declared in the earlier steps.

Step 8: After the process is done, print in the Immediate tab as “completed.”

Step 9: Freeze the Workbook for 2 seconds, and then print its successful completion in the Immediate tab.

Code:

Sub multiply_10_numbers()

    Dim res As Double

    Dim i As Integer

    res = 1

    For i = 1 To 10

        res = res * i

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

        Debug.Print “Product of number ” & i; ” is: ” & res

    Next i

End Sub

Sub WitnessStepByStepExecution()

    multiply_10_numbers

    Debug.Print “Task 1 completed”

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

    Debug.Print “Task 2 completed”

End Sub

Step 10: Run the second subroutine, WitnessStepByStepExecution, specifically to witness the breakdown of how Excel VBA works.

VBA Sleep vs. VBA Wait

In VBA, “Sleep” and “Wait” are two different methods used to introduce delays or pauses in your code execution, but they serve slightly different purposes and have some differences:

VBA SLEEP	VBA WAIT
1. Sleep is not a built-in VBA function; you need to use the Windows API function Sleep. The import function is: Public Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As LongPtr) Note: This is used in systems with 64-bit kernels. The code is a little different for 32-bit systems (in Excel 2007 or earlier). Public Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)	1. Application.Wait is a built-in VBA method used to introduce a delay by waiting for a specific time or condition.
2. It is used to introduce a delay in milliseconds.	2. It is used to pause an event for seconds.
3. It doesn’t freeze the entire Excel application, so it’s more suitable for longer delays or for situations where you don’t want to block the user interface.	3. It can make Excel unresponsive during the waiting period, so it’s generally not recommended for long or unpredictable delays.
4. It is often used for creating precise timing delays, such as waiting for a specific period without affecting other application processes.	4. It is often used to pause code execution until a certain time or until a condition is met.

Important Things To Note

• The Application.Wait function can make Excel unresponsive during the waiting period, hence, the Application.Wait function can make Excel unresponsive during the waiting period.
• It’s generally not recommended to use an Application.Wait for long or unpredictable delays.
• The Application.Wait function may not be exact, especially for very short time intervals till the second’s denomination.
• When specifying the time to wait until in the Application.Wait, ensure that you use the correct time format.
• When using an Application.Wait in a loop, and ensure that there is a condition that can eventually be met.
• Do declare the time in a string inside the Application.Wait function. Or else it will return the VBA Wait function not defined error.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Wait Function. Here we explain the How to use Excel VBA Wait Function along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Pause
• VBA Progress Bar
• VBA Timer