Progress bar – simple thing to watch your Excel macro

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:

DoEvents

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.

Example Code

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 🙂

Next: https://simpleexcelvba.com/progress-bar-refresh-macro-performance/