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
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
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
Posting code? Use <pre> tags for VBA and <code> tags for inline.