Progress Bar in Excel VBA

.

How to use Progress Bar in excel Macro (VBA)

Progress bar is nothing but a placeholder, where you see the Progress of the operation which is getting performed.
Like Visual Studio there is NO already built progress bar in Excel Macro (VBA), which you can use it as an object and it will act like a Progress bar. But this is not a difficult task to create a Progress Bar in Excel Macro. In this article you are going to learn how to create/simulate different types of Progress bar in Excel macro (VBA).
Basically as part of this article we are going to discuss following different kind of Progress bar:

  1. Progress in Status Bar of Excel
  2. Simple Progress Bar using User Form Control
  3. Simple progress bar with % increment
  4. Free Download : Progress Bar Excel Workbook

Progress in Status Bar of Excel:

I believe this is the simplest way to show user the progress of your Excel Macro. For displaying and coding, both purposes it is very easy to use. Below is the Syntax to show the Progress of your Macro in Status Bar:
 
Application.StatusBar=<Your Message or Status here>

Example: Below is an example, which shows how to show Progress message in Status Bar of the Excel, when Macro is running.



Sub ShowProgressInStatus()
	Dim Percent As Integer
	Dim PercentComplete As Single
	Dim MaxRow, MaxCol As Integer
	MaxRow = 800
	MaxCol = 800
	Percent = 0
	For irow = 1 To MaxRow
		For icol = 1 To MaxCol
			Worksheets("Sheet1").Cells(irow, icol).Value = irow
		Next
		PercentComplete = irow * icol / (MaxRow * MaxCol)
		Application.StatusBar = Format(PercentComplete, "0%") & " Completed"
		DoEvents
	Next
	Application.StatusBar = ""
End Sub

The above code will display the Dynamic Progress in % while Macro is running as shown below. Message you can customize yourself by changing the above Code.

Progress in Status Bar

How to Show Progress in Status Bar


Simple Progress Bar using User Control Form:

In Excel Macro, there is no already built Progress bar Control, which can directly be used just by dragging and putting it in User Form. Below are the Steps to create a Progress Bar in your Excel macro Code.

1. Open one Excel Workbook
2. Press Alt+F11
3. Now Add a New User Form by Right Clicking on the Left side Project as shown below:

How to Create Progress Bar : Add a User Form

How to Create Progress Bar : Add a User Form


4. Now Change the Caption of the User Form as “Processing…”

How to Create Progress Bar : Add a User Form

How to Create Progress Bar : Add a User Form


5. Add one Label Control from the Control Box and also change the Back Color of the Label in different Color.

How to Create Progress Bar : Add a Label

How to Create Progress Bar : Add a Label


6. Remove the Caption of the label and keep it blank.
7. Now make the Size of the User Form same as the Label

How to Create Progress Bar : Add a Label-2

How to Create Progress Bar : Add a Label-2


8. Now Double Click on the User Form and Copy –Paste the below Code



Private Sub UserForm_Activate()
    Call ShowProgressBarWithoutPercentage
End Sub


Sub ShowProgressBarWithoutPercentage()
	Dim Percent As Integer
	Dim PercentComplete As Single
	Dim MaxRow, MaxCol As Integer
	Dim iRow, iCol As Integer
	MaxRow = 500
	MaxCol = 500
	Percent = 0
'Initially Set the width of the Label as Zero
	frmProgressBar.LabelProgress.Width = 0
	For iRow = 1 To MaxRow
		For iCol = 1 To MaxCol
			Worksheets("Sheet1").Cells(iRow, iCol).Value = iRow * iCol
			
		Next
		PercentComplete = iRow / MaxRow
		frmProgressBar.LabelProgress.Width = PercentComplete * frmProgressBar.Width
		DoEvents
	Next
	Unload frmProgressBar
End Sub

You are done with your Progress bar creation. Now you can activate the User Form at any point of time during your code and Progress Bar will start.
 

Important: How to call this Progress Bar?

