How to Show Progress Bar in Excel / Macro

Objective

Like modern software, while your Macro/code is rendering a time consuming service, a percentage completion status should be shown to the user.

Prerequisites 
The reader/user should have basic idea of Macros, VBA, User Form, Loop (For Next) & Variables to understand this concept.

Let’s do it

1.1.      Preparing “User Form” (See Video)

1.1.1. In VBA Editor, ‘Insert’ ‘User Form’

1.1.2. Add Frame from Toolbar

1.1.3. Add Label within ‘Frame’ from Toolbar

1.1.4. Now Confirm Names and Captions from Properties window

   Name = Identification for VB code / Macro

   Caption = Text to appear

User Form

 Frame

 Label

1.1.5. Set “Modal” Property of “User Form” to FALSE.

   If Modal Property is TRUE, nothing can be done or the macro will not proceed unless the ‘User Form’ is closed.  However, we want our Macro to run in background while the ‘Form’ shows the progress hence the property needed is ‘FALSE’

‘Enabled’ property allows user to close ‘User Form’. In our model we want ‘Form’ to close once macro ends not while it is running, therefore FALSE property is set.

1.1.6. Final Shape of ‘User Form’ would look like

1.2.      Da Code / Macro

Sub mProgressBar()

   

    UserForm1.Show 'Display our designed form

        UserForm1.Frame1.Caption = "% Completed" 'Text on Frame

            UserForm1.Label1.Width = 0 'Progress bar reset / blank

                DoEvents

  

For lProgressReporter = 1 To 100 'Percentage Generator Plus Loops

           

            'Dummy Work

            For lDummyWork = 1 To 5000000

               

            Next lDummyWork

   

                'Progress Bar

                UserForm1.Frame1.Caption = lProgressReporter & "% Completed" 'Display Percentage

                 UserForm1.Label1.Width = lProgressReporter * 2 'Move                           Colored Bar by changing its width

                        DoEvents 'Implement above codes

 Next lProgressReporter 

            

       

   

        UserForm1.Hide 'Unload Form once macro is complete

End Sub

(In above Code small initial letter will represent: m = Macro, l = Loop, v =variable for example mChart is name of Macro)

1.2.1. That’s it (Don't forget to add Actual Work in place of Dummy Work)

Download Sample Workbook

Prerequisites, See Also (These will be added/activated with future posts)