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. Hi Ejay!

    Thanks for the code. However, I’m having difficulties incorporating it with a macro. Below is the macro:

    ———–START of CODE————

    Private Sub REVEAL_ALL_TABS()

    ActiveWorkbook.Unprotect Password:=”myPASSword”

    Dim Sh As Worksheet
    Dim yourPassword As String
    yourPassword = “myPASSword”

    For Each Sh In ActiveWorkbook.Worksheets
    Sh.Unprotect Password:=yourPassword
    Next Sh

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws

    Dim Sh1 As Worksheet
    Dim yourPassword1 As String
    yourPassword1 = “myPASSword”

    For Each Sh1 In ActiveWorkbook.Worksheets
    Sh1.Protect Password:=yourPassword1
    Next Sh1

    End Sub

    ———–END of CODE————

    I’ve followed your explanation above, and I get the progress bar to display. But that’s it – the macro does not continue to execute, and the progress bar does not show any progress. It’s as if the userform keeps the focus and nothing happens from there on.

    I’d really appreciate some help on this! Thanks in advance!

    Like

  2. Extremely Nice Work! Your ProgressBar is a wonderful addition to my workbooks. It immediately stopped me from clicking the mouse while things were still running.

    The Progress Bar was appearing on the left margin of my second monitor while the worksheet was on my primary monitor. It was very confusing.

    I added the following to move it to the center of the workbook.

    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    

    I added it to the With MyProgressBar section as follows:

    With MyProgressbar
    .Title = “Macro Progress” ‘"Test The Progress Bar" ‘Set the Title
    .ExcelStatusBar = True ‘Set this to true if you want to update Excel’s Status Bar Also
    .StartColour = rgbGreen ‘Set the colour of the bar in the Beginning
    .EndColour = rgbRed ‘ Set the colour of the bar at the end

    'Move the Progress Bar to the center of the worksheet in a dual monitor setting.
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    

    End With

    You did some really nice work with your “MyProgressBar” module. Thank you!

    CraigM

    Liked by 1 person

  3. I just came across this Progress Bar and think it’s great. Very simple to implement when adding the form to the workbook. However, to keep all my workbooks simple and macro free I use a PERSONAL.xlsb for all my macros. I therefore tried to copy the form there and invoke it from PERSONL.xlsb. I know it’s possible to open form from other workbooks but for some reason I can’t get this to work. Any ideas?

    Richard

    Liked by 1 person

    1. I need to look at how you are invoking the form to get an idea.

      I suggest you look into creating an addin. I reckon is definitely better than using a binary file, in your case. You can even look into creating a simple ribbon to invoke the macros.

      Like

  4. Hello Ejaz,
    nice work…thanks for that, really appreciate it (that save me time and your progressbar looks much better then what I produced 🙂

    I wonder if you could help me.
    My process is the following:
    1. I open a text file count all lines. This number I am using for MyProgressbar.TotalActions
    2. Within my code for e.g. on line 61, I create a batch file –> execute it and an other sub routine tells me, when the external process is done. After that I return and go on to next line 62.
    Is there a way to have maybe another Instance of a second progressbar to show the progress of that sub routine? Or maybe in the statusbar of the progressbar an additional field which tells me the percentage of the sub routine?

    Also many thanks to CraigM for the snippet with the second monitor.

    Thanks a lot Astera

    Liked by 1 person

    1. Hi Astera. I am glad that you liked my work.
      There is an example of how to use two instances of the progress bar in the file.

      I suggest you check that out first. Get back to me if you have any more questions.

      Like

      1. Hi Ejaz,

        thanks for your quick answer.
        The ProgressBar (one Bar) works like a charm 🙂

        I found the “SubBar” in Class Progress Bar V2.01 in “modCovers”.
        Your example is within Sub TestTheSubBAR()
        I tried a lot to have the MainBar in my Sub and the SubBar in a further public function where I have a Do Loop. But it will not work.

        What I do is:

        In my SUB()
        ‘Declare Sub Level Variables and Objects
        ‘Initialize the Variables and Objects
        ‘Declare the ProgressBar Objects
        ‘Initialize a New Instance of the Progressbars
        ‘Set all the Properties that need to be set before the
        ‘ProgresBar is Shown
        With MainBar
        ……
        End With
        With SubBar
        …..
        End With
        ‘Show the Bar
        MainBar.ShowBar

        in my Do Loop within SUB() I start with
        MainBar.NextAction “Performing Action”

        so far –> OK The MainBar is visible and counts all.

        In my public function()
        ‘Declare the ProgressBar Objects
        ‘Initialize a New Instance of the Progressbars
        Counter = 1
        Do
        ‘Update the ProgressBar NextAction Method
        MainBar.NextAction “Struggling To Excel ” & Counter, True
        SubBar.Terminate —-> at this point I get
        Run-Time Error “Z”:
        Set TotalActions Property First
        When I commet out “MainBar.NextAction “Struggling To Excel ” & Counter, True” then it terminates my SubBar what is ok but my MainBar also !!

        Sorry but I can`t get it to run proper 😦
        Could you please help me a little bit?

        cheers Astera

        Liked by 1 person

        1. Hey Astera,

          I need to see where exactly you are declaring the progress bar object.

          The following declaration needs to be outside of any sub in a standard code module:
          Public MainBar as ProgressBar
          Public SubBar as ProgressBar

          I suggest you download the ProgressBar again. I have updated it with a new module “modCovers2” that uses Public Progress Bar objects, and two subs to illustrate how you can modify it from different subs.

          Check it out and get back to me if you have any more questions.

          Regards,
          Ejaz Ahmed

          Like

        2. Hi Ejaz,

          thanks for coming back to me.
          I played a little bit at the weekend and found a way. But I don`t know if this is a good way from a programmer perspective.
          I changed ALL “private” statement to “public” within the class “ProgressBar”
          In my MainSub I declared only the MainBar
          In my SubSub I declared only the SubBar.
          To have the MainBar position within the SubSub I created variables like this direct after ShowBar:
          ‘Show the Bar
          MainBar.ShowBar
          mbLeft = MainBar.Left
          mbTop = MainBar.Top
          mbHeight = MainBar.Height
          And used it as parameter to call the SubSub
          (RunShell UPNbat, mbLeft, mbTop, mbHeight)

          If you recommend to change back public to private and use the
          Public MainBar as ProgressBar
          Public SubBar as ProgressBar
          outside my Modules I will give that a try.

          Another little thing I like to mention:
          Your code:
          ‘Move the Second bar below the main Bar
          SubBar.Top = MainBar.Top + MainBar.Height + 10
          SubBar.Left = MainBar.Left

          Whenever the SubBar will be shown first it goes under the MainBar and then to the left. That creates a “twitch” or “flickr” (sorry don`t know how to say in English).
          If you change to have first the “left” statement there is no “twitch”
          SubBar.Left = MainBar.Left
          SubBar.Top = MainBar.Top + MainBar.Height + 10

          cheers Astera

          Like

        3. I am happy that it works for you now. Changing the scope of the subs within the class module is not necessary. In fact, I recommend that you don’t change it.

          Some subs were intentionally declared as private so that they are not available to the user, so they do not get confused with options they were not meant to use.

          Like

  5. I really like what you have done with MyProgressbar. Your skills at programming classes are far beyond my own.
    I provided the suggestion to center the progress bar on the worksheet a week or so ago:

    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) – (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height)

    You did a really good job with the classes. I find I can invoke as may progress bars as I want and keep them open until I choose to close them. Each class instance inherits and respects its properties correctly.

    So now I am going to give you a challenge I hope you will take up.

    I’d like to suggest you modify the class code so that when more than one instance is opened they all remain centered in the worksheet. Progress bars that are open would move up a little (-38) and the newly invoked progress bar would be placed below (+75) the ones just moved up.

    I added a calculation to your positioning code that places the second bar below the first. It seems like it would be possible to track how many bars are open and how to position the next one.

    With MyProgressbar2
    .Title = “Aligning Ouput” ‘Optional
    .ExcelStatusBar = True ‘Optional
    .StartColour = rgbMediumSeaGreen ‘Optional
    .EndColour = rgbGreen ‘Optional
    .StartUpPosition = 0 ‘Move the Progress Bar to the center of the worksheet in a dual monitor setting.
    .Left = Application.Left + (0.5 * Application.Width) – (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height) + 75
    .TotalActions = 2
    End With

    It’s the addition of + 75 at the end of the line that moves the second bar down below the first.

    .Top = Application.Top + (0.5 * Application.Height) – (0.5 * .Height) + 75

    Multiple bars work great because of the way you crafted the inheritance.

    Using the + 75 as a reference you might move all open bars up -38 and then drop the new bar in at + 75 from the last invoked bar (lowest bar).

    You may have to limit the number of bars invoked so that they all sit one form. I haven’t thought that through all the way, yet.

    I leave MyProgressbar1 open and invoke MyProgressbar2. As bar2 gets ready to close I use your statement:
    MyProgressbar2.Complete 2

    This closes the second bar and returns focus to MyProgressbar1, which then invokes its command:

    MyProgressbar1.Complete 2

    This closes the first bar.

    It is magic! Multiple MyProgressbars look great and keeps the user informed of the progress of all actions being undertaken. I can easily see invoking five simultaneous bars as processing moves from subroutine to subroutine and then back out.
    Another thought I had is to convert the Excel Color Palette rgbGreen to HTML #FFF00 to give a broader range of colors. I haven’t thought that through yet either.

    You have a first class MyProgressbar (no class inheritance intended) :-).

    CraigM

    Liked by 1 person

    1. Hi Craig,

      The joke at the end cracked me up; good one.

      I’d like to take you up on your challenge, but I am afraid I do not have the time now, I have not written any posts here in months and I’d like to give some of my other ideas some time.

      Coming to your comment about colours. I am not using excel’s palette. Excel’s colour pallette is linked to the ColourIndex property of the Interior Object. The Colour property on the other hand uses the Hexadecimal colour system. Also the palette does not apply to UserForms.

      User Forms use the Hexadecimal system just like HTML. You can use VBA’s RGB function to explicitly specify the colour you want, or use any of the predefined constants to pick a colour quickly.

      I recommend you look into the RGB function: https://msdn.microsoft.com/en-us/library/zc1dyw8b(v=vs.90).aspx

      I thank you again for your support and efforts extending my work.

      P.S: I recommend you use the Form’s Height property instead of explicitly specifying it in code, so your code would still work if the use ends up resizing the userform.

      Regards,
      Ejaz Ahmed

      Like

    1. Hi Kaleb,

      I am happy that you figured it out yourself. I removed the comment you put in earlier because it was super long; I hope you don’t mind.

      I’d remove the current one also if you prefer.

      Thank you for dropping by.

      Regards,
      Ejaz Ahmed

      Like

    1. There is a drop box link in the post. Click on the file name please. The link might not work if you are trying to access the file from work. Network Admins block cloud services left right and center nowadays.

      Like

        1. Thank you for visiting. Do let me know if you need to email the file to you. I haven’t posted anything new in my blog in a while. I am working on a few ideas now; I hope they make you come back here.

          Have a good day.

          Like

  6. i want to say how much style this progress bar adds to any project. however when i call this macro from my project, it runs and after it is complete the next line of code starts-which must be obvious. but then how is it showing any progress in my project? it is in fact taking up more time. or am i missing something?
    i want this progress bar to keep the user occupied till the calculations being done by excel are over but i am not able to achieve that please
    help!
    regards
    vivek

    Like

    1. Excel does a lot of things in the background when it calculates. You may want to check out my post about volatile functions to get an idea.

      Only Excel knows exactly what percentage of the calculation is remaining or done. And to find that out will use up resources unnecessarily, and I reckon it is not worth pursuing.

      Note that it does show a progress indicator when it does any calculations in the status bar. However, there is a catch: if your project is enormous, users clicking on the sheet may stop the calculations. And that is precisely why you want to show a progress bar, and stop users from interfering, yes?

      I reckon that the best way to ensure a calculation is made is to Disable screen updating and Events; then run the Application.Calculate or CalculateFull or CalculateFullRebuild methods, in the order of how paranoid you are about ensuring calculations are performed correctly. Don’t forget to enable Screen Updating and events at the end. This will ensure that the user does not stop the calculations inadvertently.

      Unfortunately, I am afraid showing a precise progress bar is not that straight forward. If you are very particular about showing progress, you could just go sheet by sheet and calculate individual sheets. This is very very inefficient, but could fake some sort of an indicator about progress.

      Like

  7. Hello Ejaz,

    It is truly a clever way to display and manage progress bars. I’ve been doing the clunky mistakes by using different forms for different parts.
    I implemented your bars quite easily, it is really straightforward to use.

    Thanks for the time that you dedicate in your website, I’ve found multiple useful tricks on it.

    Keep doing the good work!

    Aubry

    Liked by 1 person

    1. I am glad you like it. And I am glad you took the time to check out the other posts.

      I spent a lot of time re-building stuff for every project and I got fed up of it. That is why I started thinking of ways to make re-usable projects.

      I want my readers realize that VBA can be super powerful, if you treat it as a programming language, and not a record and play scripting language.

      Thank you for visiting. I hope you will spread work about my little blog. Have a great day.

      Like

  8. Hi Ejaz,
    i have this macro which delete some sheets from workbook:

    Sub EliminaAlcuniFogli()

    Dim ws As Worksheet
    Application.DisplayAlerts = False
    Call ProgressBar
    For Each ws In Worksheets
    If ws.Name <> “DASHBOARD” And ws.Name <> “RinominaFile” And ws.Name <> “GenerazioneFogli” And ws.Name <> “ListaMail” And ws.Name <> “Sheet2” And ws.Name <> “COPIA SPOSTA FILE CARTELLE” And ws.Name <> “ORIGINAL” And ws.Name <> “UPDATED” And ws.Name <> “TROVA ERRORI” And ws.Name <> “CHANGES” And ws.Name <> “COPIA SPOSTA FILE AVANZATO” Then ws.Delete
    Next
    Application.DisplayAlerts = True
    End Sub

    Now, where can i insert the progressbar call?
    Thank yuo so much.
    Andrew

    Like

  9. I’m thinking of incorporating this onto something I’ve created. The primary function is to extract a number of user set variables from a unformatted data extract sheet and then populate them onto a nice, pretty looking new sheet. The thing is, depending on what is requested by the user, the process could be short or long. I’m struggling to understand if this progress bar will accuratley track progress of my code or whether I input a guesstimate time for when I think the process will finish. Could you advise?

    Like

    1. This progress bar cannot be controlled by time taken. This can be controlled only by number of actions completed in relation to number of actions to be performed. The only way I see you using this is to count the number of actions to be performed by looking through your data first and setting the number of actions to be performed. Then you increment the number of actions performed when it is performed.

      I understand that this is a slight waste of resources, but that is the only way you can use this.

      An alternate way, is to consider all possibilities as one action, and you set that as the total number of actions. And you can increment the counter even if you did not have to copy that particular value.

      I hope that helps.

      We have been experiencing unprecedented rainfall and floods here in Chennai; hence the late reply.

      Like

  10. Has anyone else run across an issue when using .Value in conjunction with the progress bar? There’s a variable called “value” within the progressbar code that breaks the .Value property once a progressbar is created. Is there any workarounds for this? I did a simple find-replace “value” with “val” inside the progressbar code and am still running into problems. Any help would be much appreciated.

    Like

  11. Hi Ejaz,

    Thanks for this lovely piece of coding.
    I browsed through all the comments but I could not find any mention about how to turn off the close button on top of the form.
    I run some complex macros where they should not be aborted.
    Please guide me on what changes (and where) to make in your code.

    Thanks a lot!

    Like

    1. Well, hiding the close button on the form would require some complex windows api programming i suppose.

      You could however, try adding some lines of code in the terminate sub of the form, to check whether the progress bar hit hundred before letting it run fully. This is just a theory. Test it out, and let me know what happens.

      Like

      1. OK, a bit of googling helped!

        Source: http://www.excelforum.com/excel-programming-vba-macros/694008-remove-user-form-borders-post2138340.html#post2138340

        And: http://www.excelforum.com/excel-programming-vba-macros/875940-remove-borders-and-x-sign-on-a-userform-through-vba.html#post3874313

        This is what I did:
        I added the following code in ProgressBar Form
        Private Sub UserForm_Activate()
        Me.Height = 0
        RemoveFrame
        Me.Height = 50
        End Sub

        Added another module
        ‘Written: August 01, 2009
        ‘Author: Leith Ross
        ‘Summary: Removes the Titlebar and thick border around a UserForm.

        ‘Returns the Window Handle of the Window that is accepting User input.
        Public Declare Function GetForegroundWindow Lib “user32.dll” () As Long

        Private Declare Function GetWindowLong _
        Lib “user32.dll” _
        Alias “GetWindowLongA” _
        (ByVal hWnd As Long, ByVal nIndex As Long) As Long

        Private Declare Function SetWindowLong _
        Lib “user32.dll” _
        Alias “SetWindowLongA” _
        (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
        Sub RemoveFrame()

        Dim Bitmask As Long
        Dim hWnd As Long
        Dim WindowStyle As Long

        Const GWL_STYLE As Long = (-16)
        Const WS_DLGFRAME As Long = &H400000

        hWnd = GetForegroundWindow
        WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
        
        Bitmask = WindowStyle And (Not WS_DLGFRAME)
        
        Call SetWindowLong(hWnd, GWL_STYLE, Bitmask)
        

        End Sub

        I hope the code appears clean here. Seems to work. Will test a bit more.

        Like

  12. Hi there,

    great macro and a nice looking progress bar 🙂

    I was wondering what the terms of use (or the licensing) of your code are? Ist it allowed for companies to use it in their documents (for free)?

    Thank you in advance!

    Regards,
    Sebastian

    Like

    1. Feel free to use it in your documents. All the models here are to illustrate ideas and coding practices to learners. If you really like what you are seeing, and would like to support me, I’d invite you to make a donation to keep my blog running.

      I am very grateful that you even took the time to enquire. Much appreciated!

      Like

  13. Hi Ejaz, thanks for the progress bar it looks and works amazing! I’ve implemented it into a huge amount of code and for some reason when calling “Myprogressbar.complete 3” at the end of the code it seems to run it twice. The progress bar will say Complete, closing in 3,2,1 and then flash and do it again before actually closing. Not a huge issue but seeing as it looks so good I thought I may as well try and make it look as pro as possible. Do you have any idea why this might be happening? I thought it could be a screen updating thing. Cheers, James.

    Like

  14. Great progress tool for Excel. Ran into issue with Windows 10 where and Excel 2016 where color fill for progress has stopped working (worked then something changed with Win 10). I have verified that this is consistent across several Windows 10 machines. Works fine in Windows 7. Anyway, just curious if there are any other instances of this happening.

    Like

    1. I recently migrated to Office 2016 on Windows 10 too. I just checked and I am facing the same issue too. This bug happened even on design time. I am able to change to colour of the label, but as soon as I click the title bar of the user form, the colour reverts to that pale grey.

      Microsoft is releasing its operating systems without testing things, and now office 2016 is also beginning to get very buggy.

      God Save Microsoft.

      Like

  15. Another point I’d like to make here is that this setup is capable of displaying two progress bars simultaneously. If you want to do that, and not able to, then let me know. I’d help out. I’d need to see your code though.

    Like

  16. It was simply a “Runtime Error ‘401’: Can’t show non-modal form when modal form is displayed.” The easy fix was to simply Hide the previous form before loading the ProgressBar form…..

    My mistake… I should have taken more notice of the error message….

    Love this Progress Bar…. works like a charm….

    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