The Only Progress Bar You Will Ever Need
Seriously, you don’t need a progress bar
Have you ever heard this one?
The road to hell is paved with good intentions.
Google “excel vba progress bar” sometime and prepare for a hell paved with good intentions. There are dozens of write-ups, thousands of lines of custom UserForm code and more designs then one person could use in a lifetime.
Save yourself the heartburn and summarily ignore every single piece of UserForm-based progress bar advice that comes your way.
When you’re working on an Excel app that takes a long time or enters a big loop, all you need to do is make sure your user knows that Excel has not frozen. And for that, Application.StatusBar is definitely the right look.
Here’s an example with a big outer loop that appends “Smith” to the end of 1000 first names.
Operating on every cell (i.e. row) in this column
Since all we’re really interested in for this particular example is the “progress bar” aspect, let’s talk about lines 26 through 29 (and eventually line 34).
There are two variables in play here: lngIdx, which is the counter that iterates through the rows, and lngLastRow, which we assigned to be 1001 way up on line 10.
By showing the user an incrementing counter, he or she will be able to clearly see that the script is running. Mission accomplished!
We can add a little sugar to the display with the Format function, which calculates the progress as a ratio (i.e. lngIdx / lngLastRow), then formats the result as a percentage (“0%”).
One last thing — line 34. Before finishing, we want to set the StatusBar back to its default state, which is as easy as setting it to False. Boom!
Here’s a 4-minute review and explanation of Application.StatusBar:
Have you ever genuinely needed to create a progress bar? If so, let me know — I’d love to know more! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.