DoEvents doesn't do the events... Why?

Question

I'm using DoEvents to force an update of a progress indicator in the status bar (or in some cell in the sheet) as in the example code below. But the screen doesn't refresh, or stops refreshing at some point. The task eventually completes but the progress bar is useless.

Why won't DoEvents "do the events"? What else can I do to force a screen update?

Edit: I'm using Excel 2003 on Windows XP.

This is a follow up to an earlier question; thanks to Robert Mearns for his answer and the 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've found DoEvents is not always completely reliable. I would suggest trying two different things.

First, try placing the DoEvents call immediately after the Status Bar update (ie, before your Some code .... line).

If that does not work, I've found in some cases that using the Sleep API is a more reliable way to yield processor time. It's usually the first thing I try if DoEvents is not working as I'd like. You'll need to add the following line at the top of your module (outside of your function):

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

Then add this line in place of, or in addition to, DoEvents:

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

You might try increasing the length of time you pause the program using sleep if 1 ms doesn't work.

Answer 2

I've found that calling DoEvents before updating the status bar, rather than after, yields more predictable/desirable results.

The code snippet from above would be:

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