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.


 

Categories: ApplicationsTags: , , ,

143 comments

  1. Very cool progress bar! I really need to start using them as they make code execution look a lot more professional. Can definitely relate with your (old) feelings toward class modules. I still haven’t fully embraced them but I’ve be able to hash out a few cool things with their vast capabilities.

    Liked by 1 person

  2. Thanks Chris. Class modules are so awesome.

    Like

  3. I wrote one for Access this spring. That was before I realized you could use userforms in Access. I digress though, I mention it because I have a way to run it in a loop, waiting for a query to run. I thought you may be interested. http://christopherjmcclellan.wordpress.com/2014/03/08/progress-bar-for-ms-access/

    Also, I was looking at your code, and I really think that you should take a look at Enums for your error codes. It will help you clean up all of those magic numbers. Swing by Code Review sometime and we’ll help you clean it up a bit. http://codereview.stackexchange.com/

    Liked by 1 person

  4. Hi there:)
    This seems nice, but it I can’t make it work. Is it right that the &qout; should be replaced with quotation marks(“)?

    Every time I try to run it I get: Run-time error ‘5’ Run the Show Method First

    It seems like there is a problem with line 2 in point 6 “.ActionNuber = 5”

    Do you know what I have done wrong?

    Liked by 1 person

  5. You are absolutely right. Remove they have to be double quotes. WordPress editor screwed up again; thanks for letting me know; I will get that taken care of.

    I suggest you take a look at the cover macros I have written in the standard code module in the file attached to the download link.

    Like

  6. Thanks for the invitation. I will check out the Code Review thing. And I will check out the Access Progress bar and let you know what I think.

    Like

  7. Hi Ejaz. Well done! It looks good. I am still struggling to find uses for Classes. I know they are powerful, but it is finding something that is worth creating them for.

    Regards
    Neale

    Liked by 1 person

  8. This project right here is based on what classes can do. The first use I find interesting is, we can create properties and methods and associate some code to it.

    For instance, in this scenario, changing the ActionNumber property of the progress bar animates it.

    Other uses are, you can create a lot of instances; add them to collections, and loop through them.

    Like

  9. Hi, Id like to download your macro but I cannot open the link in our office. hope you can send me a copy in my email?
    sandramiles02@gmail.com

    Liked by 1 person

  10. Hi,

    I cannot download the file in dropbox, looks like it was blocked. can you send the file in my email?
    sandramiles02@gmail.com

    Thanks!!

    Like

  11. I have sent you an email. Be sure to check your spam, if it did not show up in your inbox.

    Like

  12. thank you. i got it 🙂

    Like

  13. Hi There,
    It was nice to see such a use of VAB.Very Well done.

    Liked by 1 person

  14. Hello,

    I dont use any macro in my file so is there any way to use this amazing progress bar when Excel is Actually Calculating? I have managed to use the progress bar after excel finished calculating….or before.. but never at the same time. So how can i trigger the progress bar when sheet change event? or group/ungroup event?

    Thank you.

    Like

  15. Thank you can calling my progress bar amazing.
    Which version of excel do you use? Excel 2010 usually displays a progress bar in its status bar.

    Otherwise, calling the progress bar for excel calculations is not that easy. You can write a small macro to find out all the cells that have a formula, and then calculate them one by one. That’s using resources inefficiently, unnecessarily. I wouldn’t suggest that.

    If you want to enforce a full calculation, I can give you a small sub.

    Like

  16. Hello,

    I am using Excel 2010. Although i can see the status bar i would prefer the user to see a progress bar or at least a msg box to inform the user that excel is calculating at the same time that excel is actually doing it :).

    I am using a bunch of Indexes formula ( more than 500 rows ) that are searching the data in another sheet ( around 20 .000 rows). Excel is recalculating ,on a sheet change event from a dropdown list or group/ungroup. The actual calculation now last 20-30 sec.

    Thank you.

    Liked by 1 person

  17. Well, here is a suggestion:

    Create a User Form
    Set its Name property to “CalculatingForm”
    Set its Caption to “Excel is Calculating”
    Set it ShowModal Property to False
    Simply insert a label control, and set its caption Property to “Calculating…”

    Now, paste the following sub in a Standard Code module and Call it:

    Sub CalculateNow()
    
    DoEvents 'To Make sure the Lines of code following the Show Form Method Execute
    CalculatingForm.Show 'Show The form
    CalculatingForm.Repaint 'Make sure the form is rendered.
    
    Application.CalculateFullRebuild 'Recalculate all the workbooks
    'You can also use this line, if you do not want excel to recalculate dependencies: Application.Calculate
    
    Unload CalculatingForm 'Terminate the form
    MsgBox "Calculation Complete", vbInformation 'Indicate the end of a Successful Calculation
    
    End Sub
    

    This will ensure a full recalculation. Show the Form that says calculating. And it will also let excel show the progress on its status bar. Hope this helps.

    Like

  18. Hi,

    Looks great! I just can’t figure out how to integrate it into a macro. I tried one simple loop and it ran the full progress bar between each loop. I know it will probably be really simple but it just hasn’t clicked for me yet.

    Like

  19. Hi Danny, I need to see your code to check what’s happening. There is a simple loop in the workbook, that explains how to use the bar. Go through it again. If you still need my help, mail your code to me: StrugglingToExcel@outlook.com

    Like

  20. Hi Ejaz,

    First off, I just wanted to say a big thank you for all the times your knowledge has helped me.

    I’m really struggling to get this to work in the file I’m using. I have a fairly good understanding of how it all works but it is just that little bit too complex for my tired brain!

    I have a spreadsheet that when the main macro is run, it calls around 15 separate subs to do their thing and would like to use a progress bar such as yours to check on how far it has completed.

    Each sub is called like below:

    Call CombineDataUnapp
    Call SetMonthandTypeUnapp

    Are you able to provide some help as to what I need to do to get this to work?

    Cheers, Josh

    Like

  21. This is an excellent question Josh. And I am glad that my blog helped you at work.

    I have not considered this thoroughly, but I reckon you can use a Public ProgressBar object. Initialize it in the Sub that calls all the other Subs, and then try to animate it from the sub-Subs.

    Does that make sense? Give it a shot. Theoritically, it should work. Let me know if you need me to explain it more. If you can’t get it to work, I will give it a try this weekend, and let you know how it goes.

    Regards,
    Ejaz Ahmed

    Liked by 1 person

  22. Hi Ejaz,

    I’ve spent the morning trying to get it to work but no luck unfortunately. All I seem to be able to do is get my system to hang repeatedly!

    I’ve put a copy of the main module up on dropbox (https://www.dropbox.com/s/l82sbg2uhn40qfm/Module1.bas?dl=0). If you do find a chance to have a quick look, I would be forever in your debt.

    Thanks again,
    Josh

    Liked by 1 person

  23. Hello Team, Good Evening. I prepared an excel report for software testing metrics. i want to implement the progress bar untill all the reports generated. please send me the snippet for creating progressbar untill windows 7 progress bar is running.
    Thanks in advance

    Like

  24. Hi Ejaz
    I have a quick question for you . Is possible to update the Total Action number during the flow?
    I want to add some actions at half way of my code…

    Thanks

    Like

  25. You Could Go into the ProgressBar Class module and comment out the part where I do not let you change that attribute.

    Like

  26. I do not understand your question. Please give me more details. The Windows 7 Progress bar part confuese me the most.

    Like

  27. Hi, seems to be great work ! Could you send it to me by mail as well, please ? alex90@noos.fr Thanks !

    Liked by 1 person

  28. Hi Alex, I have sent you the file. And accepted your request on LinkedIn. Let’s keep in touch.

    Like

  29. Hello.

    Thanks for sharing the source code of your progress bar. It’s really nice.

    You putted a very good and handful option to allow the progress to be also displayed at Excel’s Status Bar. I would like to know if there is a way to show just the information at Excel’s Status Bar without showing the window of the progress bar itself. If there is no option for doing this currently, can you provide some code that can be inserted at your original code to acomplish this?

    Thank you.

    Liked by 1 person

  30. Hi Daniel,

    I am very happy that you like my work.

    Modifying Excel’s status bar is very simple. All you have to do it use the StatusBar property of the Application object.
    Type in: Application.StatusBar = “Your Message”

    Remember to set the property to false when you want Excel to take back control:
    Application.StatusBar = False

    If you are interested in showing the Rectangular boxes you can use the ‘ProgressText’ function, in the ‘ProgressBar’ userform, in the same excel file. Just copy that function into your project, and then call it with appropriate arguments to generate the boxes, as a string, and assign it to the StatusBar property of the Applications object. To indicate a 50% completion, I’d write something like this:
    Applications.StatusBar = ProgressText(5,10) & ” | Your Message here”

    Let me know if you’d like me to explain things in more detail.

    Regards,
    Ejaz Ahmed

    Like

  31. Hi Ejay

    Im using a very similar code however im hoping that i can get the userform to display each task as a list as it comples them.

    i.e rather than replace the text for each action, list the actions completed.

    Is there any way to amend your code to get this to work? im using excel 2007

    Thank you

    Dan

    Like

  32. I developed this to indicate the progress of for loops, hence the current action out of total actions construction. You are looking for something different.

    You could use a list box, and then simply select the task that is being performed at the moment.

    Another option would be, to replace the one line status bar that I have, with a bigger one; and tweak my code slightly to keep appending the messages to the existing text.

    Like

  33. I am sorry I answered without fully understanding your concern. Here is what I think you should do:

    Open up my form.
    Get the Name of the Label control I use to display the Status message in the Form.
    Add a new Text box. Give it the name you got in step 2, and delete the label control.
    Identify all the places where I update the Status Bar Label in the Form Module.
    >> LabelName.Caption = “New Text”
    Change the code to the following:
    >>> NewTextBox.Text = NewTextBox.Text & vbnewline & “New Text” >>> Last line has latest update

    or

    NewTextBox.Text = “New Text” & vbnewline & NewTextBox.Text >>> First line has latest update

    This will do the trick. Let me know if it does not work.

    Regards,
    Ejaz Ahmed

    Like

  34. Hello Ejaz,

    Your comment maked it more clear for me about how to use Excel’s status bar properly along with your progress bar code.

    In my code I putted an option for the user to choose if he wanted to show your progress bar or not. And if the user sets it to not show the progress bar I inserted this lines along the code in the proper ways:

    lngStatusBarAction = lngStatusBarAction + 1 ‘My declared variable to count Excel’s status bar actions.
    Application.StatusBar = ProgressText(lngStatusBarAction, MyProgressbar.TotalActions) & ” | Message”

    What I wanted was that Excel’s status bar to be always updated, regardless if the user choosed to show or not your progress bar. By doing that I could acomplish this.

    But I missed one thing. Excel’s status bar doesn’t show the percent number (example: 50%) and also doesn’t show the message “Action 5 of 10”, as it does when we set your progress bar to run and update Excel’s status bar. It’s showing only the unicode bar indicator and my message.

    I am doing something wrong? In this manual Excel’s status bar update approach, is it possible to also show the percent number and action count message?

    Thank you!

    Like

  35. If you only want to use Excel’s status bar, I’d highly recommend you to not use my Progress bar form. I feel it is very counter productive to use a Form Object, load it into your computer’s memory and not show it.

    All you have to do, to use Excel’s status bar is this:
    Application.StatusBar = “Message here”

    Excel’s status bar shows what ever you ask it to. Therefore to show the Percent Indicator, you have to code it in explicitly:
    Application.Statusbar = Format(lngStatusBarAction * 100 / MyProgressbar.TotalActions, “0”) & ” | ” & …The Unicode Function… & ” | ” & “Your Message”

    I recommend you to slightly modify the Unicode Progress Bar function to generate the Percent Indicator by itself, and then may be make provisions for it to accept the custom message, and then generate the exact text that should be displayed in the Status bar. And then ditch my progress bar completely.

    Something like this
    Function ProgressText(ByVal ActionNumber As Long, _
    ByVal TotalActions As Long, _
    Optional ByVal Message As String = vbNullString, _
    Optional ByVal ShowPercent As Boolean = True, _
    Optional ByVal BarLength As Long = 20)

    Dim BarComplete As Long
    Dim BarInComplete As Long
    Dim BarChar As String
    Dim SpaceChar As String
    Dim TempString As String

    BarChar = ChrW(&H2589)
    SpaceChar = ChrW(&H2000)

    BarLength = Round(BarLength / 2, 0) * 2
    BarComplete = Fix((ActionNumber * BarLength) / TotalActions)
    BarInComplete = BarLength – BarComplete
    ProgressText = String(BarComplete, BarChar) & String(BarInComplete, SpaceChar)

    If ShowPercent Then
    TempString = Format(ActionNumber * 100 / TotalActions, “0”)
    TempString = String(3 – Len(TempString), ” “) & TempString
    ProgressText = ProgressText & ” | ” & TempString & “%”
    End If

    If Len(Message) > 0 Then
    ProgressText = ProgressText & ” | ” & Message
    End If

    End Function

    And Then maybe call it like this:
    Application.StatusBar = ProgressText(Counter, Max, “Your Message” , True)

    Like

  36. Awesome class module! Thank you.

    Liked by 1 person

  37. Good job dude! Thx for free share…
    I’m going to change the variable naming convention (I prefer the “Hungarian Notation”) , convert some variables to constants(introduce the errors as string constants e.g. etc) and rearrange the methods and form factory – by introducing a forms handling class maybe.
    Some guys asks for donation for such a lousy work but you would deserve one!

    Liked by 1 person

  38. I am curious to know what a form handling class is.

    I learnt about using enums to display errors after this project. Is that what you mean?

    Like

  39. Hi Ejaz,

    It worked perfectly! Thank you!

    I just needed to add a little piece code in this modified ProgressText function to show the information “Action 50 of 100”.

    Sorry for the delay in answering, I was busy these days. Again, thank you!

    Like

  40. I’ve been playing with that thing and i ve reworked it a little bit. I hope you will not be angry about that…
    see then the change log in the user form, but i guess, the most significant change is the possibility to associated the progress pointer directly with the loop pointer, and the new Interface class to provide the user only relevant properties and methods

    I would be glad about some feedback best regards!

    https://www.dropbox.com/s/wb67hnq3g6ajdsf/Class%20Progress%20Bar%20V4.01.xlsm?dl=0

    Like

  41. I created an interface class first, and then decided to move to this set up, because it allowed me to move the subsequent progress bars below the promary progress bar quickly. But I agree that a intermediate class helps beginners use the progress bar.

    Like

  42. Absolutely first class….. thanx for the share…. really…100%

    Liked by 1 person

  43. Hi there! The DB hyperlink is not live. Would you send me the form via email? carneirodanilo@live.com
    Thx 🙂

    Like

  44. Hi Danilo,

    The Link is alive, but I suspect your network admins have blocked Dropbox. I could send it over to you only by the end of today, but if you need it in urgently, perhaps you could have someone on the outside, mail it to you?

    Regards,
    Ejaz Ahmed

    Like

  45. Hi Ejaz,
    It can be possible as I use a corporate machine…
    No, it is not urgent, send me whenever you can! Thanks for responding, tough,

    KR,
    Danilo

    Like

  46. Ejay…. I’m trying to use multiple instances of this Progress Bar… but I keep running into a conflict….

    If I have used the Progress Bar…. and then try to use it again in another instance (which worked previously)… then the Progress Bar Name comes up as an error…

    pbLogNewTrade.ShowBar ‘Critical Line

    I have both the Macro’s in modCovers…. and have Defined the New Progress Bar Name in each….

    I’m trying to make as much sense here as possible…lol…. and am hoping you know what I mean….

    What am I missing here…?

    Thanx

    MikeT

    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:

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

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

%d bloggers like this: