The Perfect Progress Bar

A progress bar's purpose is to assure users our program is running and about when it will finish. The perfect progress bar does that and includes an optional Cancel button so users can kill processes that take too long. And it wouldn't be perfect if it wasn't simple to implement in Excel (NOTE! Windows version only!). The perfect progress bar:

• Is just one object in our VBA (a userform) making it easy to import into other projects
• Can be called in just 1 line of VBA
• Has an optional cancel button to interrupt process that are taking too long
• Can display messages in the progress bar and in the userform's title.
• Can, in many cases, predict accurately at what time our process will end.

To build the user form we need 5 controls:

1. frmProgress - The user form
2. lblBack - A white label behind the expanding bar
3. lblBar - An expanding gray label
4. txtBar - A textbox with transparent background where we can display progress messages over the expanding bar.
5. cmdCancel - An optional command button for killing the process

At the bottom of this post are each control's settings, but let's look at the form's code first.

When we initialize the form we set the bCancelled property to its default of FALSE. We then set the form's position to our workbook's center.

The QueryClose routine handles someone clicking the form's red X. It acts like clicking the Cancel button.

The form's Display method is our form's only interface. It returns vbOK or vbCancel. vbCancel indicates the user clicked the cancel button or the red X.

The only required parameter is fPercent which is the percentage complete (where 0.10 = 10%). This determines the bar's length.

All other parameters are optional. They add titles and text, as-well-as the Cancel button if we want to give users that option.

Here is an example on how we call the form.

We could call this test routine from the VBE immediate window like so:

Test 1000

In this example we have a loop calling oProgress.Display() repeatedly to update the progress bar. If our user clicks the Cancel button, the routine exits the loop.

To learn how to predict a process's ETA, follow how tBegin, tElapsed, and tEstimate are used in the code.

If you are wondering why I created a variable called oProgress to hold the userform, it is so we can display multiple progress bars simultaneously. This is helpful when we have a master process calling several lower level processes with the master process showing a high level progress while the lower level processes show their individual progress. Each of these processes can have their own instance of frmProgress running totally independent of other instances.

Here are each control's settings. Most settings are defaults so it does not take us long to adjust them.