VBA DoEvents

What is Excel VBA DoEvents Function?

Excel’s VBA DoEvents function is an effective tool for controlling the responsiveness of your VBA macros. It also enables the system to handle other events while a macro is executing. Excel may become inoperable until the VBA macro is completed because it usually operates as a single, continuous process when it is executed.

You can give the Excel application temporary control over user interactions, screen updates, and other events by placing VBA DoEvents strategically throughout your code. Below is an example of the use of the VBA DoEvents function to facilitate user interaction within a loop.

A message box with the text “Iteration X” appears within a “For loop,” where “X” is the value of “i” at that moment. The user is informed about the loop’s progress through this visual feedback. Crucially, the loop makes use of the DoEvents function to make sure that users can interact with the message box that is displayed while the loop is still running.

The loop moves on to the next iteration after each message box is displayed, and the user has the opportunity to interact with it.

A final message box with the text “Loop completed!” appears after all three iterations are finished, indicating that the process is now complete.

How to Use DoEvents Function?

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.

How to Interrupt the Code Running?

Sometimes, you may need to interrupt a running VBA macro that uses DoEvents. Here’s how you can do it.

• Step 1: Stop the Macro Execution

You can use your keyboard to press Ctrl + Break (or typically Ctrl + Fn + Pause/Break key) while the macro is running. It will cause the macro’s execution to pause.

• Step 2: Debug or Abort

The next step is to select whether to stop the macro by selecting the “Stop” button or debug the code by selecting the “Debug” button in the VBA editor.

Examples

Example #1 – Responsive User Interface

Let’s say you have a VBA macro that executes a complex computation. Excel stops responding without DoEvents until the computation is finished. You can make sure that the user interface stays responsive and that users can cancel the operation or work with Excel while the macro is running by strategically placing DoEvents throughout your code.

• Step 1: We start by defining a new subroutine called LengthyCalculation.

• Step 2: We declare two variables: total to store the sum of numbers and i to use as a loop counter.

• Step 3: We start a loop that iterates from 1 to 1000, performing the following steps inside the loop.

• Step 4: Inside the loop, we calculate the running total by adding the current value of i to total.

• Step 5: We check if the value of i is a multiple of 100. If it is, we enter the condition.

• Step 6: We use the DoEvents function to yield control to the Excel system. It allows Excel to process other events while the macro is running.

• Step 7: We end the condition and continue looping until i reaches 1000.

• Step 8: Here, we use a “MsgBox” function that shows the sum of numbers from 1 to 1000.

• Step 9: Save the macro and click on run. The code uses VBA DoEvents to keep Excel responsive while summing numbers from 1 to 1000 over an extended period and periodically handing control back to the system. Upon completion, the sum of the numbers from 1 to 1000 appears in a message box.

Here is the complete code

Sub LengthyCalculation()
Dim total As Double
Dim i As Long
For i = 1 To 1000
total = total + i
If i Mod 100 = 0 Then
DoEvents ‘Yield control to the system
End If
Next i
MsgBox “The sum of numbers from 1 to 1000 is: ” & total
End Sub

Example #2 – Updating a Cell Value with a Timer

In this example, we will update a cell value in an Excel worksheet once every second using the DoEvents function.

• Step 1: In the new module, we start by creating a subroutine named “UpdateCellValueWithTimer” and declaring a variable “i” of type Integer. This variable will be used as a loop counter.

• Step 2: We begin a For loop that will iterate from 1 to 5. This loop will update a cell value for 5 seconds.

• Step 3: Inside the loop, we use the ‘Cells’ function in VBA to update the value of cell A1 (row 1, column 1) in the active worksheet. The cell is updated with the text “Update ” followed by the current value of i.

• Step 4: We give the Excel system control by using the DoEvents function. Excel can now handle additional events and user interactions while the loop is in operation. In this case, it ensures that the Excel interface remains responsive.

• Step 5: We pause the execution of the code for one second using the “Application.Wait” method. The time now is represented by Now, and a one-second time interval is specified by TimeValue(“00:00:01”). This makes the loop iterate once every one second.

• Step 6: We continue with the next iteration of the loop. This loop will run five times, each time updating the cell value, yielding control to the system with DoEvents, and waiting for 1 second.

• Step 7: After the loop completes (i.e., after 5 seconds), we display a message box with the text “Updates completed!” to indicate that the updates have finished.

• Step 8: Now, save the macro and click on run. For five seconds, this code updates cell A1’s value in an Excel worksheet every second. To keep the system responsive, it periodically gives control to the system through the use of VBA DoEvents.

It then shows a message box indicating that the updates have finished.

Here is the full code:

Sub UpdateCellValueWithTimer()
Dim i As Integer
For i = 1 To 5
Cells(1, 1).Value = “Update ” & i
DoEvents ‘Yield control to the system
Application.Wait Now + TimeValue(“00:00:01”)
Next i
MsgBox “Updates completed!”
End Sub

Example #3 – Handling User Input

Here, the goal is to ask the user for input while a macro is executing. DoEvents lets you handle user input without stopping the macro from running.

• Step 1: First, we define a new subroutine called GetUserInput.

• Step 2: We declare a variable userInput to store the input from the user and use the InputBox function to prompt the user to enter a value.

• Step 3: Here, we check if the user provided input (the input box wasn’t canceled). If input is provided, we display a message box with the entered value. If no input is provided, we display a different message.

• Step 4: Save the macro and click on run. This code uses an input box to prompt the user to enter a value.

The macro then runs in response to the user’s input, showing either a message indicating no input was provided or a message box containing the entered value, if one was entered.

Here is the full code:

Sub GetUserInput()
Dim userInput As String
userInput = InputBox(“Enter a value:”)
If userInput <> “” Then
MsgBox “You entered: ” & userInput
Else
MsgBox “No input provided.”
End If
End Sub

Important Things to Note

• Although VBA DoEvents is a helpful tool for enhancing VBA macro responsiveness, it should only be employed sparingly. Use of DoEvents can result in predictable behavior and make debugging your code more complex.
• Verify that your VBA code has the necessary error handling to deal with unforeseen circumstances that might arise when utilizing VBA DoEvents.
• The performance of your macro may be affected by using VBA DoEvents because it frequently gives the system control. Recognize that there may be trade-offs between performance.
• If you want responsiveness without depending entirely on VBA DoEvents, think about using background worker threads or optimizing your code as a VBA DoEvents alternative.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA DoEvents, with it features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA DoEvents. Here we learn the DoEvents function in Excel VBA code & how to use it, with examples & points to remember. You can learn more from the following articles –

• VBA ME
• VBA FILECOPY
• VBA Font Color