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.
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 '
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