Advertisements

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
Categories: ApplicationsTags: , , ,

141 comments

  1. Did you try looking at the cover macros in the Workbook?

    Like

  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

  4. Thank you for sharing your experience here. And thank you for the feedback. Hope you come back and find something useful.

    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

  6. Thank you for letting me know.

    Like

  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

  8. Thank you. It has been a while since I posted anything. I’m hoping to change that this year. Wish me luck!

    Like

  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

  10. Thank you for taking the time to leave a comment. Much appreciated!

    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

  13. Great work! Learned a lot on how using Objects. THANKS!

    Like

  14. Glad I was able to help!

    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

  18. Try to look at my cover macros and ask me what you don’t understand about it specifically. I’d be glad to help you understand.

    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

  21. hi Ejaz,
    thank you, I have sent you an e-mail, please reply,
    thank you
    Haris

    Like

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

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

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

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

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

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

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

  29. You made my day Andy. Thank you! God bless you too!

    Like

  30. It’s pretty amazing your work, I will try to understand the code, the class code into the form, impressive. Thanks for sharing

    Liked by 1 person

  31. Good luck with understanding the code. Do ask me any questions you may have.

    Like

  32. Thank you for the efforts!

    Liked by 1 person

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

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

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

  36. Hi Ammar. This is the first time Dropbox is limiting downloads. I was not aware of this. I am looking into this, I’m considering other places to host my data. For the time being, please write to me at ejaz@strugglingtoexcel.com. I’ll email you.

    Like

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

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

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

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

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: