VBA DoEvents

This tutorial will discuss how to use the DoEvents command in VBA.

The VBA DoEvents function temporarily pauses a running macro, giving Excel a chance to process key presses, mouse clicks, and other operating system messages.

In long-running macros, Excel can appear to hang and become unresponsive, and the macro may be impossible to interrupt. If DoEvents is included in your code, users can be assured that the macro is still running, and can still interrupt execution if necessary.

 

VBA DoEvents Example

Consider the following code:

Public Sub Test()
    Dim i As Long

    For i = 1 To 20000
        Range(“A1”).Value = i
    Next i

End Sub

When you try this code, you’ll note that the Excel window can’t be interacted with. However, because it’s not very demanding on the processor, the macro can still be interrupted by pressing ESC or CTRL+BREAK.

If a lot of complex and demanding functions were being performed inside this loop, Excel would take longer to register a key press event – up to several minutes or possibly not at all, especially if the computer is running other programs at the same time.

Now add a single line with DoEvents below the Range assignment:

        Range("A1").Value = i
        DoEvents

If you run this code again, you’ll see that Excel is now responsive – it can be focused and brought to the foreground.  DoEvents is called every time through the loop, which ensures that the macro is always yielding execution so that Excel can process any messages sent to it.  Because this macro runs quickly, it almost seems as though it’s running in the background (though it isn’t) – more operations inside the loop would quickly expose that illusion.

 

DoEvents Dangers

Take a second look, though. More than simply allowing Excel to be focused, you can actually click around inside cells and even switch tabs while the macro is running (try it – you’ll see some funny behavior).  This shows one of the dangers of DoEvents – it can be the cause unintended consequences if your macro isn’t coded carefully.

Another danger is that DoEvents can serve as a window for other macros to run inside of.  Try this: place an ActiveX CommandButton on the worksheet, double-click it, and add the following code inside the CommandButton1_Click() event:

    Dim c As Range

    For Each c In Range("B3:E8")

        c.Value = c.Value + 1

    Next c

 

Toggle off Design Mode in Excel, then run the Test() macro you added earlier.  While the Test macro is running, you can click the CommandButton on the worksheet, and its associated macro will run as soon as DoEvents gives the Test() macro a break.

The danger here is if the CommandButton macro altered the data the Test() macro was working on, or triggered the Test() macro again.  You can end up with some extremely messy results if you’re not careful.

Finally, you should be aware that DoEvents will cause your macro to suffer a performance impact when called.  Inside a loop, this impact will add up quickly unless you limit how often DoEvents is called.  Referring to our Test() example, try this:

        If i Mod 1000 = 0 Then DoEvents

This visibly reduces Excel’s responsiveness, but the performance impact will be lessened.

 

When to use DoEvents

Despite these dangers, DoEvents is a handy function that aids in testing and debugging.  Consider adding DoEvents to long-running loops.

Another reason to include DoEvents is to allow for user feedback. For example, a macro might update a UserForm’s labels with progress indicators. Without DoEvents, Excel may not receive the messages to repaint the UserForm, giving a user the impression that the macro has stopped working – especially if you switch to another program and then try to switch back to Excel. With DoEvents however, the UserForm will continue to be repainted, and updates to the macro’s progress will continue to appear.

For the most part, DoEvents isn’t something you’ll want to include a lot in your code, and can often be omitted.  If responsiveness is something your macro needs though, don’t count it out!