First Class Progress Bar for all your Macros


I believe I owe you an explanation; about this post’s title. At first, you might think I am a pompous prick, branding my work myself as First Class. This Progress Bar was my first project after I learned how to set up class modules: hence the name. The progress bar that I developed earlier, is one of my most visited posts, and I thought I have to reward my readers with a progress bar that is much easier to use.

What’s new?

In addition to being more user-friendly, I added in a few touches that you might like:

  1. Since I have set this userform up with Properties and Methods, a separate standard code module is no longer required to animate the bar. I have bundled all the code needed to use the Progress Bar into the userform itself.
  2. You can create new instance of class object that allows you to create and control multiple progress bars simultaneously.
  3. I also tweaked the lay out a little; made it more compact. Also, you can make the colour of the bar change gradually from start to end.
  4. I discarded the “Abort” button, but not completely. A few of my readers appreciated the idea of stopping the code when they wanted. Now, you can click the close button on top of the form to terminate execution.

ProgressBar

How to use the Progress Bar in your Spreadsheet applications?

  1. Download the workbook from the Downloads Section below.
  2. Import the User Form into your project. You can just drag it from my workbook and drop it in your project, in the Project Explorer Window.
  3. Declare and Create a new Instance of the ProgressBar Class.
    'Declare the ProgressBar Object
    Dim MyProgressbar As ProgressBar
    'Initialize a New Instance of the Progressbars
    Set MyProgressbar = New ProgressBar
    
  4. Give it a title. Choose if you’d like to change the Excel Status bar also. Set the Start and End Colours.
    With MyProgressbar
        'Set the Title
        .Title = "Test The Progress Bar"
        'Set this to true if you want to update
        'Excel's Status Bar Also
        .ExcelStatusBar = True
        'Set the colour of the bar in the Beginning
        .StartColour = rgbMediumSeaGreen
        'Set the colour of the bar at the end
        .EndColour = rgbGreen
    End With
    
  5. Set the Total number of actions you expect to perform first. Remember that you would not be able to change this later on.
    MyProgressbar.TotalActions = 100
    
  6. After setting the Total number of actions, you can start animating the progress bar using one of the following two methods:
    1. Use the Next Action Method with a status message you would like to show.
      MyProgressbar.NextAction "Performing Action"
      
    2. Or, manually override the Action Number and the Status message.
      With MyProgressbar
          .ActionNumber = 5
          .StatusMessage ="Override Test"
      End With
      
  7. Finally, once the code has finished running, you have three choices:
    1. Execute the Complete Method to let the user know that it is over, and let the user close the form when they feel like it. This you can use on the Main Progress Bar.
      MyProgressbar.Complete
      
    2. Execute the Complete Method with the number of seconds you would like to wait before closing the window automatically.
      MyProgressbar.Complete 5
      
    3. Or, execute the Terminate method and close the Form programmatically. This method you can use on the subordinate progress bars.
      MyProgressbar.Terminate
      

Feel free to rearrange the Form Controls however you please, but remember to not play around with the names though. I have written a couple of cover macros in workbook to illustrate how to use this progress bar. I also have and example that uses two progress bars simultaneously. Let me know if you get stuck somewhere, I will be happy to help.


Downloads

Download

I received a feedback from readers who use multiple displays that the progress bar is not centered to their Excel window. They even gave me code snippets to resolve the problem; thank you all. Here is a updated version that centers the progress bar to Excel’s Active Window.

Download


Further Reading

I was intimidated by the mere thought of learning how to use Class Modules, but not anymore. Chip Pearson’s article on Classes in VBA was my starting point. I urge you to check it out. And I have added plenty of comments in my code; perhaps that will also help you understand how to use classes in your code.


 

Advertisements
Posted In

