DoEvents in VBA: how to make Excel VBA execute asynchronously

DoEvents is an Excel VBA command that temporarily pauses the execution of the macro to refresh the screen and execute any pending events in Excel. It can allow the user to interact with the Excel spreadsheet while the macro is running on the very same workbook! DoEvents can help a VBA program mimic asynchronous execution, which is per se not available in Excel VBA.

Every VBA programmer knows that Excel will freeze as long as a VBA macro is running. This is not such a big problem but it does decrease the productivity of the user (which will have to wait the end of the macro) and most of all, it deteriorates drastically the user experience.

Using Excel while a VBA macro is running is possible with DoEvents

The small procedure below should freeze your Excel for some seconds. You can run it and see that you effectively can’t use Excel at all.

Sub test()
     For i = 1 To 10000
        result = result + i
        Range("a1").Value = result
    Next
 End Sub

Now we slightly twist this piece of code with the DoEvents command. The code is below. Every time the loop runs through the code, the DoEvents command will be called. Run it and observe that you can click on the Excel spreadsheet, move through the ribbon, the sheets.

Sub test2()
     For i = 1 To 10000
        result = result + i
        Range("a1").Value = result
        DoEvents
     Next
 End Sub

What are the limits of the DoEvents VBA command?

This all seems great until you try to edit a cell and type some text for example: the macro stops.

Indeed the DoEvents command is just an illusion of asynchronous execution. You can only interact with the Excel spreadsheet in a very basic way. Don’t dream, you can’t execute another macro at the same time! You can’t refresh pivot tables, links… And anyway keep in mind that the macro could be poorly designed and use the active sheet or active workbook.

The piece of code above is indeed very badly conceived! Here is a nice experience, create another workbook in this Excel session and run the code again, thanks to the DoEvents instruction, you are able to go to other worksheets and can even activate and navigate the other workbook! Do you observe that the numbers are still added in the first cell of all worksheets you go to? Yes you got it: the illusion of freedom given by the DoEvents command can lead to major errors and problems!

The other drawback of DoEvents is also quite obviously the loss in computing efficiency. Every time the execution goes through a DoEvents instruction, it stops, waits, and then continues! And you can expect a bigger loss if you are using Excel at the same time, as all actions you want to perform are queued and executed when the macro calls DoEvents!

When to use DoEvents?

Using DoEvents to improve User Experience

Using DoEvents must remain very scarce and rare, as anything that modifies the basic rules of VBA and Excel. You may want to consider this only when you are running very light macros and want to keep them in the background as much as possible. It will improve the user experience drastically, as the user won’t think that the macro is lagging and slow. If your boss uses this macro, believe me he will be impressed. But the “Wow” effect only works if the macro using DoEvents is very light. If it is too big, the user will get stuck between each DoEvents instructions just like before.

Using DoEvents to display live results

In some macros, that are rather slow and would freeze your excel for a while in normal conditions, you may want to let the user know that the macro didn't just crash, and show the progress made. When the excel screen freezes, it can be hard to show a progress bar, a status bar update, or any other update of the screen. Just add a DoEvent instruction after these updates should have taken place to make sure the screen refreshes and the user happily sees the results live!

Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances
Close

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :
1
2
3
4
5

Up next

Articles similaires