Use Statusbar as Progressbar in Excel VBA

There is not an easy way to use existing progress bar neither there is any class to use to show the progress in the Excel application.

Using the Progress class below, we can use Excel’s status bar as a progress bar filling with square blocks to give a sense of the progress graphically as well as in percent.

Progress Class

Option Explicit

Private statusBarState As Boolean
Private enableEventsState As Boolean
Private screenUpdatingState As Boolean
Private Const NUM_BARS As Long = 50
Private Const MAX_LENGTH As Long = 255
Private BAR_CHAR As String
Private SPACE_CHAR As String

Private Sub Class_Initialize()
    ' Save the state of the variables to change
    statusBarState = Application.DisplayStatusBar
    enableEventsState = Application.EnableEvents
    screenUpdatingState = Application.ScreenUpdating
    ' set the progress bar chars (should be equal size)
    BAR_CHAR = ChrW(9608)
    SPACE_CHAR = ChrW(9620)
    ' Set the desired state
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.EnableEvents = False
End Sub

Private Sub Class_Terminate()
    ' Restore settings
    Application.DisplayStatusBar = statusBarState
    Application.ScreenUpdating = screenUpdatingState
    Application.EnableEvents = enableEventsState
    Application.StatusBar = False
End Sub

Public Sub Update(ByVal value As Long, _
                  Optional ByVal MaxValue As Long = 0, _
                  Optional ByVal Status As String = "", _
                  Optional ByVal DisplayPercent As Boolean = True)

    ' Value          : 0 to 100 (if no max is set)
    ' Value          : >=0 (if max is set)
    ' MaxValue       : >= 0
    ' Status         : optional message to display for user
    ' DisplayPercent : Display the percent complete after the status bar

    '   

    ' Validate entries
    If value < 0 Or MaxValue < 0 Or (value > 100 And MaxValue = 0) Then Exit Sub

    ' If the maximum is set then adjust value to be in the range 0 to 100
    If MaxValue > 0 Then value = WorksheetFunction.RoundUp((value * 100) / MaxValue, 0)

    ' Message to set the status bar to
    Dim display As String
    display = "  "

    ' Set bars
    display = display & String(Int(value / (100 / NUM_BARS)), BAR_CHAR)
    ' set spaces
    display = display & String(NUM_BARS - Int(value / (100 / NUM_BARS)), SPACE_CHAR)

    ' Closing character to show end of the bar
    display = display & BAR_CHAR

    If DisplayPercent = True Then display = display & "  (" & value & "%)  "

    display = display & Status
    
    ' chop off to the maximum length if necessary
    If Len(display) > MAX_LENGTH Then display = Right(display, MAX_LENGTH)

    Application.StatusBar = display
    Log display
End Sub

Public Sub Completed()
    Class_Terminate
End Sub

How to use Progress class

Dim progress As New ProgressBar
progressMax = 10
' After certain works is done...
progress.Update 1, progressMax, "Step one is completed"

' After another long work...
progress.Update 2, progressMax, "Step two is completed"

' When reporting progress is completed
progress.Complete