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.
Table of contents
- VBA DoEvents is crucial to keep the system and Excel files from going unresponsive while lengthy code is running.
- If you don’t use VBA DoEvents, your system may freeze or crash, especially if you use the application frequently.
- To add a waiting or pause function to your macros, use “VBA DoEvents wait” in combination with “Application.Wait.” This method keeps the user interface responsive while your VBA code runs and allows for controlled timing intervals.
- You can use “VBA DoEvents Sleep” in conjunction with other methods like “custom loops” or “Application.Wait” to create a sleeping or waiting effect.
- You can improve user interaction in Excel macros by using VBA DoEvents activate in conjunction with activation commands.
How to Use DoEvents Function?
To use the DoEvents function in Excel VBA, follow these steps.
- Open your Excel workbook and press ALT + F11 to access the Visual Basic for Applications (VBA) editor.
- Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
- Put the DoEvents function in your VBA code where you want it.
' Your code here
' Continue your code
- You can run your VBA macro from the VBA editor, or you can assign it to an Excel button or shortcut.
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.
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
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
MsgBox “The sum of numbers from 1 to 1000 is: ” & total
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:
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”)
MsgBox “Updates completed!”
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:
Dim userInput As String
userInput = InputBox(“Enter a value:”)
If userInput <> “” Then
MsgBox “You entered: ” & userInput
MsgBox “No input provided.”
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)
If VBA DoEvents isn’t working, it could be because of overuse, inadequate error handling, or problems with the logic in your macro. Scrutinize your code to find any possible problems.
Yes, there could be disadvantages, such as heightened complexity, decreased performance, and the requirement for strong error handling. Unpredictable behavior can also result from overusing VBA DoEvents.
Yes, there are other options like optimizing your code, using background worker threads, or dividing tasks into smaller portions to enable the system to process events more frequently.
Yes, you can give the system control regularly by using VBA DoEvents in a loop. To prevent excessive overhead and potential performance issues, exercise caution when it comes to the frequency of calls.
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.
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 –