VBA Progress Bar

Progress Bar shows us how much of a process has been done or finished. For example, when we run large sets of codes that require a larger execution time, we use the progress bar in VBA to show the user the status of the process. Or, if we have multiple processes running in a single code, we use the progress bar to show which process has progressed and how much.

A progress bar shows the percentage of tasks completed when the actual task is running behind the screen with a set of instructions given by the code.

When the VBA code takes a considerable amount of time to execute, it is the user’s anxiety to know how soon it can finish. By default, we need to wait for the full time to complete the task, but by inserting the progress bar, we get to know the progress of the VBA code.

In almost all computer software, we see a progress bar chart that shows the progress of the task we are performing, just like the below image.

In Excel, we can create a progress bar by using VBA coding. If you have ever wondered how we can create a progress bar, this is the tailor-made article for you.

To create a progress bar, we need to follow several steps. Below are the steps to involve while creating the progress bar chart.

Step 1: Create or Insert a new UserForm.

As soon as you click on the option above, you will see a UserForm like the one below.

Step 2: Press the F4 key to see the VBA Properties window.

In this Properties tab, we need to change the properties of the VBA Userform we have inserted.

Step 3: Change the name of the UserForm to UFProgressBar.

Now, we can refer to this UserForm with the name “UFProgressBar” while coding.

Step 4: Change the "Show Modal" property of the UserForm to "FALSE."

Step 5: Now, adjust the user's alignment to fit your needs. We have changed the height of the UserForm to 120 and the width to 300.

Step 6: Change the "Caption" of the UserForm to "Progress Bar."

Step 7: Now, from the toolbox of the UserForm, insert LABEL to the UserForm at the top.

With this label, we need to set the properties of the label. First, delete the caption, make it blank, and adjust the width of the label.

Step 8: Change the "Name" of the label to "ProgessLabel."

Step 9: Take the frame from the toolbox and draw just below the label we inserted in the previous steps. Make sure the frame is at the center of the UserForm.

Step 10: We need to change some of the frame's properties to make it look the same as the UserForm we have inserted.

Property 1: Change the "Name" of the frame to "ProgressFrame."

Property 2: Delete the "Caption" and make it blank.

Property 3: Change the "SpecialEffect" of the frame to 6 – fmSpecialEffectBump.

After all these changes, our UserForm should look like this.

Step 11: Now, insert one more label. This time insert the label just inside the frame we have inserted.

While inserting the label, ensure the left side of the label exactly fits the frame we have inserted, as shown in the above image.

Step 12: After inserting the label, change the label's properties as follows.

Property 1: Change the "Name" of the label to "MainProgressLabel."

Property 2: Delete the "Caption."

Property 3: Change the background color as per your wish

Now, we have completed setting up the progress bar. At this point, it looks like this.

Now, we need to enter codes to make this work perfectly.

Step 13: To make the framework add the below macro in excel.

Code:

Sub InitUFProgressBarBar()

With UFProgressBar 
.Bar.Width = 0 
.Text.Caption = "0%" 
.Show vbModeless

End With

Note: “UFProgressBar” is the name given to the UserForm, "Bar" is the name given to the frame we have created, and "Text" is the name given to the label inside the frame.

Now, if you run this code manually or through the F5 key, we should see the progress bar like this.

Step 14: We need to create a Macro to perform our task. We are performing the task of inserting serial numbers from 1 to 5,000. We also need to configure the progress bar chart along with this code. The code is tailor-made code for you.

Code:

Sub ProgressBar_Chart()

Dim i As Long 
Dim CurrentUFProgressBar As Double 
Dim UFProgressBarPercentage As Double 
Dim BarWidth As Long

i = 1

Call InitUFProgressBarBar

Do While i <= 5500

Cells(i, 1).Value = i

CurrentUFProgressBar = i / 2500 
BarWidth = UFProgressBar.Border.Width * CurrentUFProgressBar 
UFProgressBarPercentage = Round(CurrentUFProgressBar * 100, 0)

UFProgressBar.Bar.Width = BarWidth UFProgressBar.Text.Caption = UFProgressBarPercentage & "% Complete"

DoEvents

i = i + 1

Loop

Unload 

UFProgressBar

End Sub