Excel Progress Bar for VBA

If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback is useful for your users.

Letting them know just how long something will take to complete, and just as importantly, letting them know that Excel hasn't hung, reassures them that the code is working and that it will be finished soon(ish).

This is where you can make use of a progress bar for VBA code you create.

Excel doesn't have any progress bar functionality built into it so we have to create our own.

Creating a Progress Bar

To make this we will use a form with three labels, and a cancel button. That's it, not too tricky.

You could also uses frames, or a combination of frames and labels, but I liked the way two labels looked so that's what I went with.

The first label is named BarBorder and serves to enclose our colored bar within a border, but also indicates the length of the task being performed. That is, you know that when the colored bar gets all the way to the right hand side of the border, the task will be complete.

The second label is named BarColor and is the bar that moves left to right to indicate progress.

I've used a third label called ProgressText which can contain any message you like. I've chosen to use it to display the % of the task completed.

Finally there's a cancel button so the user can end the task if they want. You can leave this out if you wish as terminating the code part way through may not be what you want. While I mention that point, make sure you use error handling in that situation.

The completed, working bar looks like this

Bar Color

There aren't many colors available to you by default when you are designing your form. When you add a label you can choose between the System colors, or the limited range on the Palette.

The default color for the background (BackColor) is a very light grey:

You aren't restricted to those colors though and can enter your own, you just need to know how. There are two ways you can do this.

If you right-click on any of the white squares at the bottom of the palette, you get a color chooser like this:

Select the color you want by moving the slider and pointer around, or enter the HSL or RGB values, then click 'Add Color'.

The 2nd method is to enter the hex values for your color directly into the properties window. The colors for the form controls (labels, frames, buttons etc) are specified in an unusual Hexadecimal format. If we look at the default Backcolor for a label it is &H8000000F&, or written so that it's easier to read &H80,00,00,0F,&.

I've color coded those numbers because they correspond to the Red, Green and Blue component of the color. Normally you'd write that RGB, but in this format it's reversed as BGR.

Also, the default colors are predefined constants, so just changing the red component to FF will not give you red, it'll give you an error. To specify your own colors you must change the &H80 to &H00.

Our main site color is a shade of green which in hex is 75AD21. Red = 75, Green = AD, Blue = 21. So to use that color for my progress bar I specify the BackColor for the BarColor label as &H0021AD75&

If you have a particular color you want to use, maybe to match your company colors, just find out the color's hex or RGB value and you now know what to do.

Calculating Progress

Each task will take a different amount of time and will have a different amount of steps before it is complete. So how do you work out how you are progressing and when you are done?

At this point it should be noted that a progress bar isn't necessarily totally accurate. It is there to indicate that the task is still going and to let the user know that Excel hasn't crashed. Whether you are 46% of the way through the job, or 48%, doesn't really matter.

Anyone who has ever copied a large amount of files in Windows can testify to this. One moment it'll tell you that the copying will be done in 10 seconds, then it tells you it'll be another 3 hours.

This is why I have also chosen to not show any elapsed time or estimated time remaining on the progress bar. You can do so if you wish of course, but I find that it can be misleading to display time remaining, and it can also difficult to calculate.

My approach requires that you know the maximum number of steps in a task, and what step you are currently on.

For example, if you are executing a loop and the bounds for the loop are 1 to 5000, then your maximum number of steps will be 5000. Your current step will be whatever your loop counter is currently set to.

Initializing the Progress Bar Code

When we know the number of steps in the task, we can initialize the code. This involves setting a value we use during code execution which tells us how many steps equate to 1% of the progress bar's width.

Look at the code below and imagine that the width of our progress bar (.BarBorder.Width) is 250. We have 5000 steps in the task. 250/5000 = 0.05 which means that every 20 steps (1/0.05) equates to 1% of the task completed.

Sub InitProgressBar(MaxValue As Long)

    With ProgressBar
    
        .BarColor.Tag = .BarBorder.Width / MaxValue
        .BarColor.Width = 0
        .ProgressText = ""
        .Show vbModeless
        
    End With
    
End Sub

Updating Progress

Every time we update the progress bar (see code below) we set the width of the colored bar (.BarColor.Width) to 0.05 * 'number of completed steps' (.BarColor.Tag * Progress). I use Round to make sure we always have integers to show our completed progress

Sub ShowProgress(Progress As Long)

  With ProgressBar

    'Round Up
    .BarColor.Width = Round(.BarColor.Tag * Progress, 0)
    .ProgressText.Caption = Round((.BarColor.Width / .BarBorder.Width * 100), 0) & "% complete"
    
  End With

End Sub

DoEvents

Once we display the form, Excel won't 'redraw' or 'repaint' the screen unless we allow it to do so. So unless you allow Excel to repaint the screen, the form will appear to be whited-out and the bar won't appear correctly.

To allow this to happen, we use a call to the DoEvents sub in our loop, before we call the ShowProgress sub.

    For j = 1 To Max
    
        DoEvents
        ShowProgress (j)
        
    Next j

Task Completed

When the code is done, the CloseProgressBar sub removes the form from the screen.

vbModeLess

Normally when you display a userform it is done so as a modal form. Which means that Excel stops working until you interact with the form, like clicking a Cancel or OK button.

We need our code to continue working while the form is displayed so we display it as modeless form

.Show vbModeless

Using the Progress Bar in Your Code

I've written some basic test code which you can get in the sample workbook (see below), but I've also incorporated the progress bar into the code I wrote for finding missing numbers. This too is in the sample workbook you can download.

In order to use the code yourself you need to initialize it, with the maximum number of steps your code will perform, call the ShowProgress sub to update the progress bar, and finally use CloseProgressBar to remove the form when your code is done.

Overhead - Does This Slow the Code Down?

The short answer is yes, using a progress bar will slow your code down a bit, but it won't be noticeable - see the next section for some real world tests. But if you have a lengthy task, you need to consider if the extra few seconds it takes to display the progress is outweighed by the reassurance you give your users by letting them know Excel hasn't died and gone to digital heaven.

In my opinion if my code is going to take a significant amount of time, I'd use a progress bar just to let people know Excel is still working, and give them some idea of how long they will have to wait.

Measuring the Extra Time

To measure how much extra time it takes a routine to run and use the progress bar, I ran some tests with my finding missing numbers code

I ran two batches of tests. The first with 10,000 numbers, the second with 50,000 numbers, and in both cases I ran the code to find the missing numbers both with, and without, displaying a progress bar. Here are the results.

As you can see the code was taking tenths of a second longer to execute when it was showing a progress bar. If you crunch the numbers I make it that the code with the progress bar took between 1% and 3% longer.

Of course you may not get exactly the same results as I did, but I think this is a fairly decent estimation of how much extra time the progress bar adds to the execution of some code.

For code like this that only runs for a few seconds, you may not bother with a progress bar, and only use one with code that takes longer to execute.

It would seem reasonable to assume that the longer the routine goes on, the more time the routine has to spend calling the progress bar code. But those extra few seconds will be worth it to keep the user informed of what's going on.

Getting the Code Into Your Workbook

Next - The Status Bar

In my next post I look at using Excel's Status Bar to display progress, and other messages, as your code executes.