Don't Forget to "DoEvents"

I'm writing a reporting package which uses Microsoft Access and Microsoft Excel to gather and chart data collected from a set of custom SharePoint lists.  I have some VBA code which does some calculations on all of the list items before the reports are generated to ensure that the data is the most up-to-date that it can be.

The VBA code lives in Access.  It basically does a lookup on each row in one of my lists and sets values in another list.  The business rules for this update had enough conditions on it that I couldn't implement it in one complex Update statement, thus the looping.

I also discovered that you can very easily add a progress bar to the Access status bar by calling "SysCmd acSysCmdInitMeter, 'message', maximum value" to initialize it, and "SysCmd acSysCmdUpdateMeter, currentValue" to increment the counter.  (To reset the status bar when I'm done, I call "SysCmd acSysCmdRemoveMeter".)  So I added this bit of functionality to the VBA code as a bonus for my users to give them some feedback.  After all, some of the lists I'm working with have a few thousand items in them (don't get me started about best practices stating that 2000 items is the workable maximum—I've already fought that battle and lost!) and it can take some time for the VBA code to complete.

Well wouldn't you know it, but the update code continually ran, and the thread blocked on the SQL command, so the progress bar just sat there, no progressing.  Furthermore, the whole UI appeared to be locked up while the update was running.  I couldn't even pause execution of the VBA code because the IDE wouldn't recognize my mouse clicks on the pause button.

And then I remembered something from my old VB6 days: the DoEvents method.

The DoEvents method relinquishes control of the processor by the VBA engine to allow other processes to do things. Or more accurately in my case, other threads. It was more important to call it when a background thread was doing heavy processing and you wanted to allow the UI thread a chance to handle the mouse clicks, keyboard presses, and other events which had queued up since the last time it got a time slice from the operating system.

I remembered one main warning about using it, though:

• You don't want to call it too often, since an expensive context switch most likely would take place

So I decided to see if it worked in VBA by adding this block of code:

If currentRow Mod 10 = 0 Then
    DoEvents
End If

In this block of code, currentRow is an integer which gets incremented each time through the loop. It is the variable that I pass to the method which updates the progress bar. You can see that I don't call DoEvents each time through the loop—I want the main processing code to actually finish in a reasonable amount of time, after all!

The IDE didn't complain about the method call, so it at least recognized it as valid VBA code.  So far, so good. But will it accomplish what I want it to?

The short answer: YES!

So now, in the latest version of the code, the DoEvents method is called every 10 rows.  This allows the UI to get refreshed, so I can now pause execution and scroll through the source code (albeit in bursts).

But more importantly, the progress bar now shows progress as the code runs!

DoEvents FTW!