When developing Excel projects, sometimes it becomes necessary to provide some automation for your user, such as clicking a button to import some external data, update a complicated static output, or solve a circularity. In most cases this can be achieved by writing some VBA to run the process for your user and hopefully this will be a quick and simple process.

However, despite the developers best efforts, the VBA code can run slowly (especially if the end user’s computer is not the most powerful or up-to-date) leaving your user staring at a spinning cursor wheel with no idea of what is happening, how much longer the code will take to finish, or if the code is even still running correctly. In cases where a process is expected to take more than 1-2 seconds to complete, some form of progress bar or progress message can help the user to understand what is happening and how far along the process has got to.

Potential options to alert the users to what is happening as the VBA code executes include:

• Displaying a pop-up window (via a UserForm) displaying a message to the user; the message can be updated as the code progresses to keep the user informed.
• Displaying a pop-up window (via a UserForm) displaying an animated progress bar to visual for the user how far along the total VBA code execution the process has got; this can include a text message as well.

The main disadvantage of this approach is that it requires additional processing power to load and display the pop-up window (UserForm), and each time your code wants to update the progress bar or the displayed message, the UserForm needs to re-paint the window, which will interrupt the VBA code running and take up some processing power and execution time. I have also noticed that using this approach can lead to a lot of screen flickering, particularly when the code does run faster or frequent updates to the displayed pop-up window are processed. Although this screen flickering will successfully indicate to the user that something is happening and the code is progressing, it can be quite distracting and off-putting to watch.

The Idea

You may have noticed that often Excel makes use of the Excel Status Bar (at the bottom of the Excel window) to display messages and even progress bars, such as when saving a workbook. The Status Bar can be easily updated without the process executing in VBA itself (as an update to the displayed Status Bar text triggers processes within the Excel application itself) and as it is not a separate window there is little to no screen flicker with fast/frequent updates.

I had previously seen a 3rd party Excel add-in which made use of the Excel Status Bar to display a simple text-based progress bar and a percentage value to represent how far along a VBA process the code had got to. This add-in used a series of “-” characters to show the length of the unfilled progress bar and replaced the “-” characters with a “|” or “/” character as the progress bar was filled. This achieved what I wanted to replicate, but as you cannot control the font used in the Status Bar (e.g. setting a mono-space font), when the displayed progress bar text changes from “-” characters to “|” characters the length of the displayed progress bar changes, which is quite distracting and doesn’t look as smooth as I wanted.

After a lot of Googling, I found a suggestion to look at the extended Unicode character set which can be used to display a series of set characters beyond the standard Latin characters in most default fonts. The Unicode character set includes box drawing characters, which were used in the first text-based computer games in MS-DOS, as well block elements, geometric shapes and other widely used symbols.

Within the geometric shapes set I found the filled “black square” (Unicode U+25A0) and empty “white square” (Unicode U+25A1). Unfortunately, when displayed in the Excel Status Bar, the white square character is smaller than the filled square meaning that as the progress bar fills and the filled squares replace the empty squares, the progress bar changes length, which was what I was trying to avoid. Fortunately, after some trial an error, I found that the “empty ballot box” (Unicode U+2610) in the miscellaneous symbols section, displays in the Status Bar as the same size as the filled “black square” character.

The idea was, to create a VBA module that would produce a nice looking text-based progress bar displayed in the Excel Status Bar, complete with percentage completion and optional message string. I wanted to make this a self-contained module which could simply be dropped in to any VBA project and easily used and updated with a couple of sub-routine calls and minimal impact on the execution of the main code which was calling it.

VBA Status Bar Progress Bar

Below is the main code for my version of an Excel Status Bar progress bar. It uses three sub-routines and four module-level constants to control its functionality.

0. Module-Level Constants & Variables

' Constants
Private Const ProgBar_Len As Integer = 20   ' Sets the total size of the progress bar in "blocks"
Private Const ProgBar_Max As Integer = 255  ' The max Excel StatusBar string in characters
Private Const Char_Empty As Long = 9744     ' Unicode character Hex: &H2610 => CLng("&H2610") = 9744
Private Const Char_Full As Long = 9632      ' Unicode character Hex: &H25A0 => CLng("&H25A0") = 9632

' Variables used for the Progress Bar
Dim ProgBar_Empty As String * 1, ProgBar_Full As String * 1
Dim ProgBar_Set As Long
Dim blUserStBar As Boolean

The constants (declared with the key word Const) are Private as they are only intended to be used within the RXL_ProgressBar module. They are used to avoid having hard-coded values within the main code, and so that if one of the values needs to be updated, it can easily be done by updating the single constant value rather than having to find all occurrences of that hard-coded value in the code.

The ProgBar_Len value sets how many text squares make up the length of the progress bar the code creates. By default, it is set to 20 blocks so that each filled block/square represents 5%. completion. This can be updated if you wish.

The ProgBar_Max value represents the maximum number of characters that the Excel StatusBar can hold and should not be changed.