137 Comments

  1. Thank you Ejaz, I develop office applications and this progress bar is an excellent tool and have used in a couple of apps that have quite lengthy processes that need to run. Impressive work.

    Liked by 1 person

  2. Ejaz,
    Thank you for sharing. I had some problems get it working because I am using another userform and after that calling the progressbar… at the end I found out that I was closing the first userform too early. Nevertheless thumbs up for your great work.

    Like

  3. Ejaz,

    Thanks for sharing this progress bar…

    Will I be able to use this form when excel is exporting some data to word?

    Like

  4. How do i make progress bar for connection update. I have one that show percent in status bar but not know how to add it to the form. Please help
    Sub RefreshAllConnections()
    Dim con As String
    Dim cLen As Long
    Dim Iter As Long
    Dim pInt As Double
    Dim percent As String
    cLen = ActiveWorkbook.Connections.Count

    For Iter = 1 To cLen
    pInt = Iter / cLen
    percent = FormatPercent(pInt, 0)
    With ActiveWorkbook.Connections(Iter)
    Application.StatusBar = “Updating ” & Iter & ” of ” & cLen & ” | ” & percent & ” Complete”
    .Refresh
    End With
    Next Iter

    End Sub

    Like

    1. You have to declare and initialise the progress bar object first. And instead of updating the status bar, you have to call my object’s methods.

      There are lots of notes on how to use my class in the post and on the example macros in the workbook. Give it another read. Write back if you have any questions.

      Like

      1. I am not sure how to do that as i am a beginner . Refresh all connections is general bar that can be used widely. Will you be able to help creating one please.

        Like

  5. Hello Ejaz.

    Can you please give me your email address ?
    I’ll ask about this progress bar in here but script VBA for the imolementation is very long.

    Thank you,
    Haris

    Like

      1. Hi Ejaz,
        I’m a new subscribed and also i’m very newbie with VBA, I have a question :
        Let say i download and follow the procedure to install in my excel 2016 the progress bar in worksheet called ‘MYWorksheet’.
        then i send ‘MYWorksheet’ to a friend, The progress bar is going to work on his computer with excel 2010 ? or
        he have to download follow procedure to install the progress bar on his computer?

        Like

        1. If you’ve successfully imported it into your workbook, it travels with the workbook. It will work on any computer. It should work on Excel 2007 and later.

          My advice for you is, these are the kind of questions you can easily answer yourself by experimenting. Have fun while you learn my friend! All the best!

          Like

        2. Hi Ejaz,
          Thank you for fast response. and btw Yes i know, trying is the best way to learn but my comment/question was set as example.

          Thank you

          Best regards

          Like

  6. Hello Ejaz, Great tutorial. BTW i have a bunch macros. I am running all macros using call function. How to use is tutorial for my requirement. i.e without loop.

    Like

  7. This is very cool … but is it me? Or shouldn’t there be (if only by virtue of the project’s name and intent) an actual Class Module contained in the project? There are only two general VBA Modules.

    Like

    1. I see what you mean. In the interest of making it compact and easy to install, I used the form itself as a class.

      I got feedback from other readers that a form’s built-in properties and methods confuse a new user, when Intellisense is turned on.

      Like

    2. I don’t think you’re a “pompous prick”, Contrary i think you’re a humble person that try to share your knowledge and help lot people….god bless you my friend.

      Regards
      Andy

      Liked by 1 person

  8. Is it possible to have my counter equal a variable that is in my coding. For instance I have a variable z which goes through 3 loops because each loop is a different product (1 – 200, than 201 to 400 and than 401-600). I want my counter to change everytime z changes. It is kind of working but it is changing the outcome of my actual data. My code looks like this:

    Sub Run()
    Application.ScreenUpdating = False
    DeleteContents

    Dim z As Integer
    Dim Counter As Long
    Dim TotalCount As Long
    Dim SomeRange As Range
    Dim EachCell As Range

    TotalCount = 600
    Set SomeRange = Sheet1.UsedRange
    SomeRange.Clear

    Dim MyProgressbar As ProgressBar
    Set MyProgressbar = New ProgressBar

    With MyProgressbar
    .Title = “Test The Bar”
    .ExcelStatusBar = False
    .StartColour = rgbMediumSeaGreen
    .EndColour = rgbGreen
    .TotalActions = TotalCount
    End With

    Debug.Print MyProgressbar.Title
    Debug.Print MyProgressbar.TotalActions
    Debug.Print MyProgressbar.ActionNumber
    Debug.Print MyProgressbar.StatusMessage

    MyProgressbar.ShowBar
    Counter = z

    For z = 1 To 200

    ‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
    DoEvents
    End If
    MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
    Next z

    For z = 201 To 400
    ‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
    DoEvents
    End If
    MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
    Next z

    For z = 401 To 600
    ‘code deleted for confidentiality, involves copy and pasting between sheets if data includes certain criteria such as cell a4 = Male
    DoEvents
    End If
    MyProgressbar.NextAction “Struggling To Excel ” & Counter, True
    Next z

    MyProgressbar.Complete 3
    SomeRange.Clear

    Application.StatusBar = False
    Application.ScreenUpdating = True
    End Sub

    Like

    1. You can set it to a variable. It’s just that you can’t change it after setting it once.

      You have to determine the total number of actions in advance.

      Like

  9. I tried to download Class Progress Bar V2.02.xlsm, I get the following error

    “Error (429)
    This link is temporarily disabled. The person who shared it hit their daily limit of traffic or downloads. Learn about traffic ”

    can you please help? 🙂

    Liked by 1 person

  10. Work it out Chema…. it’s worth the effort…

    On 30 October 2016 at 16:18, Struggling To Excel wrote:

    > Ejaz commented: “Did you try looking at the cover macros in the Workbook?” >

    Liked by 1 person

  11. I can confirm too that upgrading to Office 365 2016 caused the progress bar color fill to stop working. It worked fine before the upgrade (Windows 10 + Office 365 2013). Nevertheless thank you for such a useful tool, Ejaz!

    Like

  12. Updating to versions 16.0.7465.1015 (Excel 2016), 1610 build 7466.2038 (Excel 2013) and 14.0.7165.5000 (Excel 2010) fixed a lot of visual bugs in Office. Works fine for me now.

    Liked by 1 person

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s