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

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!