VBA DoEvents and when to use it

Introduction | Example | Peformance | Applications |

Introduction to VBA DoEvents

VBA DoEvents yields execution of your macro, so your computer processor will be able to simultaneously run other tasks and recognize other events. The VBA DoEvents function also enables interruption of code execution so it’s easier to stop a running macro.

Only few things in life are more frustrating than trying the halt the execution of a program and not being able to because Excel has become unresponsive. I’m sure you’ve been there. We’ve all made a mistake in a code which causes an infinite loop or something Your program will, in theory, never terminate and Excel will remain unresponsive until you shut down your Excel application! So frustrating.

In the first part of this tutorial, we’ll show you how to circumvent this and other similar problems by using the VBA DoEvents function. However, as the saying goes, all good things come with a price. The DoEvents function in VBA is no exception since it will cause the execution time of your program to increase.

This is logical, since, by yielding execution to other events and processes, Excel temporarily halts the code execution while the operating system handles these other events or processes.

The second part of this introduction is dedicated to exploring how the DoEvents function affects performance. We’ll present some performance test results with important lessons attached to them and then we’ll show you how to balance the yielding of code execution with performance considerations.

Finally, we’ll discuss the generic applications of the DoEvents function in Excel and when not to use it in your VBA macros.

With this in mind, let’s present a very basic and nifty example of VBA DoEvents usage!

VBA DoEvents Example

Insert the code below into a standard code module:

Sub ShowStatusWithDoEvents()
    '(1) Stores the current value of the Application.Statusbar
    '(2) Updates the value of the Application.Statusbar through a loop
    '(3) Enables code interruption with the DoEvents function in the loop
    '(4) Restores the value of the Application.Statusbar

    Dim i As Long
    Dim appStatus As Variant
    
    With Application
        .ScreenUpdating = False
        If .StatusBar = False Then appStatus = False Else appStatus = .StatusBar
    End With
    
    For i = 1 To 5000
        Application.StatusBar = "Processing row " & i
        DoEvents
    Next i
    
    With Application
        .ScreenUpdating = True
        .StatusBar = appStatus
    End With
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

In this simple procedure we use VBA to repeatedly update the value of the Application.Statusbar in the lower left corner of the Excel window inside a loop. In practical situations, if a program containing a loop takes a long time to complete it’s really helpful to track its progress that way.

VBA Excel Application.StatusBar

The procedure starts by storing the current value of the .StatusBar in a Variant type variable. This is because you update the .StatusBar with a string, but its default value is the boolean value False. This may sound strange, but otherwise it would be impossible update the the .StatusBar with the string “False” without setting it to its default value, “Ready”!

We also turn the screen updating off by setting .ScreenUpdating to False, just like we did in our Application.ScreenUpdating VBA tutorial. As will become evident in the next section, this is crucial in terms of performance!

Next, the procedure loops from 1 to 5000 and updates the .StatusBar at every iteration. The DoEvents function in the loop enables you to interrupt the code execution. To do this, simply press Esc or Ctrl + Break. Note, however, that not all computer keyboards come with a Break button!

We’ve actually written a good tutorial explaining how to stop an Excel macro. By including VBA DoEvents, it’s easier for the system to not got bogged down so it should be better able to recognize when you’re trying to press the Esc or Ctrl + Break to end your running macro.

Interrupting code execution with VBA DoEvents

When the loop completes, the .StatusBar is set to its original value stored in the AppStatus variable and we also set the .ScreenUpdating property back to True.

Okay, we’ve shown you how to include the DoEvents VBA function in your macro and how it can make it easier for you to cancel your macro. Next, let’s examine the price you pay for this increased user friendliness and control stemming from the use of the DoEvents function.

VBA DoEvents Performance Impact

To test the influence of the DoEvents function on macro performance, we modified the ShowStatusWithDoEvents procedure to loop from 1 to 10 and we wrote a small timing procedure which calls it 10,000 times. Next, we timed the execution of the four different variants of ShowStatusWithDoEvents procedure shown in table 1 below.

Table 1. DoEvents test results ranked by average execution time.

It should come as no surprise that the fastest execution time (25.34s) is obtained by removing DoEvents and setting .ScreenUpdating to False in the ShowStatusWithDoEvents procedure.

The second row in the table shows that calling the DoEvents function at every iteration increases the execution time by a factor of 3.6 (91.16s / 25.34s). In other words, the performance overhead incurred by using DoEvents that way is quite substantial!

Even more conspicuous is the steep increase in execution time we observe from row 2 to row 3 (1,663.66s). This is caused by not setting the .ScreenUpdating property to False in the procedure. This result suggests that forgetting to do so may increase the execution time of your program by a factor of more than 18! The main takeaway from this is that, unless you have specific reasons not to do so, you should always set the .ScreenUpdating to False while executing your programs.

The bottom feeder in our tests is row 4 (2,658.62s), where the .ScreenUpdating property was set to True and DoEvents was removed. It is quite surprising that including DoEvents at every iteration in the loop, cf. row 3, decreases the execution time of the procedure compared to when DoEvents is removed from the procedure!

The Microsoft Developer Network documentation on the Application.ScreenUpdating property states: “Turn screen updating off to speed up your macro code. You won’t be able to see what the macro is doing, but it will run faster. “ However, it’s not entirely accurate to say that you can’t always see what the macro is doing while it’s being executed when the screen updating is turned off. When you update the status bar, you can in fact see what your macro is doing, even if you haven’t included the DoEvents function in your code. That is, unless your screen freezes up. This suggests that .ScreenUpdating only updates the worksheet area (i.e. the cells), rather than the entire Excel window, including the status bar. This may in turn explain the surprising result in row 4 of the table. If the repeated requests from the loop to update the screen and the status bar are conflicting with one another, this might indeed cause the procedure to run slower without the DoEvents function!

Of course this is all just a theory. I’d love to hear if you all get similar results in your tests!

The obvious solution to decreased performance caused by the DoEvents function is to only call it intermittently in the loop. This can easily be achieved by using the VBA Mod operator like this:

If i Mod 10 = 0 Then DoEvents

The line of code above tells Excel only to call DoEvents at every tenth iteration. This decreases the total execution time of the macro tested in row 2 of the table from 91.16s to only 29.67s and you can still interrupt the code execution. A no-brainer!

Finally, let’s discuss the generic applications of the VBA DoEvents function and, just as importantly, when not to use it!

Application ideas using VBA DoEvents

Broadly speaking, there are two main reasons for using the DoEvents function:

• Debugging purposes.
  • Let say you’re developing a procedure which loops through thousands of rows while performing expensive operations. Unless you include DoEvents in your loop, it means you simply must wait until all the operations specified in your code have been carried out, even if your code isn’t working as expected. This is particularly annoying if you mistakenly wrote an infinite loop because you forgot to specify a termination clause.
• Improved user experience.
  • The users of your Excel VBA programs may find it annoying to watch a blank screen while your procedure is executing. For psychological reasons, you may want use something like a status bar or VBA progress bar to track the progress of your procedure while it’s running. Visual tools like these are nice distractions for an end user.

The DoEvents function should not always be used in your procedures, though. For most “lightweight” procedures with short execution time, this function is entirely unnecessary. Moreover, if performance is key and your code has been thoroughly tested, you may want to leave DoEvents out of your code, even if the execution time is substantial. In that case, the benefits described above may be outweighed by the price of using it.

The choice is yours!

Comments

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!

Share this article on Twitter and Facebook, then leave a comment below and let’s have a discussion.