Progress Bar in Excel VBA



Excel Macro

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:

Progress in Status Bar of Excel
Simple Progress Bar using User Form Control
Simple progress bar with % increment
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.

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

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

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

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

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

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 %

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.

To Check out Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

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.

Hurray..Lets Go

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

Feb 9, 2024

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

Feb 3, 2024

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

Feb 3, 2024

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 (=).

Know more

Karthik on March 3, 2012 at 5:12 pm

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 on March 3, 2012 at 8:05 pm
  
  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 on March 4, 2012 at 4:20 am
    
    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 on March 4, 2012 at 12:54 pm
      
      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 on July 25, 2012 at 5:25 am
        
        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 on July 25, 2012 at 6:20 pm
        
        Hi Fazil,
        
        Are you facing this issue in the file, which is downloaded from the site?
        Please Let me know !!
      - Fazli on July 26, 2012 at 12:47 am
        
        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?
Ramu on March 12, 2012 at 3:04 pm

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 on March 12, 2012 at 6:20 pm
  
  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
Matt on March 14, 2012 at 1:45 pm

I also have the same problem/error as karthik, how did you solve it?
Reply
- Vishwamitra Mishra on March 15, 2012 at 10:44 am
  
  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 on June 16, 2012 at 6:54 am
    
    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 on June 16, 2012 at 12:56 pm
      
      Hi Prashant,
      I have sent you the Workbook with Progress Bar. Hope that will help.
      Thanks
      Reply
Alain on May 30, 2012 at 7:17 am

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 on May 30, 2012 at 9:21 am
  
  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
Madiha on July 5, 2012 at 7:06 am

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 on July 9, 2012 at 5:34 pm
  
  Kindly send me the error with your workbook to me.
  Reply
Mario on July 25, 2012 at 8:55 am

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 on July 25, 2012 at 6:24 pm
  
  Solution sent to your email id !!
  Reply
Satpal Matharu on October 23, 2012 at 12:04 pm

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 on October 23, 2012 at 12:05 pm
  
  Ctd…
  
  I get a 'Sub or Function not defined' error message.
  Reply
- Vishwamitra Mishra on October 23, 2012 at 12:40 pm
  
  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 on October 23, 2012 at 2:21 pm
    
    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 on October 23, 2012 at 6:03 pm
      
      Yes please !!
      Reply
      - Satpal Matharu on October 24, 2012 at 8:02 am
        
        Hi Vish
        
        Tried sending you the file but got an email failure. It said your inbox was full.
      - Vishwamitra Mishra on October 24, 2012 at 4:13 pm
        
        Send me on vishwamitra02@gmail.com
Irshath on May 6, 2013 at 11:41 am

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
Chris on December 28, 2013 at 12:24 am

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
laxmanan on April 7, 2014 at 9:00 am

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
Ejaz Ahmed on April 30, 2014 at 8:29 pm

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 on May 1, 2014 at 8:00 am
  
  Great Job Ejaz !!
  
  I will definitely try this and provide you the feedback
  Reply
deepika on May 2, 2014 at 1:14 pm

Hi Please could u please provide me the Code to upload test cases from Excel to QC
Reply
deepika on May 2, 2014 at 1:17 pm

Could u please share the Macro code to upload Test cases from Excel to QC
Reply
- Vishwamitra Mishra on May 2, 2014 at 2:32 pm
  
  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
Jose on February 20, 2015 at 12:35 pm

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
Mike on May 20, 2015 at 1:21 pm

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
Stef on September 22, 2016 at 10:35 am

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

Thank’s for help
Reply
Pradeep Kumar on November 29, 2016 at 5:55 am

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 on November 29, 2016 at 8:50 pm
  
  Hi, is this article not helping you in order to get a progress bar?
  Reply