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 spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
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
We also turn the screen updating off by setting
Next, the procedure loops from 1 to 5000 and updates the
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
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
VBA DoEvents Performance Impact
To test the influence of 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.
|Rank||Iterations||Total execution time(seconds)||Average execution time(seconds)|
|Without DoEvents,without screen updating||1||10,000||25.34||0.002534|
|With DoEvents,without screen updating||2||10,000||91.16||0.009116|
|With DoEvents,with screen updating||3||10,000||1,663.66||0.166366|
|Without DoEvents,with screen updating||4||10,000||2,658.63||0.265864|
It should come as no surprise that the fastest execution time (25.34s) is obtained by removing
The second row in the table shows that calling the
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
The bottom feeder in our tests is row 4 (2,658.62s), where the
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
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
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
- 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!
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!