How to Use Excel VBA DoEvents Function

Excel VBA DoEvents is a useful function that temporarily gives control back to the operating system. By allowing other processes to run while your macro is executing, it can prevent Excel from crashing or freezing and improve code efficiency. While the function has some drawbacks, it’s a handy tool to work with when dealing with large codes. In this article, we will learn about the Excel VBA DoEvents function in detail and give you an example of how you can utilize it.

Download Practice Workbook

Download this file to practice yourself.

Overview of Excel VBA DoEvents Function

The DoEvents function in Excel VBA allows the operating system to handle other events while your VBA code is running. It prevents the Excel user interface from freezing when you have lengthy operations or loops.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue.

In simpler terms, DoEvents lets your code work in the background and allows the computer to be available to do other things while it’s running. This prevents your program from appearing stuck or unresponsive.

You should use the DoEvents function when you have a long-running operation or loop in your code, primarily if it interacts with the user interface. By periodically using DoEvents, you ensure that other events, like button clicks, cell formatting, or form updates, can be processed in a timely manner. It helps maintain a smooth user experience by preventing the application from becoming unresponsive during these operations.

Excel VBA DoEvents Function: A Simple Example to Understand

In this article, we will see an example of the DoEvents function in the Excel VBA script. If you are new to Microsoft Visual Basic Application, follow this article on How to Write VBA Code in Excel.

We also learn how it impacts the performance of Excel and when to use it.

When you have a long-running VBA code, while the code is running, Excel gets frozen, and you have to wait until the code is finished running. This is very annoying to sit around when you can do other useful formatting in those moments.

This is where Excel VBA DoEvents comes in to circumvent the Excel application to become still and let you use your valuable time efficiently. You may use DoEvents not only to interact with other tools or applications but also to interrupt or stop the execution of code.
Here, we have created a code for you that is not very long but the use of Application.Wait. We will execute the code for a period of time. This will let us understand the benefits of VBA DoEvents function.
In this example, we will generate even numbers from 1 to 100 and use the following code.

• First, create a Module in the Visual Basic Editor.
• Then write/paste this code into the Module.

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

• Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

   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

Application.Wait Now + TimeValue("00:00:01")

• This line is unnecessary and in no way related to the Excel VBA DoEvents function. This is used solely for the purpose of delaying the execution of the code so that the DoEvents function can be understood.
• 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.

Excel VBA DoEvents Complications That You May Face

While the Excel VBA DoEvents function comes in handy when you want to multitask, it does come with some complications.

• If you change the worksheet/workbook while your code is running, an interesting thing happens. You see DoEvents function changes the output location in this case. So be careful not to change the worksheets/workbooks.
• The code stops running when you insert values or update cells and there is no notification to inform the user.
• Due to DoEvents function, the code runs much slower than usual.

When Should You Use Excel VBA DoEvents Function?

Despite these complications, the DoEvents function is very useful when you are working on long and complicated code. That’s why you do not want to use this function frequently. Here are a couple of instances where you can truly get the most out of Excel VBA DoEvents function.

• You want to use DoEvents when you need to test and debug your code as it runs.
• Another reason DoEvents should be included is to allow for user input. A macro, for example, might replace the labels on a UserForm with progress indications. Without DoEvents, Excel may not get the messages necessary to repaint the UserForm, giving the user the appearance that the macro has ceased running – especially if you switch to another program and then return to Excel. DoEvents, on the other hand, will keep repainting the UserForm and updating the macro’s progress.
• If you want to improve user experience, DoEvents function is the way to go. Especially if you don’t want to see a frozen and blank window on your pc.

Excel VBA DoEvents vs Wait Function

You have a code that demands a precarious amount of time and might also depend on another task. Meaning it depends on the completion of other tasks to proceed to the next line of codes. In such a case, we might need to pause the code or hold still for a certain amount of time. Application.Wait does exactly that.

As the name suggests, Wait will pause the execution of the code for a specified amount of time. We need to specify two things while using the Excel VBA Wait function. When the pause should start and for how much time it should wait.

The syntax of VBA Wait function:

Application.Wait (Time)

It’s a boolean function, and hence it returns True or False. It returns False till the stated time arrives. It returns True when the specified time comes.

We used the previous code for generating even numbers from 1 to 100. For code explanation see the previous section.

The main difference between Excel VBA DoEvents function and VBA Wait function is that DoEvents lets you complete other tasks like formatting, but the Wait function doesn’t. In the case of the Wait function, until the code finishes running, you cannot do anything in Excel.

Excel VBA DoEvents Performance Impact

As we have said, the Excel VBA DoEvents function does impact the performance of your PC, it considerably makes your code run slowly. We made a table to let you know just how much it slows things down. We wrote a code to print 1 to 10000 on a cell using VBA and edited the code for different circumstances. Sometimes we used the DoEvents function, and sometimes we didn’t. Sometimes we stopped screenupdating by assigning False and sometimes we allowed screenupdating with each execution by assigning True. Here is the result.

As you can see, with no DoEvents and screenupdating gives you the fastest running code.

Things to Remember

• For short and brief code, you want to leave Excel VBA DoEvents function out of your code.
• If your code is already tested and you want to execute your code with a limited amount of time, perhaps don’t use DoEvents at all.
• Without DoEvents don’t attempt to change anything on your Excel worksheet while your code is running. There is a great possibility that the system will get frozen and crush the file.
• DoEvents should not be used if other programs may interact with your method in unexpected ways during the period you have given up control.

Frequently Asked Questions

• Are there any alternatives to the DoEvents method for handling long-running processes in Excel VBA?

Ans: Yes, there are alternatives to the DoEvents method in Excel VBA for handling long-running processes. One alternative is to use a separate thread to perform the long-running process, while the main thread handles user interface updates. Another alternative is to break up the process into smaller, more manageable chunks, and use timers or events to trigger each chunk.

• Is there a limit to the number of times the DoEvents method can be called in Excel VBA?

Ans: There is no specific limit to the number of times the DoEvents method can be called in Excel VBA. However, excessive use of DoEvents can lead to performance issues and slow down the execution of the code, so it should be used judiciously and only when necessary.

Conclusion

In conclusion, while we do not recommend you use the Excel VBA DoEvents function on a frequent basis, you can use it in times of need and get the most out of it. Remember, the DoEvents function is a simple command that you can add to your code to allow the operating system to process other events while your macro is running. In this way, the DoEvents function can help prevent Excel from crashing or freezing, and it can also make your code run more efficiently. We hope, with this article, any confusion you have with DoEvents has been neutralized.