Loops as performance killers and DoEvents()

Loops are a performance killer. They shouldn't be used when they don't have to. On the plus side, the run time of a standard loop is O(n) which is linear. Nevertheless, know your language and the functions available. It's a mistake to loop through a data structure when it has a built in function call to do what you need more efficiently. I learned this the hard way as you'll see.

I was clearing a list box control in an Access form by removing each item from the list:

'clear the listbox
For i = lstAcct.ListCount - 1 To 0 Step -1
lstAcct.RemoveItem i
Next i

Unfortunately, I forgot that Access loves bound controls. As such, you can set the list box's contents to be equal to a data source. So that means you can set it to an EMPTY data source and it will clear the control. Obviously it'd be better if VBA just provided a function to clear list boxes and combo boxes. The above code was replaced with the following:

'clear the listbox
lstAcct.RowSource = ""

The performance hit I was taking from the loop was very noticeable to the user. A number of events on the form triggered the function that used it. Just like with people hitting elevator buttons over and over, users will without fail click on their screens while a control is populating. In Access, this can lead to the application crashing. In a dedicated client like C#, you have more control over this.

Granted, you can stick the following function in the middle of all your loops and never have the active window freeze up on users while code's running. You don't, however; want to have this function call in all your loops. Use it sparingly, only when you know something will lock up the window. It is in fact much easier to use this than to have to multi-thread, which is a whole 'nother beast. One example where I called DoEvents() inside a loop was for a status display form in C#. A progress bar was displaying the status of a loop looping through a massive text file. Obviously, if the form lost focus then it'd be painted over and you wouldn't be able to get it back until the loop was done. Because of the size of the files this could be a while. In the meantime you have no idea how much longer it'll take. By putting the function call in the middle of the loop the user can switch back and forth with other apps and the form will repaint itself when focus is returned to it.