The Char_Empty and Char_Full values are the Long value representation of the Unicode characters for the progress bar characters. You can change these if you wish, but the value needs to be a whole number value (as a Long data type). As the Unicode character set uses hexadecimal values (represented as “U+” then the hex value), you can use the CLng() function in VBA to convert the hexadecimal portion of the U+ value into the corresponding numeric value.

1. InitialiseProgressBar()

Public Sub InitialiseProgressBar(Optional ByRef pStrMessage As String = "")
    
    ' Initialise the persistent global variables and set as required
    '  - where the VBA session has initialised or reset the global variables will be empty
    '    and re-defined, the variables will have been re-defined and initialised at 0 (zero)
    '  - by placing the string definitions in the global variables and only setting once at
    '    initialisation reduces the number nof ChrW() function calls
    ProgBar_Empty = ChrW$(Char_Empty)
    ProgBar_Full = ChrW$(Char_Full)
    
    ' Check the length of the max set portion of the progress bar string
    '  - uses the number of blocks to display plus the length of the max size of extra string
    '    which is made up of the progress % in brackets plus space to add the optional message
    ProgBar_Set = ProgBar_Len + Len(" (100%):  ")
    
    ' Capture the currnet user status for displaying the StatusBar
    blUserStBar = Application.DisplayStatusBar
    
    ' Ensure that the StatusBar is being displayed
    If Application.DisplayStatusBar = False Then _
        Application.DisplayStatusBar = True
    
    ' Set the initial output message and empty progress bar
    Call UpdateProgressBar(0, pStrMessage)
    
End Sub

The InitialiseProgressBar() sub routine should be called when before the progress bar is used for the first time. The sub routine sets the persistent progress bar characters (ProgBar_Empty and ProgBar_Full) as well calculating the total potential length of the progress bar with the percentage value text displayed (ProgBar_Set).

The ProgBar_Empty and ProgBar_Full variables are declared as single character strings to optimise their memory allocation, and a single ChrW() function call is made to set each character for these global variables. As the ChrW() and Chr() function calls can be surprisingly expensive function calls, this process reduces the number of calls to just two at initialisation rather than at least two ChrW() function calls every time the progress bar is updated.

The initialisation function captures the current user chosen Application.DisplayStatusBar state (determines whether Excel displays the Status Bar) and then ensures that it is displayed. Before, finally displaying the initial empty progress bar with 0% completion and with the optional message if provided via the pStrMessage argument.

2. UpdateProgressBar()

Public Sub UpdateProgressBar(ByVal pDblProgress As Double, _
                             Optional ByRef pStrMessage As String = "")
    
    ' Define local variables
    Dim strOut As String
    Dim lnBlocks As Long, lnPercent As Long
    
    ' Restrict the progress value in case progress value passed is outside of the expected range
    If pDblProgress > 1 Then _
        pDblProgress = 1
    If pDblProgress < 0 Then _
        pDblProgress = 0
    ' Convert the passed progress value (% as Double/decimal) to a whole number
    lnPercent = CLng(pDblProgress * 100)
        
    ' Calculate the number of filled progress bar blocks for the passed progress value (%)
    lnBlocks = CLng(pDblProgress * ProgBar_Len)
    ' Restrict the number of bar blocks in case it is calculated as greater than the set total 
    ' progress bar length
    If lnBlocks > ProgBar_Len Then _
        lnBlocks = ProgBar_Len
    
    ' Note - max length for a StatusBar message is 255 characters
    '      - the progress bar aspect of the message will be between 28 and 30 characters
    '        [=> 20 boxes + "_(" + 1-3 digits for percentage complete + "%):__"]
    '      - need to check the length of the passed message string [:= pStrMessage] and restrict
    '         -> i.e. If greater than (255 - 30) then restrict to allowable length - 3, and add 
    '                 "..." at the end to indicate a truncated message string
    If Len(pStrMessage) > (ProgBar_Max - ProgBar_Set) Then
        pStrMessage = Left$(pStrMessage, ProgBar_Max - ProgBar_Set - 3) & "..."
    End If
    
    ' Construct the output string
    strOut = String(lnBlocks, ProgBar_Full) & _
             String(ProgBar_Len - lnBlocks, ProgBar_Empty) & _
             " (" & lnPercent & "%)"
    If pStrMessage <> vbNullString Then _
        strOut = strOut & ":  " & pStrMessage
        
    ' Display the constructed output string in the Excel StatusBar
    Application.StatusBar = strOut
    
End Sub

The UpdateProgressBar() sub routine is the main code in this module, which re-draws the progress bar each time it needs to be updated.

The sub routine expects the pDblProgress argument to be passed as a decimal number between 0.00 and 1.00, which is equivalent to a percentage progress between 0% and 100%.

• if a value is passed which is less than 0, then the sub routine will restrict the value to 0.00;
• if a value is passed which is greater than 1, then the sub routine will restrict the value to 1.00.

Although the value passed as the pDblProgress argument can have any number of decimal places, when calculating the number of filled progress bar characters to display and the conversion to a percentage figure to display, the value will be rounded down to the nearest whole number. This rounding in both cases is achieved by converting the calculated value (as a decimal value) to a Long data type, which is a non-decimal value. This conversion removes any decimal element to the number and just keeps the whole number portion of the value thereby effectively rounding down to the nearest whole number in an efficient manner.

