[vba] Force a screen update in Excel VBA

My Excel tool performs a long task, and I'm trying to be kind to the user by providing a progress report in the status bar, or in some cell in the sheet, as shown below. But the screen doesn't refresh, or stops refreshing at some point (e.g. 33%). The task eventually completes but the progress bar is useless.

What can I do to force a screen update?

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.......

Next i

I'm using Excel 2003.

This is not directly answering your question at all, but simply providing an alternative. I've found in the many long Excel calculations most of the time waiting is having Excel update values on the screen. If this is the case, you could insert the following code at the front of your sub:

Application.ScreenUpdating = False
Application.EnableEvents = False

and put this as the end

Application.ScreenUpdating = True
Application.EnableEvents = True

I've found that this often speeds up whatever code I'm working with so much that having to alert the user to the progress is unnecessary. It's just an idea for you to try, and its effectiveness is pretty dependent on your sheet and calculations.

Specifically, if you are dealing with a UserForm, then you might try the Repaint method. You might encounter an issue with DoEvents if you are using event triggers in your form. For instance, any keys pressed while a function is running will be sent by DoEvents The keyboard input will be processed before the screen is updated, so if you are changing cells on a spreadsheet by holding down one of the arrow keys on the keyboard, then the cell change event will keep firing before the main function finishes.

A UserForm will not be refreshed in some cases, because DoEvents will fire the events; however, Repaint will update the UserForm and the user will see the changes on the screen even when another event immediately follows the previous event.

In the UserForm code it is as simple as:

Me.Repaint

I couldn't gain yet the survey of an inherited extensive code. And exact this problem bugged me for months. Many approches with DoEnvents were not helpful. Above answer helped. Placeing this Sub in meaningful positions in the code worked even in combination with progress bar

Sub ForceScreenUpdate()
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Wait Now + #12:00:01 AM#
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Sub

@Hubisans comment worked best for me.

ActiveWindow.SmallScroll down:=1
ActiveWindow.SmallScroll up:=1

Text boxes in worksheets are sometimes not updated when their text or formatting is changed, and even the DoEvent command does not help.

As there is no command in Excel to refresh a worksheet in the way a user form can be refreshed, it is necessary to use a trick to force Excel to update the screen.

The following commands seem to do the trick:

- ActiveSheet.Calculate    
- ActiveWindow.SmallScroll    
- Application.WindowState = Application.WindowState

Put a call to DoEvents in the loop.

This will affect performance, so you might want to only call it on each, say, 10th iteration.
However, if you only have 30, that's hardly an issue.

This worked for me:

ActiveWindow.SmallScroll down:=0

or more simply:

ActiveWindow.SmallScroll 0