Daily Dose of Excel

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