Both the pDblProgress argument and the optional pStrMessage argument can be calculated in the sub routine call. For example:

• if a VBA process is being run which is made up of a fixed number of definable steps (totalSteps),
• and the variable currStep represents the process step which is being executed
• the UpdateProgressBar() sub routine could be run with the following code each time a new step starts execution

UpdateProgressBar pDblProgress:=currStep/totalSteps, _
                  pStrMessage:="Running step " & currStep & " of " & totalSteps

The pStrMessage parameter is optional and can have a text string of any length passed, which is then displayed to the right of the progress bar to inform the user of what the associated VBA code is doing at that time. If it is omitted, then the just the progress bar characters and the percentage progress are displayed.

The Excel Status Bar has a maximum capacity of 255 characters (as reflected in the ProgBar_Max constant) and will cut off any characters beyond this total. To help deal with this, the code above will calculate the expected total constructed string length (the progress bar characters, % progress characters, and the optional message), and if this will be greater than 255 characters, the optional message string is restricted to the allowable length and “…” characters are added to the end to indicate that the string has been truncated.

3. CloseProgressBar()

Public Sub CloseProgressBar()
    
    ' Reset the StatusBar display to the setting used before initialising and displaying the 
    ' progress bar
    ' - checks if one of the calculated global variables is 0 (zero) which would indicate that 
    '   the global variables have cleared and reset and so the blUserStBar variable is likely a 
    '   false negative
    ' - where the calculated global variables are reset, default the DisplayStatusBar setting to True
    Application.DisplayStatusBar = IIf(ProgBar_Set = 0, True, blUserStBar)
    
    ' Release the StatusBar back to the application otherwise the final progress bar message 
    ' will persist
    Application.StatusBar = False

End Sub

The CloseProgressBar() sub routine attempts to reset the Application.DisplayStatusBar property to state that it was before the progress bar was initialised.

The sub routine also makes an update call to the Application.StatusBar property, but instead of providing a string to update the Status Bar with, it sends a False value. This releases the Status Bar back to Excel so that further updates of the text displayed on the Status Bar comes from the Excel application itself. If this final False value is not sent, then your final progress bar and message will persist on the Status Bar beyond the end of your VBA code execution finishing.

Example

An example of the intended use of the three sub routines in the RXL_ProgressBar module is shown below:

Private Sub Executed_VBA_Code()
    
    ' Start of the main process code
    ...
    
    ' Initialise the StatusBar progress bar with a start message
    InitialiseProgressBar pStrMessage:="Running [process] ..."
    
    ' More main process code
    ...
    
    ' Update the progress bar as each step of the main process is executed
    UpdateProgressBar pDblProgress:=(currStep - 1)/totalSteps, _
                      pStrMessage:="Running [process] - step " & currStep & " of " & totalSteps
    ...
    
    ' Update the progress bar for a final message
    UpdateProgressBar pDblProgress:=1, _
                      pStrMessage:="[process] finished!"
    ' Provide a message box for the user to indicate the main process has finished
    []
    ' Close the progress bar before ending the main process sub routine
    CloseProgressBar
    ' Complete any tidy-up code for the end of the main process code
    ...
    
End Sub

If you would like to have a closer look at the Status Bar Progress Bar code and have a play around with a demo, there is an Excel file (RXL_ProgressBar Demo.xlsm) which can be downloaded from the RXL_VBATools GitHub repository within the ProgressBar sub-folder:

https://github.com/r-silk/RXL_VBATools

To include the RXL_ProgressBar module in your own Excel project, you simply need to import the module into your VBA project and then make the three sub routine calls as discussed above.

The module can be imported by using one of the two methods below:

1. Copy from demo file
   • If you have downloaded the RXL_ProgressBar Demo.xlsm file (https://github.com/r-silk/RXL_VBATools), you can copy the module and import it directly.
   • Open the RXL_ProgressBar Demo.xlsm file, as well as the Excel file you wish to import it into, and open the VB Editor.
   • Find the RXL_ProgressBar module in the VB Editor Project Explorer, right-click on the module and select “Export File…“
   • Save the RXL_ProgressBar.bas file to your Desktop.
   • Select the Excel file you wish to import the module to in the VB Editor Project Explorer, right-click, and select “Import File…“
   • You can then select the RXL_ProgressBar.bas file from your Desktop and click “Open” – this will import a copy of the VBA module into your Excel project file.
2. Download from repository
   • Alternatively, go to the GitHub repository and the ProgressBar sub-folder, where the demo file is saved (https://github.com/r-silk/RXL_VBATools), and directly download the RXL_ProgressBar.bas file.
   • Open the Excel file you wish to import the module into, go to to the VB Editor and find the chosen Excel file in the VB Editor Project Explorer.
   • Right-click on the chosen Excel file and select “Import File…“; choose the RXL_ProgressBar.bas file where you downloaded it to and click “Open” – this will import a copy of the VBA module into your Excel project file.