Wherever you want to show this progress bar use the below statement:
 
frmProgressBar.Show
 
While running, this is how your Progress Bar will look like.

How to Create Progress Bar : Progress Bar

How to Create Progress Bar : Progress Bar


Simple Progress Bar with % Increment:

This can be achieved exactly the same way as we have done above. The only change will be in the code. You need to add one line to display the calculated % in Label Caption or User Form Caption.

The below highlighted line will display the Completed % dynamically.



Private Sub UserForm_Activate()
    Call ShowProgressBarWithoutPercentage
End Sub


Sub ShowProgressBarWithoutPercentage()
	Dim Percent As Integer
	Dim PercentComplete As Single
	Dim MaxRow, MaxCol As Integer
	Dim iRow, iCol As Integer
	MaxRow = 500
	MaxCol = 500
	Percent = 0
'Initially Set the width of the Label as Zero
	frmProgressBar.LabelProgress.Width = 0
	For iRow = 1 To MaxRow
		For iCol = 1 To MaxCol
			Worksheets("Sheet1").Cells(iRow, iCol).Value = iRow * iCol
			
		Next
		PercentComplete = iRow / MaxRow
		frmProgressBar.LabelProgress.Width = PercentComplete * frmProgressBar.Width
                frmProgressBar.LabelProgress.Caption = Format(PercentComplete, "0%")
		DoEvents
	Next
	Unload frmProgressBar
End Sub

You are done with your Progress bar creation. Now you can activate the User Form at any point of time during your code and Progress Bar will start.
 

Important: How to call this Progress Bar?

Wherever you want to show this progress bar use the below statement:
 
frmProgressBar.Show
 

This is how it will look while running the code:

How to Create Progress Bar : Progress Bar With %

How to Create Progress Bar : Progress Bar With %


If you need the Sample Workbook with Progress bar download it from here. If you are facing any issue in downloading it, please let me know.

Download Now

 
To Check out Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

