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
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.
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
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
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
When the code is done, the CloseProgressBar sub removes the form from the screen.
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
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.
|Without Progress Bar||With Progress Bar|
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
Enter your email address below to download the sample workbook.
The progress bar code consists of a form module named ProgressBar, and a code module named Progress. After you download the sample workbook, you can drag and drop the modules into your own workbook like so:
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.
Thanks a lot for the code. Very nice and straight forward.
Just one question: why not putting the DoEvent call directly in the ShowProgress sub?
I tried and it seems to work. This simplifies a bit the code when not updating in a loop…
This is very informative and cool as well.
My sincere thanks for sharing this code and the understanding behind it.
I have successfully implemented the same with some additional tweaks.
You’re welcome, glad it was useful.
Absolutely simple but fantastic code. Thank you so much for sharing.
Avid fan of your site: Michael
Thanks Michael, glad you found this useful.
Thanx for your great code.
But I don’t have enough experience to use it in my workbook. Can I post my code here to help me with it?
Thanx in advanced.
Hi Khaled,You can sign-up to our forum, create a new topic, then uplaod your sample file, we will help you fix it.
a very good job!
I have some trouble by using your code into my application.
If I use it “stand-alone” the routine run very well.
But If I put “Call InitProgress(50000)” in a Userform of my software, Excel show me a Runtime 401 error (“You can not display the form…”): with the debug I can see that the routine stop in “.Show vbModeless”.
Where is my error? Could You help me?
Please open a Helpdesk ticket and send me your workbook so I can see what is going on.
Thank you providing such a wonderful VBA Tip.
I am not able run the code provided in the Excel available for download as I am getting compile error
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statement and then mark them with the PtrSafe attribute.”
Please suggest how to resolve it.
When did you download the workbook? There isn’t any 32 bit only code in the workbook linked to above?
Nice bit of code, thanks. I have copied your code to a procedure that I am testing. However, the userform “whites out” and does not display anything. This doesn’t happen with your example. From a cursory search I’ve found that this may happen if the code runs too quickly for the computer to compute. Suggestions include using a small delay timer but this obviously slows the code down.
Do you have any thoughts?
I think you might be missing the DoEvents call before you try to update the bar progress. Sorry, that’s my fault, I didn’t make it clear in the post that you need to call DoEvents.
I’ve added a section above now so just have a look at that.
Thanks, that worked perfectly.
This is such a useful procedure to use in my line of work.
No worries Toby, glad that you find it useful.
What are you using it for?
I am using it to generate a Snakes and Ladders simulation in a pseudo Monte Carlo fashion to see how many rolls of the dice it takes on average to win. My interest was piqued the other day when I was playing my children and the game took forever as I kept going back to zero.
So if I run thousands of games I can use the progress bar to see how far along I am.
Cool. Snakes and Ladders, yes it can take forever.
Thanks, this is really helpful. Only thing that I’m having trouble with is that my processes go for about 10 seconds. With the progress bar, it’s up to 100% after about 3 seconds and it just sits there for the next 7 seconds. Is there any way to progress it to 100% more evenly so people don’t think it’s stuck?
Can you send me your workbook so I can see what it is doing? If you open a Helpdesk ticket you can attach the wb to that.
How would I go about adding this to my VBA macro assigned to a button that sends current active worksheet to designated email address. Is there a way to add this code to my VBA macro? Is this possible?
Yes, you just add the calls to my code as described in the blog post. Without seeing your code I can only give general instructions. But if you are only emailing one sheet, how long does it take? Is the progress bar required?
Once you have opened the sample workbook, copy (drag and drop) the form and code modules into your workbook.
Somewhere towards the beginning of your code you need to initialize the bar code by calling InitProgressBar(MaxSteps) where MaxSteps is the maximum number of steps in your code, but in this case it sounds like there is only 1 step, which is emailing the 1 worksheet.
As you do each step in your code update the progress bar with calls to ShowProgress(Step) where Step is the step you are on in the code, i.e. how many steps in the task have you completed. Again though, it sounds like you only have 1 step.
When your code is done, call CloseProgressBar to remove the progress bar.
If your issue is that your code is taking a while and excel appears to have hung, as the task as you describe sounds like it only has 1 step, you may be better off just creating a form that has a message that reads, for example, “Preparing Worksheet and Emailing” then remove that form when the email is sent.
You could modify my progress bar form and code for this or you can create your own from from scratch. See this post for instructions https://www.myonlinetraininghub.com/excel-forms
You could also just a message on the Status Bar to communicate this information. I’ll be writing a blog post on this next week but I will email you the code shortly.
I have a large Excel file with a VBA program to process project data from a database. It’s used by many project managers on several continents every month (I work for a very large company). Naturally, that means it’s used on computers with many different configurations and languages so something is bound to go wrong from time to time.
My program uses Mynda’s progress bar with three added label boxes:
The first label describes where the VBA program is in its execution, both in words and by step number, eg: “Step 1.03 — Calendarizing monthly data”. Every time I update the status bar, I update this label text. If the program dies, the user can send me a screen snapshot to help me debug the issue. At least I know where to start looking.
The second label is my contact information. Name, phone and email. So the user knows who to contact.
The last label contains the program version: Version.Revision.Modification such as V01.R12.M17.
Useful modifications to the bar, excellent.
Congratulations, very good
I have a bug in Excel Progress Bar for VBA Can anyone help me?
Sure, please post a topic on the forum with you workbook/code and we’ll have a look.