Excel VBA DoEvents Alternative

In this article, you will learn about the Excel VBA DoEvents alternative.

Excel VBA’s DoEvents function is commonly used to allow a running macro to process other events, such as user input, while still performing its tasks.

While DoEvents can be helpful in certain scenarios, it has some limitations that might affect code responsiveness and performance.

In this tutorial, we’ll explore the drawbacks of DoEvents and discuss alternative approaches to improve your VBA code’s efficiency and user experience.

Download Practice Workbook

You can download the practice book from the link below.

Overview of VBA DoEvents Function

In Visual Basic for Applications (VBA), the DoEvents function is a built-in function that temporarily gives control to the operating system during the execution of a macro or code. When DoEvents is called, VBA allows the operating system to process any pending events, such as user input, screen updates, or messages from other applications.

The DoEvents function is primarily used to prevent an Excel macro from becoming unresponsive or “locked up” while executing long-running tasks or loops.

By giving control to the operating system, DoEvents allows users to interact with the Excel workbook, respond to dialog boxes, or perform other tasks while the macro continues its execution in the background.

Excel VBA DoEvents Alternative: Using Application.OnTime Command

An alternative to the VBA DoEvents function is using the Application.OnTime command. The code below is a perfect alternative to our previous example. It shows the same output as using the DoEvents function.

Code:

Dim i As Double
Dim rownum As Integer, colnum As Integer
Dim isRunning As Boolean
Sub printevennum_Alternative()
    rownum = 5
    colnum = 2
    isRunning = True
    i = 2 ' Start with the first even number (2)
    DisplayEvenNumbers
End Sub
Sub DisplayEvenNumbers()
    If i <= 100 And isRunning Then
        Cells(rownum, colnum).Value = i
        colnum = colnum + 1
        If colnum > 6 Then
            rownum = rownum + 1
            colnum = 2
        End If
        progress = (i - 2) / 98 ' Adjust progress to start from 0 to 1
        Application.StatusBar = "Generating Even Numbers... " & Format(progress, "0%") & " Completed"
        i = i + 2 ' Increment to the next even number
        ' Schedule the next iteration after 1 second
        Application.OnTime Now + TimeValue("00:00:01"), "DisplayEvenNumbers"
    Else
        ' Clear the status bar once the loop is done
        Application.StatusBar = False
        isRunning = False
    End If
End Sub

Now execute the code to see the output.

See! This command also passes the control to the operating system, and you can perform any action while running the code. Besides, it won’t affect the overall performance.

Example of VBA DoEvents Function in Excel

When you run a long VBA code in Excel, it freezes the application, and you have to wait until it finishes. This can be frustrating as you can’t do other tasks during that time.

To avoid this issue, you can use Excel VBA DoEvents. It allows the Excel application to remain active, so you can use your time more efficiently.

With DoEvents, you can interact with other tools or applications and even interrupt or stop the code execution if needed.

We have prepared a code for you that uses Application.Wait to simulate a period of time. This will help us see the benefits of using the VBA DoEvents function.

• Press ALT+F11 to open the Visual Basic Editor window.
• Click Insert and select Module.

• In the Module window, insert your code.

Code:

Sub printevennum_doevents()
    Dim i As Double
    rownum = 5
    colnum = 2
    For i = 1 To 100
    If i Mod 2 = 0 Then
        Cells(rownum, colnum).Value = i
        colnum = colnum + 1
            If colnum > 6 Then
                rownum = rownum + 1
                colnum = 2
            End If
        progress = i / 100
        Application.StatusBar = "Generating Even Numbers... " & Format(progress, "0%") & " Completed"
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    End If
    Application.StatusBar = False
    Next i
End Sub

• Click Run to execute the code.

See the output in the article in which we described the same example about the DoEvents function in Excel VBA. You will see you can perform any action while executing the code.

The DoEvents function also lets you stop the code at any given time. Without the Excel VBA DoEvents function, you can’t do that until the code is finished running.

Drawbacks to Excel VBA DoEvents Function

The DoEvents function is helpful when you have long-running tasks, so the macro doesn’t freeze, and users can still interact with the workbook.

But using DoEvents has some problems:

• The order in which events are processed may not be what you expect, causing unexpected results.
• It can lead to repeated processing of the same event, making the code inefficient.
• Debugging becomes more complicated as the code execution sequence gets harder to track.
• It can slow down your macro, affecting its overall performance.

Frequently Asked Questions

1. How does Application.OnTime work as an alternative to DoEvents?

Ans: Application.OnTime is an alternative to DoEvents that allows you to schedule code execution after a specified delay. By scheduling short intervals of code execution and allowing other events to be processed during the delays, you can maintain responsiveness and prevent the application from becoming unresponsive.

2. Are there any best practices when using DoEvents alternatives?

Ans: Yes, it is essential to use DoEvents alternatives sparingly and only when necessary. Optimize your VBA code by using efficient algorithms, avoiding unnecessary loops, and minimizing resource-intensive operations to enhance overall performance.

3. Are there specific scenarios where DoEvents is still the best option?

Ans: DoEvents can still be useful in specific scenarios where you need a simple and quick way to allow user interactions during long-running processes. However, always consider the drawbacks and explore alternative approaches for better control and efficiency.

Key Takeaways from Article

• The DoEvents function gives control to the operating system.
• It allows the processing of pending events while the macro is running.
• Using DoEvents can lead to unpredictable event handling, debugging complexities, and a possible impact on code performance.
• An alternative approach of DoEvents improves code responsiveness and efficiency.
• Application.OnTime allows you to schedule code execution after a specified delay, keeping the Excel interface responsive while the macro runs in the background.

Conclusion

Throughout the article, we have tried to discuss an effective Excel VBA DoEvents alternative approach. The DoEvents comes with certain drawbacks, such as unpredictable event handling and potential performance impact. But the alternative approach ensures that your macros run smoothly. It also allows users to interact with the Excel interface while long-running tasks execute in the background.