39 Comments

  1. Karthik

    Hi,

    This is such a super-cool feature to have 🙂

    But, one quick question, how do I activate this progress bar. I've a macro-enabled excel sheet, and I want the progress bar displayed when it's running. Should I use a Call func – Call UserForm_Activate() – to activate it or should I include the entire macro snippet at the start of my macro code?

    Thanks again!

    Reply
    • Vishwamitra Mishra

      Hi where ever you want to display this Call this Function ShowProgressBarWithoutPercentage()

      If you want to use in your code, i can help you 🙂 🙂

      Reply
      • Karthik

        Hi Vishwa,

        I would need your help here. I inserted "Call ShowProgressBarWithoutPercentage()" at the start of my code, but it's throwing an error every time I execute it

        Compile error

        Sub or Function not defined

        Any idea how I can fix this?

        Thanks!

        Reply
        • Vishwamitra Mishra

          Hi Karthik,

          This is a very simple compile error. Looks like the Name of the function is not same as you calling. If you can send your excel on info@learnexcelmacro.com, then i can look in to it and fix it.

          Thanks,

          Vish

          Reply
          • Fazli

            Hi,

            I am facing this problem as well. Your frmProgressBar cannot be read.
            It shows “required object”.

            FYI, I am using excel 2003.

            Please advice.

            Thanks

          • Vishwamitra Mishra

            Hi Fazil,

            Are you facing this issue in the file, which is downloaded from the site?
            Please Let me know !!

          • Fazli

            Hi,

            Sorry I'm having issue with my explorer that why i cannot reply from below box.

            Yes, I have downloaded all the VBA and when I start running, the error message pop up that required object.

            If my macro are based on the sheet (tab), and not rows, do i need to change the rows formula?MaxRow and iRow?

  2. Ramu

    I want to show progress bar for my macro with "%". We dont know the timing completion.It has fetch the data from server and then process and formating the data. Depend upon the data size and speed of the server, macro will complete the task.

    Could you please help me to fix the issue.

    Reply
    • Vishwamitra Mishra

      Dear Ramu,

      Using Excel Macro, we can not Calculate the Time to be taken during any kind of operation in advance.

      Yes based on Number of records being fetched from the database we can calculate the Progress in % and display in the Progress Bar.

      Reply
  3. Matt

    I also have the same problem/error as karthik, how did you solve it?

    Reply
    • Vishwamitra Mishra

      Hi Matt,

      I have sent a sample Workbook with sample Progress Bar. Check if it helps, otherwise get back to me with your workbook, i may help you on this.

      Thanks,

      Vish

      Reply
      • Prasant

        I am facing the same problem like karthik. I inserted “Call ShowProgressBarWithoutPercentage()” at the start of my code, but it’s throwing an error every time I execute it

        Compile error

        Pl help

        Reply
        • Vishwamitra Mishra

          Hi Prashant,
          I have sent you the Workbook with Progress Bar. Hope that will help.
          Thanks

          Reply
  4. Alain

    I have a userform that shows after opening the file and collect some information and asks for the source file. Upon closing the userform, a series of macros will run to an undefined time as it will depend upon the size of the source file.

    Is there any way that I can define a progress bar for the whole process and not on every procedure/macro?

    It does not matter if it will be in the status bar or in a form of a progress bar with or without percentage, as long as there will be an indication that the macros are still running.

    Thank you for your help.

    Reply
    • Vishwamitra Mishra

      Hi Alain,

      Yes we can. Before going to the main program, is there any way to get the number of records or loop or times, it is going to run? Then we can equally divide the progress in 100% and that way it will solve your problem.

      I can help you on this.

      Reply
  5. Madiha

    Hi Vishwamitra! Excellent stuff here! 🙂 I seem to be facing the same problem as karthik and prasant. It gives an error everytime I try to execute the code. Any suggestions?

    Reply
    • Vishwamitra Mishra

      Kindly send me the error with your workbook to me.

      Reply
  6. Mario

    Hello,

    I'm trying to use the progress bar in a loop (i.e. to show the number of loops performed).

    Where should I put the progress bar code? Inside the loop or before it? i tried to put the loop code where you put "Worksheets("Sheet1").Cells(iRow, iCol).Value = iRow * iCol" but it does not work…

    Any ideas?

    Reply
    • Vishwamitra Mishra

      Solution sent to your email id !!

      Reply
  7. Satpal Matharu

    Hi Vishwamitra

    Thanks for your code but I am having problems trying to adapt it.

    I have added the 'Simple Progress Bar with % Increment' in a userform.

    I am trying to call this in my module 4.

    I have:

    Sub Validation()

    Call ShowProgressBar

    Reply
    • Satpal Matharu

      Ctd…

      I get a 'Sub or Function not defined' error message.

      Reply
    • Vishwamitra Mishra

      Hi Satpal,

      you should use the below statement wherever you want to show your progress bar.

      frmProgressBar.Show

      You can also download the sample workbook and see how it is working.

      Reply
      • Satpal Matharu

        Hi Vish

        I can see what its doing in the form but am not sure how to implement it to my working file. Can I send you file and can you help me put the progress bar in there?

        Thanks

        Reply
        • Vishwamitra Mishra

          Yes please !!

          Reply
          • Satpal Matharu

            Hi Vish

            Tried sending you the file but got an email failure. It said your inbox was full.

  8. Irshath

    Hi Vishwa,

    It is nice..Actually I want to show the progressbar while opening a huge excel file through excel macros. it takes around 50 – 120 secs to open.. so in that time period I want show this progressbar.. that please wait the file opeiong in progress… like that… could you please help me on this..

    Reply
  9. Chris

    I am trying to use this code when copying a sheet to another sheet in the same workbook. I am getting an error on: worksheets("sheet1").cells(irow,ect… I have tried replacing "sheet1" with activesheet.select or even diming the sheet as a variable but it still wont work. Anyone have any ideas?

    Reply
  10. laxmanan

    Hi Vishwa,

    I had a try with your progress bar for my data base sheet.but i cant and i dont know where i can apply your codes to my database sheet.Also i sent a seperate mail regarding this.Can you please help me out..

    Thanks,

    Lakshmanan M

    Reply
  11. Ejaz Ahmed

    The label control that resizes is a quick solution. However, most people end up creating individual forms for each of their macros. I used the DoEvents function and a modeless form to use a single form for all your macros.

    Here is a blog post I wrote about it: http://strugglingtoexcel.wordpress.com/2014/03/27/progress-bar-excel-vba/

    All you have to do is import the form and a module into your projects, and call the progress bar with: Call modProgress.ShowProgress(ActionIndex, TotalActions, Title…..)

    I hope this helps.

    Reply
    • Vishwamitra Mishra

      Great Job Ejaz !!

      I will definitely try this and provide you the feedback

      Reply
  12. deepika

    Hi Please could u please provide me the Code to upload test cases from Excel to QC

    Reply
  13. deepika

    Could u please share the Macro code to upload Test cases from Excel to QC

    Reply
    • Vishwamitra Mishra

      Hi Deepika,
      Any specific reason you want the code to export test cases from Excel to QC? Because there is an Excel Add-in created by HP itself to upload data from Excel to QC. Are you aware of that? If not you can follow the below steps to install that:
      1. Open this URL /qcbin (Note only go till /qcbin. remove start_a… which is part of complete URL)
      2. Now click on the link to open Add-in Page
      3. There you can go to Excel Add-in
      4. Download and Install this add-in
      5. Now in your excel sheet you will see a button “Export to QC” under Add-in Tab in the Ribbon.

      You are done now. Hope this helps !!

      Reply
  14. Jose

    Hi,

    I was wondering if you could please help me with the Excel I´m working on.
    I have created several macro buttons. The excel works perfectly! What I need is to add the progress bar/indicator in each macro.
    I´m not an VBA expert, so I was wondering if you could help me to add the codes in each macro so that it shows the progress indicator
    The Excel file consists in 4 macros.
    1) “Update Cecos”: this macro enters to SAP with the parameters typed on cells I7,J7 and K7 and updates several variants created in SAP system with a list of information from another Excel workbook.
    2) “Download SAP Actuals”: as macro 1, it enters to SAP and download an excel file from SAP to the share folder.
    3) “Run GFER View”: it processes the downloaded excel from SAP and it sends it to the Excel workbook we are working on (“Reporte SAP Actuales – Jose”).
    4) “Delete”: it deletes all the cells from row 35 down to the last cell with any value.

    Please let me know if you are interest in helping me with this.
    If your answer is YES, I will be pleased to send you a copy of the Excel Workbook.

    Thanks and regads,
    Jose

    Reply
  15. Mike

    Vishwamitra,

    Thank you for putting this example together as it is very neat. I am having a problem implementing it. I have 5 modules that run in sequence and over the 5 modules there are approximately 23 subroutines. What I want to do is at the end of each subroutine, pass a value starting at 1 all the way to 23 with the last module and have the progress bar calculate mod#/23 as my percent complete but I am struggling to pass the value at the end of the module to the showprogressbar subrountine, can you provide any suggestions? Also does the showprogressbar subroutine have to be on the form module?

    Thanks,
    Mike

    Reply
  16. Stef

    I am Stef in Indonesia.
    Please give me an example simple application with Progress Bar

    Thank’s for help

    Reply
  17. Pradeep Kumar

    Hi Vishwa
    Please help me on word macro, I have created a Macro that worked in multiple file in a folder. I want to create a Progress form that provide of the my macro processing.

    Regards Pradeep

    Reply
    • Vishwamitra Mishra

      Hi, is this article not helping you in order to get a progress bar?

      Reply

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest