Excel Status Bar

In my previous blog post I created a progress bar using a userform. We can use this progress bar to communicate to our Excel user that our VBA code is still running.

In this post I’m going to look at using the Status Bar to do the same thing, and create something like this.

Status Bar

The status bar is the bar at the bottom of your Excel window.

You can put your own text messages on it by using a call to Application.StatusBar e.g

Application.StatusBar = "Hello world!"

Once you have put your message on there, it stays there. So when you are done you have to clear your message and return control of the Status Bar to Excel. You do this like so:

Application.StatusBar = False

Calculating Progress

To update progress on the Status Bar you call the sub StatusBarProgress and pass in two values, CurrentStep and Max. CurrentStep is how far along in your code you are, and Max is the maximum number of steps in the code (i.e. 100%)

StatusBarProgress CurrentStep, Max

As with the progress bar code, this code needs to know how far along in the assigned task your code is. Let’s say we are processing cells in a range, and you are looping through each cell in turn doing something with it.

The number of steps in your code would then be the number of cells in your range, which would correspond to the variable you are using as your loop counter.

Likewise if you are processing X number of rows, then the number of steps in your code is X.

Not every task will involve looping, and larger programs will involve several different tasks to achieve the goal. In such cases you could assign certain points in your code to be X% complete for the entire program and update the status bar accordingly, e.g.

Sub MyCoolSub

	'Some code 
	.........
	StatusBarProgress 20, 100
	
	'More code 
	.........

	StatusBarProgress 50, 100

	'And more code 
	.........

	StatusBarProgress 80, 100

	'The final bits of code 
	.........

	StatusBarProgress 100, 100

End Sub

If you don’t know the maximum number of steps your code will need to complete when you are writing the code, you can try working this out once the code is running.

As an example let’s say we are going to ask the user to browse to a folder, and after they have done that, our code is going to merge all the workbooks in that folder into one workbook.

We don’t know beforehand how many workbooks will be in the folder, but once the user has indicated which folder we’ll be working with, we can check how many files are in there, and that number becomes our maximum number of steps.

Changing the Status Bar

What is displayed on the status bar is just text, so we don’t have any fancy graphics to use like we can with our form progress bar.

If you grew up when I did with ASCII art and DOS interfaces then you’ll be familiar with what I’m about to look at.

To create our progress bar we need to specify a symbol as our progress character and another as our space character. When we put these characters together we end up with a bar.

Let’s say we use | as our progress character and a space " " as our space. If our maximum number of steps is 10 then when we have completed 30% of the task our progress bar looks like this

       |||      

You can’t see them, but there are 7 spaces at the end of that string. This is why I like to use starting and ending characters, like bookends, to indicate the overall length of the bar. I use [ and ] so adding these to our status bar we get

      [ |||       ]

Bar Width

I’ve set the width of my bar with a constant called MaxWidth (look in the StatusBarProgress sub)

      Const MaxWidth = 40

Changing this value will make your bar shorter (smaller values) or longer (larger values).

Character Width

The characters you choose for progress and space must be the same width. If they are not then the length of the bar will change as your code progresses and space characters are replaced with progress characters.

There isn’t any easy way to set the font that the status bar uses, so I’m just going to stick with the font I have.

| and a space character are the same width so they work well together.

: is also the same width as a | so you can use either.

In the code you’ll see I have sections where I specify pairings of progress characters and space characters. These are combinations I have found work well together as they are the same width.

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Progress_Char = "|" ' [||||  ]
    'Progress_Char = ":" ' [::::  ]
    Space_Char = " "
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Progress_Char = "X" ' [XXXX--]
    'Space_Char = ChrW(&H2012) ' Figure Dash works with X
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Progress_Char = ChrW(&H258C) ' Left Half Block works with underscore
    'Space_Char = "_"
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Progress_Char = ChrW(&H25AC) ' Black Rectangle
    'Space_Char = ChrW(&H2014) ' Em Dash
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Unicode

You aren’t restricted to the characters on your keyboard either, you can use UNICODE characters.

Open up Character Map (search for charmap in your Windows Search Box) and you can have a look through all the characters available to you.

Once you find a character you want to use, note the hex code for it. The Left Half Block above is hex 258C, and you can then use that with the ChrW function to return the character specified by that hex value:

     Progress_Char = ChrW(&H258C)

You can comment out and uncomment different combinations of characters in my code to see what they look like and choose the ones you want. Just add or remove ' at the beginning of the appropriate lines of code.

Just Using Text Messages

You don’t have to use an animated bar, you could just use a simple message on the status bar, e.g. 35% Complete.

If this is all you want then right at the end of the TestStatusBar sub, just use the line

Application.StatusBar = Round(CompletedSteps / MaxWidth * 100, 0) & "% Complete"

Progress Bar v Status Bar

We now have two ways to communicate with our users that our code is working. Each approach has its pros and cons.

• The Status Bar has no cancel button, so once your code starts, your average user can't stop it. The Progress Bar does have a Cancel button to stop code execution.
• The Status Bar doesn’t require calling DoEvents, which potentially means it would be faster.
• Writing code to use the Status Bar is easier.
• The Progress Bar is nicer to look at.
• The Status Bar is limited in its colors and fonts, and no graphics are available.

In the end it's up to you which method you choose. Or you could use both.

Does Using the Status Bar Make Code Slower?

I ran the same tests I used for the progress bar to see what delay using the Status Bar caused, and found that the code takes tenths of a second longer, roughly the same as the progress bar.

Get the Code