Performance hit of Statusbar

The statusbar is a nice way of showing progress of a macro.
A thing little people seem to know is that there is quite a performance hit when one uses the statusbar.
Consider this macro:

Sub Test1()
    Dim dStart As Double
    Dim lCount As Long
    dStart = Timer
    For lCount = 1 To 100000
        Application.StatusBar = lCount
    Next
    MsgBox “This took ” & Format((Timer – dStart), “0.000”) & ” msec”
    Application.StatusBar = False
End Sub

When run on my system, it takes about 1.5 seconds.

If one changes the above code so the statusbar is only updated every 10th pass, like this:

Sub Test2()
    Dim dStart As Double
    Dim lCount As Long
    dStart = Timer
    For lCount = 1 To 100000
        If lCount Mod 10 = 0 Then
            Application.StatusBar = lCount
        End If
    Next
    MsgBox “This took ” & Format((Timer – dStart), “0.000”) & ” msec”
    Application.StatusBar = False
End Sub

there is an enormous improvement, the procedure now takes about 0.16 seconds!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Posted in Uncategorized


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.