VBA Application.StatusBar to Mark Progress

Introduction - Application.StatusBar

Use the VBA Application.StatusBar Property to display progress updates when your macro is running.

Although not as fancy as the Beautiful VBA Progress Bar, the StatusBar Progress Bar is a great way to track your macro’s progress and it has the added benefit of being remarkably easy to implement.

The Status Bar progress bar shows up in the lower left of Excel and doesn’t require you to design your own UserForm or anything. How simple is that?

Example - Application.StatusBar

In this example macro, I loop through each row in a spreadsheet and update the status bar at the bottom of Excel as I go. The macro is fully commented so if you want to grab it and go, go right ahead. I’ll add a detailed tutorial below the example for those wanting to stick around and see how it works.

Sub StatusBar_Updater()
Dim CurrentStatus As Integer
Dim NumberOfBars As Integer
Dim pctDone As Integer
Dim lastrow As Long, i As Long
lastrow = Range("a" & Rows.Count).End(xlUp).Row

'(Step 1) Display your Status Bar
NumberOfBars = 40
Application.StatusBar = "[" & Space(NumberOfBars) & "]"

For i = 1 To lastrow
'(Step 2) Periodically update your Status Bar
    CurrentStatus = Int((i / lastrow) * NumberOfBars)
    pctDone = Round(CurrentStatus / NumberOfBars * 100, 0)
    Application.StatusBar = "[" & String(CurrentStatus, "|") & _
                            Space(NumberOfBars - CurrentStatus) & "]" & _
                            "   " & pctDone & "% Complete"
    DoEvents
    '--------------------------------------
    'the rest of your macro goes below here
    '
    '
    '--------------------------------------
'(Step 3) Clear the Status Bar when you're done
    If i = lastrow Then Application.StatusBar = ""
Next i
End Sub

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Tutorial - Application.StatusBar

The example macro has 3 main parts. These 3 steps are common whether or not your macro uses a For loop like the one above.

Step 1 - Display your Status Bar

At some point in your macro, you want to show your status bar. Chances are, you want to do this right after your macro starts to run. All you have to do is add the following lines to your macro, like I did in the example above:

'(Step 1) Display your Status Bar
NumberOfBars = 40
Application.StatusBar = "[" & Space(NumberOfBars) & "]"

The variable NumberOfBars represents the width of your progress bar in the StatusBar. I find that in Excel 2010, a width of between 20 and 50 characters works well.

Step 2 - Periodically Update your Status Bar

At some point, you want to update your status bar to indicate how far along your macro is. The way I do this is by replacing spaces with the character |. I chose this character because it has the same character width as a space (“ “) in Excel’s StatusBar font. In my example, I used a formula to calculate what percentange of the way through the macro I was as a function of the number of bars we defined. I did this for each row inside the loop by adding the following code:

'(Step 2) Periodically update your Status Bar
    CurrentStatus = Int((i / lastrow) * NumberOfBars)
    pctDone = Round(CurrentStatus / NumberOfBars * 100, 0)
    Application.StatusBar = "[" & String(CurrentStatus, "|") & _
                            Space(NumberOfBars - CurrentStatus) & "]" & _
                            "   " & pctDone & "% Complete"
    DoEvents

As my percent complete grows, my status bar updates with more bars.

This is a nice way to indicate progress if your macro consists of a loop where you know the starting position and you know the ending position. Examples where a solution like the one above will work are when you’re processing each line of data in a spreadsheet, or looping through files in a folder.

Step 3 - Clear the Excel Status Bar

When your macro is almost finished, reset your StatusBar by setting it equal to an empty string. In my example, I performed my reset when I got to the last row. You can do the same with a line similar to the following:

'(Step 3) Clear the Status Bar when you're done
    If i = lastrow Then Application.StatusBar = ""

Final VBA StatusBar

When you put it all together, you’ll get an automatically updated VBA StatusBar that looks like this:

Application Ideas

When I create progress bars using the Application.StatusBar method, I’m often concerned that people won’t actually notice it tucked away in the corner of Excel. This concern may or may not be founded, but I still don’t shy away from using it where I can.

With that said, if you have a project where you know you’ll have a long macro running and you want to guarantee that your user knows it too, I still recommend my Beautiful VBA Progress Bar, instead.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.