Creating a Custom VBA Progress Bar in Excel
Macros are among the best tools in Excel that allow us to save time.
Tasks that are repetitive, monotonous, and sometimes downright boring can be automated with VBA macros. This has the potential to reduce, in some cases, hours’ worth of work to minutes or seconds of work.
But not all macros can deliver on such performance claims. Sometimes the data is just too voluminous, and the computer can only operate so quickly. In extreme cases, things may move so slowly, we think the system might have locked or crashed.
Thus, the progress bar was invented.
In the early days of Windows, machines were understood to be slow and prone to crashes. By providing the user with a visual indicator of progress, we knew the system was still working and could make a reasonable guess as to when the task at hand would be completed.
VBA macros are tiny computer programs created by Users for Users. In those cases where macro execution may take a fair amount of time, a nice touch would be to provide users with a progress bar.
This post will demonstrate two variations on the same theme. One of these may serve as a complete solution or at a minimum inspire you to create your progress bar.
The primary ingredients to making this work are:
The complete code and the downloadable file can be found at the end of this post. The following sections provide high-level explanations of each major facet of code. For a more in-depth explanation of each facet, consider enrolling in the complete “Unlock Excel VBA & Excel Macros” course at XelPlus.
What we are automating can be anything our Excel minds can imagine. The great thing about this progress bar tutorial is that it can be inserted into almost any existing or future macro.
In this demonstration, our automation process will “walk” through records in a table, pausing for 1/10th of a second at every record.
In reality, we would likely be performing much more sophisticated operations on each record.
Part 2: The Visual Interface
The scroll bar window is created using a VBA UserForm.
- In the Visual Basic Editor, insert the UserForm (Insert – Userform).
This presents us with a blank, generic UserForm which acts as a canvas for our imagination’s unlimited creativity.
NOTE: If you are missing the Toolbox controls, select View -> Toolbox.
- Rename the UserForm by selecting (Name) in the Properties We will name our UserForm “UserForm_v1”.
- Set the UserForm Caption (what is displayed in the Title Bar area) to read “Create PDF Documentation”. This, of course, can be anything you wish it to display.
- Using the ToolBox Label feature, insert a label and set the Caption to read “Your Assistant at Work…”
- Using the Toolbox Image feature, insert an image placeholder. Set the Picture property to the location of your image. The ellipse button to the right of the property will allow you to browse out to your image file.
- Using the Toolbox Frame feature, insert a frame object. This will serve as a border for the scroll bar as well as display a numeric percent counter. Set the Caption to read “0%”. This will be changed to read a progressive percentage during code execution.
- Within the boundary of the Frame, insert another Label This label will not contain text, rather it will act as the scroll bar. This is performed by coloring the interior of the label and resizing it incrementally, making it larger and larger as the macro progresses. Label Properties you may wish to experiment with are:
- BackColor – &H00C00000& (Blue)
- BackStyle – 1-fmBackStyleOpaque
- BorderColor – &H80000006& (Gray)
- Height – 30
- SpecialEffect – 1-fmSpecialEffectRaised
- Width – 18
Part 3: The Code That Drives the Visual
Double-clicking on any blank area of the UserForm will open the code window for the UserForm.
Some of the major features of the code are:
Turning off screen updating (to reduce screen flicker) and suppressing alerts (if encountered.)
Check to ensure there is at least one record in the table being processed
Looping through the table rows
A few notes about the above snip of code:
- The loop will execute ‘N’ number of times as there are rows in the table.
- The PCT = calculates an ever-increasing percentage. This begins at 1/N and ends with N/N. (i.e. 1% to 100%). The more records in the table, the more granular the percentage calculation.
- The timer will count to 1/10th of a second, creating a very small pause effect. This keeps the progress bar from moving too fast for this demonstration. In the real world, you would want to omit this self-induced “pause” as it hampers performance.
- The Call UpdateProgress(Pct) line passes the calculated percentage (Pct) to the UpdateProgress This percentage will be displayed in the Caption of the Frame object.
Removing the form from the screen when finished
Part 4: Launching the UserForm
To display the UserForm when the user clicks the macro launch button, we create a traditional Module sheet (Insert -> Module) and load the UserForm into memory.
To ensure the UserForm for the progress bar appears in the center of the screen, we calculate the center position with some crafty math. After the calculations are complete, we Show the memory-loaded UserForm.
Part 7: Making the Scroll Bar “Stretch”
This is where the magic happens.
Remember that part earlier where we made a call to another macro named “UpdateProgress”? We passed that macro a value stored in a variable named Pct.
The value in Pct serves two purposes:
- The value of Pct is displayed in the Caption of the Frame
- Pct is used to calculate the Width property of the label object.
- The .Repaint instruction forces the label object to be visually refreshed based on the newly calculated Width
By redrawing the label object at an ever-increasing larger width, we achieve the illusion that the label object is growing. Ingenious, no?
The “DoEvents” instruction allows VBA to detect user-interaction via the keyboard. This is helpful during long-duration macros where the user may wish to break out of a loop prematurely.
Part 8: Assigning the Macro to the Launch Button
You can launch a macro variety of objects: buttons, images, icons, shapes, etc.
We will add an Excel icon image and assign the macro to the image. This is done by right-clicking on the image and selecting “Assign Macro”.
Next, in the Assign Macro dialog box, select the macro located on the traditional module sheet that loads and shows the UserForm. In our case, it was named “GetMyForm_v1”.
This second version of the progress bar was designed by my friend Bryon Smedley. I love it! It’s really creative.
It takes the opposite approach to displaying progress. Instead of the Label “grow”, we will have the Label “shrink”.
The trick here is that our Label will not be the indicator of progress. Instead, we have a static image indicating progress and the Label will act as a mask that hides part of the static graphic.
By coloring the Label the same as the background and placing the Label in front of the Image, we can reveal portions of the Image as we reduce the size of the Label.
When we “shrink” the Label it will give us the illusion of “growing” the Image.
For the most part, the code is the same. The main differences lie in the scroll bar/mask and the percentage display.
Instead of inserting a Frame object and changing the Caption property, we will add a Text object and change the Caption property.
The gray background is an inserted Image object that pointed to a simple image of a gray, bordered shape.
Progress Bar (Static Image)
The green “Excel” progress bar is a static image of a green rectangle with the Excel logo repeated four times.
Progress Bar (“Shrinking” Mask)
The Label object that “shrinks” has two main differences in operation compared to the first example.
- The Width property is calculated by multiplying Pct by 218 (the maximum width) and deducting that from 218. Ex: if Pct is .5, then the Width is 109; half of the original 218.
- Instead of fixing the Left property to a set position, the left side of the Label will be calculated. The logic is to deduct the calculated Width from 230 (the far-right side of the Label). Ex: if Pct is .5, the calculated Width is 109, then the Left property is calculated at 121.
NOTE: These results represent the number of pixels. 109 represents the number of pixels wide. 121 represents 121 pixels from the left edge of the UserForm. Depending on your UserForm size, you may need to experiment with these values. A bit of experimentation may be required to achieve the perfect look.
Be Careful of the Stacking Order (Layers)
Make certain that the Label object is in front of the Image object. This can be accomplished by right-clicking on an object and selecting either “Bring Forward” or “Send Backward”.
Feel free to Download the Workbook HERE.
Full Code for Version 1
Sub GetMyForm_v1() Load UserForm_v1 With UserForm_v1 .StartUpPosition = 0 .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width) .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height) .Show End With End Sub
Procedure “UserForm Activate” (v1)
Private Sub UserForm_Activate() Dim startrow As Integer Dim endrow As Integer Dim i As Integer Dim myScrollTest As Object Set mainbook = ThisWorkbook Application.ScreenUpdating = False Application.DisplayAlerts = False Set myScrollTest = Worksheets("ScrollTest_v1") mylabel = Worksheets("ScrollTest_v1").Range("A2").Value With myScrollTest 'where to start startrow = .Range("A1").Row + 1 'where to end endrow = .Range("A1").End(xlDown).Row If .Range("A2").Value = "" Then MsgBox "Please paste your entity codes starting from Row 2" Exit Sub End If End With 'start the loop For i = startrow To endrow Pct = (i - startrow + 1) / (endrow - startrow + 1) Call UpdateProgress(Pct) ' This is where your workbook does many things that take a bit of time startTime = Timer ' Capture the current time Do Loop Until Timer - startTime >= 0.1 ' Advance after 1/10th of a second ' This is where your workbook has finished an itteration of work Next i Unload UserForm_v1 myScrollTest.Select MsgBox "Report generation is complete" & vbLf & vbLf _ & "Please retrieve your report from the printer", vbInformation Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Procedure “UpdateProgress” v1
Sub UpdateProgress(Pct) With UserForm_v1 .FrameProgress.Caption = Format(Pct, "0%") .LabelProgress.Width = Pct * (.FrameProgress.Width - 10) .Repaint End With DoEvents End Sub
Full Code for Version 2
Sub GetMyForm_v2() Load UserForm_v2 With UserForm_v2 .StartUpPosition = 0 .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width) .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height) .Show End With End Sub
Procedure “UserForm Activate” (v2)
Private Sub UserForm_Activate() Dim startrow As Integer Dim endrow As Integer Dim i As Integer Dim myScrollTest As Object Set mainbook = ThisWorkbook Application.ScreenUpdating = False Application.DisplayAlerts = False Set myScrollTest = Worksheets("ScrollTest_v2") mylabel = Worksheets("ScrollTest_v2").Range("A2").Value With myScrollTest 'where to start startrow = .Range("A1").Row + 1 'where to end endrow = .Range("A1").End(xlDown).Row If .Range("A2").Value = "" Then MsgBox "Please paste your entity codes starting from Row 2" Exit Sub End If End With 'start the loop For i = startrow To endrow Pct = (i - startrow + 1) / (endrow - startrow + 1) Call UpdateProgress(Pct) ' This is where your workbook does many things that take a bit of time startTime = Timer ' Capture the current time Do Loop Until Timer - startTime >= 0.1 ' Advance after 1/10th of a second ' This is where your workbook has finished an itteration of work Next i Unload UserForm_v2 myScrollTest.Select MsgBox "Report generation is complete" & vbLf & vbLf & "Please retrieve your report from the printer", vbInformation Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Procedure “UpdateProgress” v2
Sub UpdateProgress(Pct) With UserForm_v2 .Complete.Caption = Format(Pct, "0%") ' Percentage displayed to user as numeric .LabelProgress.Width = 218 - Pct * 218 ' Shortens the mask .LabelProgress.Left = 218 - .LabelProgress.Width + 12 ' Repositions the mask .Repaint End With DoEvents End Sub
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW