Introduction | Design Progress Bar | Add together Progress Bar Macros | Brandish Progress Bar | Final Thoughts


Introduction

Make your macros stand out by creating this cute VBA Progress Bar to illustrate the progress. This tutorial walks you through how to make the sleek UserForm and how to implement it in your next project!

Inclusion of a VBA progress bar is an incredibly useful feature when you accept long macros that take several minutes to complete. Without a periodic status update, your users volition wonder if the macro is however running. Follow this step-by-step guide to learn how to comprise a progress bar into your own spreadsheet.

To go you motivated about what's in store, I'm going to go ahead and testify yous the final design of my VBA progress bar:

VBA Progress Bar


Design Progress Bar

Adding your UserForm

The first thing we want to do is create a UserForm and add the elements necessary to plow it into a progress bar. If yous're new to VBA, follow these steps to add a UserForm:

  1. Open up the VBA Editor (Alt+F11)
  2. Insert a UserForm
    1. Right-click the Project Explorer Pane
    2. Hover over Insert
    3. Click Userform

      Insert UserForm

Setting the Progress Bar Properties

Once you accept an empty UserForm, go ahead and press F4 to bring upward the Properties Window.

  1. Change the (Name) of the UserForm to ufProgress.
  2. Change the ShowModal property to Fake. Setting this property to Faux guarantees other macros will continue to run while the UserForm is open. Check out this tutorial to learn more about the ShowModal holding.
  3. Adjust the size of your UserForm to something visually pleasing for you. I like to set the Top property of my Progress Bars to 110 and the Width belongings to 240.
  4. If you'd like, you tin can go ahead and update the Caption holding of the UserForm to something like Running.... This step isn't necessary if y'all want to hibernate the title bar altogether. I'll show you how to practise that later in this tutorial!

Hither'south what my Progress Bar looks like afterward completing these steps:

VBA Progress Bar I

Designing the UserForm

At present that you have the UserForm configured the fashion we desire information technology, yous tin can brainstorm designing it. There are a few elements we must have for the VBA progress bar to role properly. Follow these steps to brand sure each element is included:

Insert Label

  1. Insert a characterization in the upper left of your UserForm. This will be used later to display the text indicating the status of your macro.
  2. Make sure the label is wide plenty for any text you may brandish later. I fix my Width belongings to 174, simply there'due south nothing magical about that number.
  3. Change the (Name) of the label to LabelCaption.
  4. Alter the Explanation property to an empty cord (no text).

At this point, your Progress Bar should wait something like this:

VBA Progress Bar Insert Label


Insert Frame

The adjacent stride is to insert a Frame on your UserForm. The frame is the item highlighted in the Toolbox screenshot below:

Insert Frame

Yous want to insert the frame then it's roughly centered on your UserForm, both horizontally and vertically, with a trivial margin on each side. It should exist positioned below the label you previously added. At this point, your UserForm will expect something similar this:

Insert Frame

To make the frame brainstorm to resemble a progress bar, you'll need to change a few properties. Follow these steps to configure the frame properties:

  1. Select the frame and change the (Name) property to FrameProgress
  2. Change the Caption property to an empty cord (no text)
  3. Change the SpecialEffect belongings to 2 - fmSpecialEffectSunken

At this point, y'all tin start to run into the outline of your progress bar. The progress bar is finally starting to have shape and volition await something similar this:

VBA Progress Bar Insert Frame


Insert Another Characterization

Next, nosotros want to insert another characterization, but this characterization is never going to have any text. This label is going to grow equally your macro progresses to make full the frame you just created. Stay with me for a infinitesimal to learn what I mean.

  1. Insert a label Within the frame y'all just created, and change the height so information technology fits nicely right up against the elevation and lesser of your frame.
  2. Position the label so the left edge of the label touches the left edge of the frame. The width of the label doesn't thing at this point. Information technology will look similar this:
    Insert 2nd Label
  3. Change the (Proper name) property to LabelProgress
  4. Change the Caption property to an empty cord (no text)
  5. Alter the BackColor property to blue, or whatever color you prefer.
  6. Change the SpecialEffect belongings to 1 -fmSpecialEffectRaised.

Yous are done designing your VBA Progress Bar! The UserForm volition look something like this:

Insert 2nd Label

Once yous get to this bespeak, you're fix to add the macros to your Progress Bar so it behaves the way we desire information technology to.


Add together Progress Bar Macros

When your progress bar pops up, yous don't desire the ugly title bar with the red X to show up, correct? I didn't think so.

Hide Title Bar

To remove the title bar, I'1000 going to borrow a macro from my Remove Window Edge tutorial. If the person using the spreadsheet you're creating is a Mac user, this macro won't piece of work, but I'll show you lot how to prevent it from crashing on them later.

  1. Insert a Standard Module
    1. Right-click the Project Explorer Pane
    2. Hover over Insert
    3. Click Module

      Insert Module

  2. Change the (Proper noun) property of the Module to HideTitleBar
  3. Paste the following macro into the new module you just created:
                          'Place IN STANDARD MODULE              Selection              Explicit              Option              Private              Module              Public              Const              GWL_STYLE              =              -              16              Public              Const              WS_CAPTION              =              &              HC00000              #If VBA7 And then              Public              Declare              PtrSafe              Part              GetWindowLong              _              Lib              "user32"              Allonym              "GetWindowLongA"              (              _              ByVal              hWnd              As              Long              ,              _              ByVal              nIndex              As              Long              )              Equally              Long              Public              Declare              PtrSafe              Function              SetWindowLong              _              Lib              "user32"              Alias              "SetWindowLongA"              (              _              ByVal              hWnd              As              Long              ,              _              ByVal              nIndex              As              Long              ,              _              ByVal              dwNewLong              Every bit              Long              )              As              Long              Public              Declare              PtrSafe              Part              DrawMenuBar              _              Lib              "user32"              (              _              ByVal              hWnd              Equally              Long              )              As              Long              Public              Declare              PtrSafe              Function              FindWindowA              _              Lib              "user32"              (              ByVal              lpClassName              As              String              ,              _              ByVal              lpWindowName              Every bit              String              )              As              Long              #              Else              Public              Declare              Function              GetWindowLong              _              Lib              "user32"              Alias              "GetWindowLongA"              (              _              ByVal              hWnd              As              Long              ,              _              ByVal              nIndex              As              Long              )              Equally              Long              Public              Declare              Function              SetWindowLong              _              Lib              "user32"              Allonym              "SetWindowLongA"              (              _              ByVal              hWnd              Equally              Long              ,              _              ByVal              nIndex              Every bit              Long              ,              _              ByVal              dwNewLong              Equally              Long              )              Equally              Long              Public              Declare              Part              DrawMenuBar              _              Lib              "user32"              (              _              ByVal              hWnd              As              Long              )              As              Long              Public              Declare              Function              FindWindowA              _              Lib              "user32"              (              ByVal              lpClassName              As              String              ,              _              ByVal              lpWindowName              As              String              )              As              Long              #End If              Sub              HideTitleBar              (              frm              Every bit              Object              )              Dim              lngWindow              Equally              Long              Dim              lFrmHdl              As              Long              lFrmHdl              =              FindWindowA              (              vbNullString              ,              frm              .              Caption              )              lngWindow              =              GetWindowLong              (              lFrmHdl              ,              GWL_STYLE              )              lngWindow              =              lngWindow              And              (              Not              WS_CAPTION              )              Call              SetWindowLong              (              lFrmHdl              ,              GWL_STYLE              ,              lngWindow              )              Call              DrawMenuBar              (              lFrmHdl              )              End              Sub                      

Make powerful macros with our free VBA Developer Kit

Tutorials similar this can be complicated. That's why we created our costless VBA Developer Kit and our Big Volume of Excel VBA Macros to supplement this tutorial. Grab them below and you lot'll be writing powerful macros in no time.


Add Macro to UserForm

Once you do this, get dorsum to your ufProgress form and follow these steps:

  1. Right click anywhere on your ufProgress form and select View Code
  2. Clear out any macros that may appear on the form
  3. Paste the post-obit macro:
                          'PLACE IN YOUR USERFORM Lawmaking              Private              Sub              UserForm_Initialize              ()              #If IsMac = False So              'hide the title bar if you're working on a windows machine. Otherwise, simply display information technology as you lot normally would              Me              .              Height              =              Me              .              Superlative              -              10              HideTitleBar              .              HideTitleBar              Me              #Cease If              Finish              Sub                      

The IsMac check in this routine is what prevents the macro from crashing for users running Excel on a Mac. Instead of the sleek UserForm without a title bar, they'll see the title bar. In other words, their Progress Bars won't be as pretty as you lot Windows users, but that'south okay, right!? ;)


Display Progress Bar

At present that you're done designing your progress bar and configuring the macros, information technology'south time to effigy out how to incorporate the progress bar into your existing macro.

This is where you may take to adapt the solution I provide into your ain awarding. This might require you to remember outside the box a bit, only I'm confident you tin can do that!

The all-time way to show you how to include the progress bar in your macro is to give you a demonstration and and so walk you through the of import pieces.

In this example, I loop through all the rows in my spreadsheet and update my progress bar after each row. Here's the sample macro (pay attention to the comment cards):

                          'PLACE IN A STANDARD MODULE              Sub              LoopThroughRows              ()              Dim              i              As              Long              ,              lastrow              As              Long              Dim              pctdone              As              Single              lastrow              =              Range              (              "A"              &              Rows              .              Count              ).              End              (              xlUp              ).              Row              '(Footstep 1) Brandish your Progress Bar              ufProgress              .              LabelProgress              .              Width              =              0              ufProgress              .              Show              For              i              =              one              To              lastrow              '(Step 2) Periodically update progress bar              pctdone              =              i              /              lastrow              With              ufProgress              .              LabelCaption              .              Caption              =              "Processing Row "              &              i              &              " of "              &              lastrow              .              LabelProgress              .              Width              =              pctdone              *              (.              FrameProgress              .              Width              )              End              With              DoEvents              '--------------------------------------              'the residual of your macro goes below here              '              '              '--------------------------------------              '(Stride 3) Close the progress bar when you lot're done              If              i              =              lastrow              So              Unload              ufProgress              Side by side              i              End              Sub                      

This sample has three of import steps. These 3 steps are common whether or not your macro uses a For loop similar the ane above.


Step 1 - Display your Progress Bar

At some point in your macro, you want to make your progress bar appear on your screen. Chances are, you want to exercise this right after your macro starts to run. To do that, y'all want to make sure the width of your progress bar is set to 0 at the beggining and then bear witness your UserForm. All you lot have to practise is add the following lines to your macro, like I did in the example in a higher place:

                          ufProgress              .              LabelProgress              .              Width              =              0              ufProgress              .              Bear witness                      

Step two - Periodically Update Progress Bar

At some indicate, you desire to update the length of your progress bar to indicate how far along your macro is. That's the point of a progress bar, right? In my example, I used a formula to calculate what percentange of the way through the macro I was, based on which row I was processing. Inside the loop, I included the following code:

                          pctdone              =              i              /              lastrow              With              ufProgress              .              LabelCaption              .              Caption              =              "Processing Row "              &              i              &              " of "              &              lastrow              .              LabelProgress              .              Width              =              pctdone              *              (.              FrameProgress              .              Width              )              Finish              With              DoEvents                      

Each time I get to a new row, I update the LabelCaption text and the width of my LabelProgress label.

This is a great manner to signal progress if your macro consists of a loop where y'all know the starting position and yous know the ending position. Examples where a solution like the one in a higher place volition work are when yous're processing each line of data in a spreadsheet, or looping through files in a binder.


Step 3 - Close the Progress Bar

When your macro is nearing completion, make sure you close your Progress Bar. In my example, I closed the VBA Progress Bar UserForm when I got to the concluding row. I did that via this line of lawmaking:

                          If              i              =              lastrow              Then              Unload              ufProgress                      

Final VBA Progress Bar

When you put it all together, you'll get a cute VBA progress bar that looks like this:

VBA Progress Bar

What if I don't have a loop?

The approach I demonstrated in the previous section is great for loops, but information technology's not very useful if your macro isn't constructed with a primary loop. What if y'all don't have a loop?

The important part is, you need to follow the same 3 steps.

You don't demand a formula to do this. You could manually update the progress bar at various points in your macro. Take a wait at this sit-in, where I include the aforementioned 3 steps but they're manually sprinkled into my existing macro.:

                          'PLACE IN STANDARD MODULE              Sub              DemoMacro              ()              '(Step one) Display your Progress Bar              ufProgress              .              LabelProgress              .              Width              =              0              ufProgress              .              Testify              FractionComplete              (              0              )              '(Step 2)              '--------------------------------------              'A portion of your macro goes here              '--------------------------------------              FractionComplete              (              0.25              )              '(Step two)              '--------------------------------------              'A portion of your macro goes hither              '--------------------------------------              FractionComplete              (              0.5              )              '(Step ii)              '--------------------------------------              'A portion of your macro goes hither              '--------------------------------------              FractionComplete              (              0.75              )              '(Step 2)              '--------------------------------------              'A portion of your macro goes here              '--------------------------------------              FractionComplete              (              i              )              '(Step ii)              '(Step 3) Close the progress bar when you're done              Unload              ufProgress              Terminate              Sub              Sub              FractionComplete              (              pctdone              Every bit              Single              )              With              ufProgress              .              LabelCaption              .              Caption              =              pctdone              *              100              &              "% Consummate"              .              LabelProgress              .              Width              =              pctdone              *              (.              FrameProgress              .              Width              )              End              With              DoEvents              Cease              Sub                      

In this case, I spaced out my macro so I could provide a status update at guess 1/4 intervals. How you separate your macro isn't important. Equally long equally you're periodically updating your LabelCaption text and your LabelProgress width, your users will be happy.

What if my progress bar isn't updating?

If yous accept a complicated macro that uses a bunch of resource, you may discover that your progress bar won't update or it'll show up white. To fix the progress bar, add the following code right below the Cease With statement in Step 2 of the progress bar.

This short piece of lawmaking forces VBA to redraw your progress bar so it's updated each time the UserForm changes.


Final Thoughts

Y'all have successfully created an incredible progress bar using VBA that your users will surely be thankful for! Go creative and alter your explanation text to specifically friction match what your macro is doing at any given point if you'd like. When I need to mass produce PDF invoices, I've included a progress bar similar this one to keep me updated on the condition. Y'all tin can do the same!

That's all for this tutorial. When you're prepare to take your VBA to the next level, subscribe using the form below.