Starting to code in VBA always wanted to see that loading rectangle as a progress bar to watch the working macro, for instance like in every software or game installation. Not only for fancy issue, but mainly to know in which point of macro I am.
So, what do we need to have a progress bar?
First of all, we need to create a form. It should have at least 1 textbox or label and the main thing – frame and label inside that frame.
My progress bar (userform) is called Progression. I got here textbox Text with 0% Completed Caption, textbox Text2 with Please wait Caption and label Label1 with 0 / 0 Caption.
It is up to You about the width and height of your frame and label inside label. For example my frame Frame1 has 204 Width and 24 Height and the label Bar – 20 Height and should have 0 Width in the beginning (always starting from 0% 🙂 )
Now time for the code in the module for our bar.
Code is taking 2 values (i – number of iteration & lLast – last number) and calculating what is the percentage of the completion (pctCompl).
pctCompl = Round((i / lLast) * 100, 2)
After that it updates label caption, text caption and width of the Bar according to the names from paragraph above.
Progression.Label1.Caption = i & " / " & lLast Progression.Text.Caption = pctCompl & "% Completed" Progression.Bar.Width = pctCompl * 2
In the end to refresh our form I use:
Code for our progress bar:
Sub progress(i As Long, lLast As Long) Dim pctCompl As Long pctCompl = Round((i / lLast) * 100, 2) Progression.Label1.Caption = i & " / " & lLast Progression.Text.Caption = pctCompl & "% Completed" Progression.Bar.Width = pctCompl * 2 DoEvents 'update the userform End Sub
To watch your macro with progress bar You need some code to test it! I wrote a simple loop to show You how to use that progress sub from above.
You need to remember about calling your progress bar form at the beginning and unloading in the end. In our case:
Progression.Show 'code Unload Progression
Finally, to update our Bar we need to call progress sub every time we want to refresh the bar, so in my case in the end of every iteration.
Option Explicit Sub main() Dim clock As Double Dim i As Long Dim colA As Range, cel As Range clock = Timer Progression.Show With ThisWorkbook.Sheets(1) For i = 1 To 10000 .Cells(i, 1) = 1 Call progress(i, 10000) Next Debug.Print Round(Timer - clock, 3) End With Unload Progression End Sub
I hope my progress bar will allow You to watch the progress of your macro.
If You got any questions or suggestions feel free to comment 🙂
5 thoughts on “Progress bar – simple thing to watch your Excel macro”
Thanks a lot. Can you upload workbook with the userform as I couldn’t apply the steps well?
Thank you very much. I have been able to apply it now. The problem was in the design of the controls on the userform itself. Awesome tutorials.