• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Progress Bar for VBA

You are here: Home / Excel VBA / Excel Progress Bar for VBA
Progress Bar for Excel VBA
June 9, 2016 by Philip Treacy

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

Progress Bar in excel VBA

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.

System ColorsPalette Colors

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

Default form control color

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:

Define your own colors

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&

Custom form control color

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.

Without Progress Bar With Progress Bar
10,000 2.4s 2.5s
50,000 12.1s 12.5s

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

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:

Drag and drop VBA modules

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.

Progress Bar for Excel VBA

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Calculate end of period dates in Excel

Excel End of Period Dates

Calculating Excel End of Period Dates can be tricky if you don't use standard month ends. Here is a formula and UDF that solves the problem.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Excel VBATag: Excel VBA
Previous Post:Excel Power Query VLOOKUPExcel Power Query VLOOKUP
Next Post:Excel Status BarExcel Status Bar

Reader Interactions

Comments

  1. LIONEL GARNIER

    June 7, 2019 at 10:27 pm

    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…

    Reply
  2. Ashish

    September 22, 2018 at 12:05 am

    Hi,

    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.

    Best Regards
    Ashish

    Reply
    • Philip Treacy

      September 22, 2018 at 4:05 pm

      You’re welcome, glad it was useful.

      Phil

      Reply
  3. David Ford

    September 14, 2018 at 10:43 pm

    Brilliant!

    Reply
    • Philip Treacy

      September 15, 2018 at 9:49 am

      Thx david

      Reply
  4. Michael

    August 29, 2017 at 11:56 pm

    Absolutely simple but fantastic code. Thank you so much for sharing.
    Avid fan of your site: Michael

    Reply
    • Philip Treacy

      August 31, 2017 at 10:24 am

      Thanks Michael, glad you found this useful.

      Phil

      Reply
  5. Khaled

    August 10, 2017 at 3:05 am

    Hi,
    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.

    Reply
    • Catalin Bombea

      August 10, 2017 at 8:36 am

      Hi Khaled,You can sign-up to our forum, create a new topic, then uplaod your sample file, we will help you fix it.
      Cheers,
      Catalin

      Reply
  6. Dilba

    July 3, 2016 at 3:07 am

    Hi Philip,
    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?
    Thanks
    Dilba

    Reply
    • Philip Treacy

      July 3, 2016 at 9:01 pm

      Hi Dilba,

      Please open a Helpdesk ticket and send me your workbook so I can see what is going on.

      Regards

      Phil

      Reply
  7. Vikas Sinha

    June 16, 2016 at 12:30 pm

    Hi Philip,

    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
    ”
    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.

    Thanks

    Reply
    • Philip Treacy

      June 16, 2016 at 12:39 pm

      Thanks Vikas.

      When did you download the workbook? There isn’t any 32 bit only code in the workbook linked to above?

      Try this link http://d104tc5h8eoywb.cloudfront.net/Progress_Bar.xlsm for a fresh copy of the workbook.

      Regards

      Phil

      Reply
  8. Toby Cooper

    June 13, 2016 at 12:21 pm

    Hi Phillip,

    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?

    Thanks,

    Toby

    Reply
    • Philip Treacy

      June 13, 2016 at 1:18 pm

      Thanks Toby.

      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.

      Regards

      Phil

      Reply
      • Toby Cooper

        June 13, 2016 at 2:39 pm

        Thanks, that worked perfectly.

        This is such a useful procedure to use in my line of work.

        Toby

        Reply
        • Philip Treacy

          June 13, 2016 at 2:48 pm

          No worries Toby, glad that you find it useful.

          What are you using it for?

          Regards

          Phil

          Reply
          • Toby Cooper

            June 14, 2016 at 8:29 am

            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.

          • Philip Treacy

            June 14, 2016 at 8:51 am

            Cool. Snakes and Ladders, yes it can take forever.

  9. Tiffany

    June 10, 2016 at 2:53 pm

    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?

    Reply
    • Philip Treacy

      June 10, 2016 at 9:19 pm

      Hi Tiffany,

      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.

      Cheers

      Phil

      Reply
  10. Mark C

    June 10, 2016 at 7:52 am

    Hello Philip
    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?

    Thanks,

    Reply
    • Philip Treacy

      June 10, 2016 at 10:07 am

      Hi Mark,

      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.

      Regards

      Phil

      Reply
  11. David Bonin

    June 10, 2016 at 12:32 am

    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.

    Reply
    • Philip Treacy

      June 10, 2016 at 9:48 am

      Hi David,

      Useful modifications to the bar, excellent.

      regards

      Phil

      Reply
  12. Ferreira

    June 9, 2016 at 10:10 pm

    Congratulations, very good

    Reply
    • Philip Treacy

      June 10, 2016 at 9:46 am

      Thanks,

      Regards

      Phil

      Reply
      • masoud heydarzadeh

        October 18, 2019 at 6:42 am

        I have a bug in Excel Progress Bar for VBA Can anyone help me?

        Reply
        • Philip Treacy

          October 18, 2019 at 8:40 am

          Sure, please post a topic on the forum with you workbook/code and we’ll have a look.

          Regards

          Phil

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x