First Class Progress Bar for all your Macros

Posted byEjazPosted inApplicationsTags:, , ,

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.

 

Published by Ejaz

I specialized in actuarial science in my masters’ program and I am determined to become a qualified actuary. I invest extra time and effort into creating intuitive spreadsheet applications that improve productivity at the workplace. I also enjoy spending my leisure time creating digital art; I publish my designs on my art in this blog named Designecdotes. Most of the people who have dramatically influenced my life were enthusiastic academicians. I believe that the greatest gift in life is having a nurturing mentor, and I would like to be that for someone. I owe it to my future mentees to be the best at what I do, and I consciously direct my endeavors at gathering what I consider essential traits of a seasoned mentor.

144 thoughts on “First Class Progress Bar for all your Macros”

  2. 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

  3. 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

  5. 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

  7. 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

  9. 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

  11. Ejaz,

    Thanks for sharing this progress bar…

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

    Like

  12. If you have a total number of actions, and if you can call my macro after each action gets over, you can use it for anything.

    Like

  15. 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

  16. 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

  17. 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

  19. This is a blog on helping people learn how to use VBA. I prefer not to hand you solutions. I aspire to creating learning opportunities.

    Like

  20. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  34. 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

  35. 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

  36. 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

  38. I just want to say Thank you!!!, recall the comment of Steve Bayliss its exactly my situation “l 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.” I just would add that people get anxious to wait, so this tool keep them relax. I used your old version, this is even more impressive with the sub main. Thanks for your effort.

    Like

  39. Hi
    Found it by random and like it.

    However, I have a few comments and suggestions for improvement 😉

    1) Downloading shows version 2.02, but in Macro there is no version but just a date.
    Not a prob but you have to download again to compare the date …

    2) Sometimes you may abort own script before or after initialization of the bar.
    When jumping than to end of own script and trying to close the bar in case of open, you get a problem when trying to execute Terminate, as Bar might have not been shown.
    To handle this, I’ve added a new property “isShown”, which returns me the cFormShowStatus variable.
    With this I can now wrap my complete and terminate methods at the end of the script and it works now for any of my cases.

    3) Sometimes, I do not know how many total actions I need.
    As I can not change the total actions amount after ShowBar execution, I have to handle that in each call of ActionNumber increasement or NextAction execution. Which is annoying …
    To handle this, I’ve added in both a simple logic like if cActionNumber >= cTotalActions, then cActionNumber = cTotalActions
    Due to I never run into problems with ActionNumber overflows.
    Might not be perfect but good enough for dynamic processes with unknown number of steps to be executed.

    4) At the end of the process, before I run Complete method, I have to set the ActionNumber to TotalActions, in case it is lower. As otherwise I get an error.
    Why ….. ?
    I changed it by simply run NextActions in a loop till TotalActions are reached.
    To still keep origin behaviour, I add a third bool param to handle the loop instead of default error handling.

    Might be helpfull for others too

    Liked by 1 person

  40. Thank you very much for your kind readiness in making available your code. It is very grateful to see that the altruist and cooperative spirit have not fled away from the big net.
    Again,
    Thank you very, very much!

    Liked by 1 person

  41. I am glad to see the spirit of being grateful has not disappeared either. Thank you for taking the time to appreciate my work. I hope you’ll share it with your friends and colleagues as well.

    Like

  42. Hi – I only discovered your site by FINALLY reading the comments of the code I’ve taken from Rob Bovey/Stephen Bullen implementation of your progress bar.
    They’ve taken it a bit further and used an Interface Class with the progress bar.
    I realized that using the class did allow me to create multiple progress bars. At first I was doing the same as you – that I had to grab the Main bars top/height to determine the position of the Sub bar. This is fine if the Main and Sub bars are with in the same procedure, but if you have a main procedure that’s calling multiple sub procedures (and those call other procedures) that need progress indicators, that became difficult to do.
    I finally created a collection called ProgressBarStack that held the position of the next progress bar. Basically, every time a progress bar is created, it adds the next position in the collection, when a progress bar is finished, it removes it’s position from the stack (which is always the last one in the collection). So all the positioning is done within the progress bar class.
    Now obviously, if you have too many progress bars running, they could be written off the screen – luckily I haven’t reached that point yet. It could be done by holding each progress bar in a collection and every time a new one is written, move the previous progress bar up the screen a bit. Not sure how much overhead/flashing of the screen would occur doing this though.
    Just a thought!
    Thanks again for this awesome “reusable progress bar object”!

    Like

  43. That is a fine addition! Thank you for extending my work. I feel so guilty that I haven’t been actively publishing recently, but moments like these are inspiring. Let me know if you are interested in sharing your work with my readers.

    Like

  44. Hello Ejaz
    Hope all Fine.. at your end
    Need a small help from you. i want to use your progress bar to run with my Recorded Macro. but dont have any idea how to proceed . i have imported userform and Module from your excel sheet. can u pls guide me how to apply your progress bar to my recorded macro.
    Thanks a Million in adavnce

    Like

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:

Gravatar
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 )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: