Excel VBA DoEvents and Wait Properties

In this article, we are going to discuss Excel VBA DoEvents and Wait property. These two property allows users to interact with the Excel GUI while any subroutine is running in the background. This improves user experience.

What Are VBA DoEvents and Application.Wait Properties?

When a VBA macro executes, it typically takes over control of the application and prevents other events from being processed until it has completed its task. This can be a problem if the macro takes a long time to run, as the application may become unresponsive, or the user may think that the macro has frozen. Using the VBA DoEvents function allows the macro to release control to the operating system periodically, so that it can process other events in the queue, such as user input or system messages. The DoEvents function returns control to the macro once all pending events have been processed.

Application.Wait is a built-in VBA function that causes the macro to pause for a specified period of time, allowing other processes to be executed before continuing. This function is useful when a macro needs to wait for a specific amount of time before continuing execution. The syntax for using Application.Wait is as follows:

Application.Wait(TimeValue)

Excel VBA DoEvents and Wait Properties: 2 Examples

In this article, we will discuss two used cases of Excel VBA DoEvents and Wait properties. We will use DoEvents in an infinite loop in the first example. In the second instance, we will use DoEvents and Application.Wait properties together.

Example 1: Using VBA DoEvents in Infinite Loop

In this example, we will incorporate DoEvents with an infinite loop. An infinite loop is a loop that runs for infinite time and steps. The user has to infare manually to stop an infinite loop. The following code is an infinite loop.

Sub DoEvents_in_InfiniteLoop()
For i = 1 To 10000
i = 5
Next i
End Sub

As we run this code by launching the VBA macro editor, the code runs for an indefinite time. During this period, we can not perform any other task in Excel. Because Excel will become responsive again when we execute the code completely. So, we will not be able to interact with our Excel file. The cursor will be circling indefinitely.

However, we can make our Excel sheet responsive while running an infinite loop by adding DoEvents in our code. The code is as follows,

Sub DoEvents_in_InfiniteLoop()
For i = 1 To 10000
i = 5
DoEvents
Next i
End Sub

Now, as we run the code, the infinite loop starts to run in the background. We have the DoeEvents property in the code. Thus, we can interact with our Excel sheet while the code runs.

Example 2: Applying DoEvents and Wait Property Together

In this instance, we are going to use the DoEvents and Application.Wait property together. We will run a for loop through the following dataset and the VBA code will calculate the profit by subtracting expense from revenue for each month. The DoEvents property will help us to interact while the loop is running and the Application.Wait property will delay each iteration.

We will run the following code which applies the DoEvents and Application.Wait property together.

Sub DoEvents_and_Wait()
Dim i As Integer
Dim Rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("DoEvents and Wait")
Set Rng = Range("B5:E16")
Application.Calculate
For i = 1 To 12
Rng.Cells(i, 4) = Rng.Cells(i, 2) - Rng.Cells(i, 3)
DoEvents
Application.Wait Now + TimeValue("00:00:01")
ws.Range("C18") = i
Application.Calculate
DoEvents
Next i
End Sub

VBA Code Breakdown

Sub DoEvents_and_Wait()
Dim i As Integer
Dim Rng As Range
Dim ws As Worksheet

This declares three variables: i as an integer, Rng as a range object, and ws as a worksheet object.

Set ws = ThisWorkbook.Worksheets("DoEvents and Wait")
Set Rng = Range("B5:E16")

This sets the ws variable to refer to the worksheet named “Sheet1” in the workbook containing the code. It sets the Rng variable to refer to the range B5:E16 on the “DoEvents and Wait” sheet.

Application.Calculate

This recalculates all the formulas on the active worksheet.

For i = 1 To 12
Rng.Cells(i, 4) = Rng.Cells(i, 2) - Rng.Cells(i, 3)
DoEvents
Application.Wait Now + TimeValue("00:00:01")
ws.Range("C18") = i
Application.Calculate
DoEvents
Next i

Here, the code runs a For Loop for 12 months of the dataset. Inside the loop, this subtracts the value in the cell in column D from the value in the cell in column C for the current row (i) and puts the result in the cell in column E.

The DoEvents statement allows Windows to process any pending events in the message queue, which can prevent the program from freezing up or appearing unresponsive. This also allows users to interact with the worksheet while the loop is running.

The Application.Wait statement pauses the program for one second before continuing to the next iteration of the loop. The code also writes the number of the month or the number of the iteration in the C18 cell.

As soon as we run the code, the code will start calculating the profit for each month. Due to the Application.Wait property, the code will pause for 1 second during each iteration. The DoEvents property will allow us to interact with the worksheet.

Frequently Asked Questions

What is the difference between DoEvents and wait?

DoEvents and Wait are two different ways of managing the timing and flow of code execution in a program.

DoEvents is a method that allows other events in the program to be processed while a long-running operation is being executed. When DoEvents is called, the program yields control to the operating system, which allows it to handle events such as user input or system messages. This can be useful for preventing a program from becoming unresponsive during long-running operations, as it allows the user to continue interacting with the program while the operation is ongoing.

Wait is a method that pauses program execution for a specified amount of time. When Wait is called, the program will not execute any further code until the specified amount of time has elapsed. This can be useful for implementing timing logic in a program, such as waiting for a certain amount of time before executing a specific action.

What is Sleep Function in VBA?

The Sleep function is a VBA method that causes a program to pause for a specified amount of time before continuing with execution. The Sleep function is part of the kernel32 library and is not a built-in VBA function, so it needs to be imported using the Declare statement.

The syntax for the Sleep function is as follows:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

What is the difference between the Sleep function and the Wait Function in VBA?

The Sleep function and the Wait function in VBA are similar in that they both cause the program to pause for a specified amount of time. However, there are some differences between the two functions that are important to note:

Importing: The Sleep function is not a built-in function in VBA and needs to be imported using the Declare statement, whereas the Wait function is a built-in VBA function and doesn’t require any special imports.
Time Units: The Sleep function takes the amount of time to pause in milliseconds (1/1000th of a second), whereas the Wait function takes the amount of time to pause in seconds.
Blocking: When we call the Sleep function, it blocks the execution of the entire program for the specified time, meaning that no other code can run during that time. In contrast, when we call the Wait function, it allows other code to continue running while it waits for the specified time.
Purpose: We typically use the Sleep function to introduce a delay in a program for a specific amount of time, for example, to allow a process to complete before continuing with the next instruction. We use the Wait function, on the other hand, to pause execution until a specific time has elapsed or a specific event has occurred.

Download Practice Workbook

You can download the practice book here.

DoEvents and Wait.xlsm

Conclusion

In this article, we have discussed Excel VBA DoEvents and Wait properties. These properties will allow users to make their worksheets more responsive while a loop is running or Excel is doing a heavy calculation. Don’t forget to share your experience in the comment section.

Get FREE Advanced Excel Exercises with Solutions!