DoEvents does not make events ... Why?

Question

I use DoEvents to force the progress indicator on the status bar (or in some cell in the worksheet), as in the code example below. But the screen does not refresh, or stops refreshing at some point. The task eventually completes, but the progress bar is useless.

Why does DoEvents not execute events? What else can I do to force a screen refresh?

Edit: I am using Excel 2003 on Windows XP.

This is an earlier question; thanks to Robert Marns for his answer and sample code below.

 Sub ProgressMeter() Dim booStatusBarState As Boolean Dim iMax As Integer Dim i As Integer iMax = 100 Application.ScreenUpdating = False ''//Turn off screen updating booStatusBarState = Application.DisplayStatusBar ''//Get the statusbar display setting Application.DisplayStatusBar = True ''//Make sure that the statusbar is visible For i = 1 To iMax ''// imax is usually 30 or so fractionDone = CDbl(i) / CDbl(iMax) Application.StatusBar = Format(fractionDone, "0%") & " done..." ''// or, alternatively: ''// statusRange.value = Format(fractionDone, "0%") & " done..." ''// Some code....... DoEvents ''//Yield Control Next i Application.DisplayStatusBar = booStatusBarState ''//Reset Status bar display setting Application.StatusBar = False ''//Return control of the Status bar to Excel Application.ScreenUpdating = True ''//Turn on screen updating End Sub 

Answer 1

I found DoEvents not always completely reliable. I would suggest trying two different things.

First, try placing a DoEvents call immediately after updating the status bar (that is, before your Some code .... ).

If this does not work, I found that in some cases using the Sleep API is a more reliable way to get CPU time. This is usually the first thing I try to do if DoEvents does not work as I would like. You need to add the following line at the top of your module (outside your function):

  Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 

Then add this line instead of or DoEvents :

  Sleep 1 'This will pause execution of your program for 1 ms 

Perhaps you will try to increase the program pause time using sleep if 1 ms does not work.

Answer 2

I found that calling DoEvents before updating the status bar, and not after, gives more predictable / desired results.

Code snippet above:

  fractionDone = CDbl(i) / CDbl(iMax) DoEvents Application.StatusBar = Format(fractionDone, "0%") & " done..."