Display a progressbar on the statusbar

 2016-09-25    Application    0    140

If you want to show how your macro is progressing in a little more "graphic" way, you can use the procedure below.

Sub ShowProgress(strStatusText As String, dblPercentDone As Double, Optional lngLastTime As Long = 0, Optional blnDoEvents As Boolean = True)
' updated 2016-10-01 by OPE
Const clngBarSize As Long = 20
Dim lngProgress As Long, PROG_CHAR As String, BAR_CHAR As String
    If Abs(Timer - lngLastTime) < 1 Then Exit Sub ' less than 1 second since last update
    If dblPercentDone < 0 Or dblPercentDone > 1 Then Exit Sub
    
    If Val(Application.Version) < = 14 Then ' Excel 2010 or earlier
        BAR_CHAR = Chr(149) ' dots
        PROG_CHAR = Chr(135) ' hash
    Else ' Excel 2013 or later
        BAR_CHAR = ChrW(&H2610) ' empty square
        PROG_CHAR = ChrW(&H25A0) ' solid square
        'PROG_CHAR = ChrW(&H2612) ' crossed square

'        BAR_CHAR = ChrW(&H2B55) ' empty circle
'        PROG_CHAR = ChrW(&H2B24) ' filled circle
    End If
    
    lngProgress = CLng(dblPercentDone * clngBarSize)
    With Application
        .StatusBar = .Rept(PROG_CHAR, lngProgress) & .Rept(BAR_CHAR, clngBarSize - lngProgress) & " " & Format(dblPercentDone, "0 %") & "   " & strStatusText
    End With
    lngLastTime = Timer
    If blnDoEvents Then DoEvents
End Sub

Sub TestShowProgress()
Dim i As Long, t As Long
    t = 10
    For i = 1 To t
        ShowProgress "Your own status bar text", i / t
        Application.Wait Now + TimeValue("00:00:01") ' do something
    Next i
    Application.StatusBar = False ' reset the status bar
End Sub
In Excel 2013 or later something like this will be displayed on the StatusBar:
‡‡‡‡‡‡‡‡------------ 40 %   Your own status bar text
‡‡‡‡‡‡‡‡------------ 40 %   Your own status bar text

In Excel versions before 2013 something like this will be displayed on the StatusBar:
‡‡‡‡‡‡‡‡------------ 40 %   Your own status bar text


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.