Progress Bar for all your excel Applications


Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing. Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run. While there are users who are happy with just a Msgbox “This thing is DONE!”, there are others who’d like to let the user know more about what is happening.

If you are thinking, “Hey I can see exactly what is going on, why would I need to display messages?”, you may have to consider digging into optimizing your code a little. I admit that allowing Excel to update the screen each time your macro makes a change, helps during development, but it slows down the code a lot. It is best to set the ScreenUpdating and EnableEvents properties of the Application object to False at the outset. I intend to write about the best practices in the coming weeks, but let us get back to the issue at hand. The quickest way to display messages while the code is running is to use the Statusbar property of the Application object. Users can display custom messages on Excel’s status bar when needed, and allow excel to take over after their macro finishes executing. Here is a short example:

'========================================================================
'A Macro to Illustrate the use of Application.Statusbad property
'========================================================================
Sub StatusBarExample()
'Disable Screen Updating and Events
Application.EnableEvents = False
Application.ScreenUpdating = False

'Declaring Sub Level Variables
Dim lngCounter As Long
Dim lngNumberOfTasks As Long

'Initilaizing Variables
lngNumberOfTasks = 10000

For lngCounter = 1 To lngNumberOfTasks
    'Altering the Statusbar Property
    Application.StatusBar = "Executing " & lngCounter & _
        " of " & lngNumberOfTasks & " | " & _
        "Custom Message " & lngCounter
Next lngCounter

'Letting Excel Take over the status bar
Application.StatusBar = False

'Enable ScreenUpdating and Events
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Another popular method is to deploy a UserForm and have a label control that displays a message. We may also choose to use the width property of a control and resize it as the code progresses, to display a progress bar of sorts. A fellow struggler, a good friend of mine, used to create dedicated forms for each of his macros, just to display a progress bar. That got me thinking, what if I could give my readers a generic form that they could use in all their spreadsheet applications? And I created this Form-Module combo.

Progress Bar

So here is what you need to do, to use this in your code. Download Progress Bar V1.01.xlsm from DropBox, import the userform, ‘ufProgress‘; and the module, ‘modProgress‘, into your VBA project. You may just drag the objects and drop them into your project, in the Project Explorer window of the Visual Basics Editor. And Call the ‘modProgress.ShowProgress‘ with a few arguments. Listed below are the arguments, and their purpose:

  1. ActionNumber – This argument denotes the number of actions that have already been performed. You may probably use a For-Loop counter here, or explicitly change it as the code progresses.
  2. TotalActions – This denotes the total number of actions you need to perform.
  3. StatusMessage – The form has a little status bar at the bottom and you can display custom messages there. ideally, a short description of the action.
  4. CloseWhenDone – Set this argument to True, to close the form automatically when ActionNumber reaches TotalActions. Hence, remember to call ShowProgress with the last ActionNumber only when that action is complete.
  5. Title – You may choose to set a custom title for the form here.

Here is an example of how to use the ShowProgress sub:

Option Explicit

'========================================================================
'Cover Macro to Test the Progress Bar
'========================================================================
Sub TestTheBar()
'Disable Screen Updating and Events
Application.EnableEvents = False
Application.ScreenUpdating = False

'Declaring Sub Level Variables
Dim lngCounter As Long
Dim lngNumberOfTasks As Long

'Initilaizing Variables
lngNumberOfTasks = 10000

'Calling the ShowProgress sub with ActionNumber = 0, to let the
'user know we are going to work on the 1st task. Also, set a
'title for the form
Call modProgress.ShowProgress(0, lngNumberOfTasks, _
                   "Excel is working on Task Number 1", False, _
                    "Progress Bar Test")

For lngCounter = 1 To lngNumberOfTasks
    'The code for each task goes here

    '

    'Call the ShowProgress sub each time a task is finished to
    'the user know that X out of Y tasks are over, and that
    'the X+1'th task is in progress.
    Call modProgress.ShowProgress(lngCounter, lngNumberOfTasks, _
                    "Excel is working on Task Number " & lngCounter + 1, False)
Next lngCounter

'Enable ScreenUpdating and Events
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Note that you can press the Abort button anytime to stop the execution. And this workbook also has a simple user defined function that you can use to check if a form has been already opened. I urge you to use them in your projects and let me know if you like it.

Update!

I have created a new class based progress bar. The new version does not need the Standard Code Module to animate the bar. Also, you can create multiple instances of the Progress Bar simultaneously. I recommend using that version in  your code. Check it out and let me know what you think.

Advertisements

68 thoughts on “Progress Bar for all your excel Applications

  1. Hello Eyaz,

    When I run Sub TestTheBAR I get an compile error “User-defined type not defined” and it shows line
    “Private cStartColour As XlRgbColor” in module ProgressBar.

    I suppose that I do something wrong (or don’t do something obvious), and hoped to find a hint reading the comments here, but instead get just frustrated: everybody is delighted with the your ProgressBar, and I can only imagine how it works 🙂

    (Btw, I use Excel 2003 – can that be the problem?)

    Regards,
    John

    Like

    • Hi John,

      Using excel 2003 can easily be the problem. Perhaps it does not have those enums defined in the object model.

      First off, Microsoft withdrew support for office 2003 years back. It is extremely vulnerable to security threats now. I recommend you upgrade. If you are an individual consultant, try office 365. The subscription based model ensures you are always on the latest version for a very low cost upfront. If you are part of firm, it is high time they upgrade for their own good.

      Now let us try to solve this problem, so you don’t have to just imagine how this works. The Xlrgbcolor is just a number, so find replace that with “long” in the entire project. After you do that, you will most probably get errors at the places where I used to named constants in that enum. For instance, I may set the progress bar’s start colour to RoyalBlue somewhere. You have to use the rgb function there to define a colour at all those errors.

      You might hit some bumps in other objects. Let me know if you do.

      In the meantime help me spread the word by recommending my blog to your friends and co-workers please.

      Have a nice day.

      Regards,
      Ejaz

      Like

  2. Pingback: Progress Bar Not Updating

  3. Hi Ejaz,

    Thanks for providing the Progress bar. We are planning to include in our existing excel sheets.
    By default we get a spinning wheel when the macro runs…….we included your progress bar in our code….it pops out the progress bar during the durination of the macro run…But also we get the spinning wheel….which might confuse users…….at work most of the users have dual screens…..if you run the excel in screen1 and if progress bar pops out in screen2 ,..the spinning wheel still shows up on screen1…this might be confusing to the users….is it expected behavior….is it possible to suppress the spinning wheel and show only thee progress bar?

    Like

    • Kumar,

      The spinning wheel is controlled by some other code that you have already incorporated. My code has nothing to do with it. I suggest you look at your modules and comment out the part which calls the spinning wheels.

      As for the multiple monitor problem, there has already been a few discussions about this in the comments section. Please take a look at it, and get back to me if you have any more questions.

      I am glad that you are planning to use my code in your projects. Please follow Struggling to excel on Facebook and or twitter for more updates.

      Regards,
      Ejaz

      Like

      • Hi Ejaz,

        adding the following code to userform resolved this issue.

        Private Sub UserForm_Initialize()
        ‘PURPOSE: Position userform to center of Excel Window (important for dual monitor compatibility)
        ‘Start Userform Centered inside Excel Screen (for dual monitors)

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

        End Sub

        Liked by 1 person

  4. I incorporated the progress bar into an application that I’m writing today. I added code to the form initialize routine to center the form on the Excel window. I also found that I was switching workbooks and worksheets during the display time of the form. This then pushed the form behind the Excel app until it was called for an update and it did not display the controls when it was visible due. I changed the code to not switch workbooks and not change the worksheets and it now works just fine. Thank you for crafting this and making it publically available. Nice coding job.

    Like

  5. Very helpful. Thank you.
    By the way, how did you create the label control lblFront ? It seems like you have lblFront , lblBase, and lblPct labels overlapping each other but I couldn’t find the property of the lblFront. Can you show me how? Thanks again

    Like

    • Excuse my late reply. I set the lblFront label’s width to 0. Therefore you wont be able to physically click it in the design mode. You could, however, choose it from the combo box in the Properties Pane of the VBA-IDE.

      Like

  6. This is really impressive Ejaz. I was looking something alike for quite a while now. However I have some questions. I would like to implement this progressbar into my programm. I didn”t quite understood your comment in the line ‘The code for each task goes here. My task are basically other subprogramms which I call. Example I have 5 sub programms named: Update_Table_1, …, Update_Table_5. I have set the folowwng code: lngNumberOfTasks = 10000 to 5 instead of 1000 as I have 5 updates that I need to perform. However i if I do the following:

    For lngCounter = 1 To lngNumberOfTasks
    ‘The code for each task goes here

    Update_Table_1
    Update_Table_2
    ...
    Update_Table_5
    
    'Call the ShowProgress sub each time a task is finished to
    'the user know that X out of Y tasks are over, and that
    'the X+1'th task is in progress.
    Call modProgress.ShowProgress(lngCounter, lngNumberOfTasks, _
                    "Excel is working on Task Number " & lngCounter + 1, False)
    

    Next lngCounter

    I have notice then, that the programm updates each Tables 5 times, 5 x Update_Table_1 and so on. What am I doing wrong. I hope I don’t offend you with my silly question, I am pretty new to VBA.

    BR,
    Mike Anumis

    Liked by 1 person

    • You don’t have to use the for loop. Most task are repeated in a loop, and that is why I went with that example. You have five different tasks to complete. Just call the show progress sub after each of your subs.

      Call Update_Table_1
      Call ShowProgress(1,5,”Updating Table 1″)
      Call Update_Table_2
      Call ShowProgress(2,5,”Updating Table 2″)

      Like

  7. This is a great help. Thank you. One suggestion: You may want to move the DoEvent to the bottom of the ShowProgress function. Otherwise it will always update the progress bar one cycle “behind”.

    Liked by 1 person

    • Hi Ras,

      The do events has nothing to do with the lagged cycle I suppose. You just have to call the next action method accordingly.

      I’d look into this, but the do events gives back to control to the calling macro, and it is enough to have it at the end.

      What do you think?

      Like

  8. I sent you this question via your contact page, but I will post here in case someone else has run into the issue.

    My workbook compiles a bunch of raw data into an pivot table, does some calculations with this aggregated pivot table data, and displays some charts (not pivot charts) and summary statistics. All of these charts and statistics update when a user changes the slicers for the pivot table.

    Everything works fine except when the abort button is pressed and the user form unloads, the charts and statistics no longer update when the slicers are changed. Furthermore, my mouse wheel no longer scrolls and my “X” button in the top right doesn’t close the workbook. I thought this sounded like a screen updating issue, as my macro doesn’t turn screen updating back on until the end, making me think that if the code is “broken” by the user hitting abort that the screen updating was never turned back on.

    I added code to turn screen updating back on within the user form abort button sub, but it doesn’t seem to be helping.

    Anyone have any ideas or run into something similar?

    Like

    • The symptoms suggest the screen updating property was not der back to true.

      I remember adding the End statement somewhere in the abort code. Did you actually enter the debug mode and check if the statement gets executed?

      None of the other readers had this problem.

      Like

      • Your abort code looked as follows:

        Private Sub cmdAbort_Click()

        Unload ufProgress

        End
        End Sub

        I added some code after unload ufProgress to delete and worksheets that would have been hidden/delete if the macro finished running.

        I will try debug mode to see what is happening, but would you suggest simply adding application.screenupdating = true in this code?

        Like

          • And an additional problem I have found following “Abort” is that when I re-run the macro, it no longer seems to be working with the data. It flies through the code and displays no results, and if I try to abort while this is running, excel crashes.

            I think I will simply remove the abort button and make the user sit through the 5 minute run time. At least they can see the progress!

            Liked by 1 person

            • This Abort button is in there to recognize the even and let you execute a set of code before it closes down. I do not know the specifics of you project to be able to help you there. You need to recount all the changes that you have done since the macro began, and un-do a sub-set of them for the worksheet to behave as it should.

              Primary things that you should reset are:
              1. Screen Updating
              2. Enable Events
              3. Display Alerts
              4. Protection for Sheets and Workbook Structure

              Like

          • So I discovered the problem, and it was not in your code nor was it the screen updating. For whatever reason, the workbook calculation option got set to manual, so in order for the charts to update, I needed to press F9. How this option got selected, I’m not sure, but problem solved.

            Thanks for taking the time to assist me. Your progress bar is a life saver and it works great!

            Like

  9. This is a great script. I found other scripts to be lacking in the sense that they don’t have abort functions. And those that did have them had buggy error handlers. This script worked like a charm. I have added a on err goto cleanup. This ensure that all the items that were false are now set to true on exit e.g. screenupdating = true.

    Liked by 1 person

    • Hi Afam, I am glad that you liked this. I have another Progress Bar, which is class bases, and does not need an additional standard code module to control. I request you to give it a try. The link is at the end of this article.

      Like

  10. Pingback: First Class Progress Bar for all your Macros #Excel #VBA | Struggling To Excel

  11. I like it a lot. But the code I incorporated it with (which examines over 5000 rows of data for inconsistent line breaks and concatenates/repairs them) takes about 5 seconds to run without the progress bar and more than 4 minutes with it. Is it that intensive that it would drag it down that drastically? Thanks.

    Like

    • Thanks for giving it a try. I am surprised that my macro slowed down your macro that much. I definitely do not expect the progress bar to affect the run time to that magnitude.

      I’d like to see how you are using it in your project. Perhaps I may be able to figure out what is slowing down the code. If you don’t mind of course. My mail id is StrugglingToExcel@outlook.com

      Like

  12. I’m having a couple of issues … I tried plugging the modules into my code and got a modal / non-modal form error. To remedy this, I tried hiding my form while the status bar is displayed. Then unhiding when status bar completes. Now when status bar goes away, it displays my form exactly as I left it, and does not continue my code. So it basically shows me the processing time, without giving me the info it took time to process. Any guidance would be appreciated.

    Like

    • Hi. I designed this form to work as a non modal form; i.e. ShowModal property set to False.

      I am afraid I did not fully understand the part where you speak of Time taken for the macro to run. I did not code any of that into my form; did you teak it to show elapsed time?

      Either way, I think you need to show a non-modal form, and use the DoEvents statement to tell EXCEL to continue working on your macro. This is just generic advice, I need to take a look at what you have done to help you further. I am happy for you to write to me at StrugglingToExcel@outlook.com

      Also, I am working on a new progress bar, that I coded as a class module. I will post it soon. I hope you will give that a shot too.

      Regards,
      Ejaz

      Like

  13. Friend, First, congratulations for the work.
    how do I use the progressbar in different worksheets that have different runtime?
    each other takes 2 minutes and 5 minutes.
    I would like the bar to accompany the runtime.
    is to help me?

    email: ccarlos1981@hotmail.com

    Like

    • Hi Cristiano,

      I apologise for the late reply. My computer crashed. I am glad you like my work, do remember to invite your friends to view my blog (perhaps on Facebook or twitter).

      I do not fully understand your question, are you working with two different files? or one file with two worksheets?

      All you have to do is Import the form and the module into your project. And then call the ShowProgress sub with appropriate arguments. It will work with any workbook.

      If you can’t get it to work, feel free to send me a mail at strugglingtoexcel@outlook.com with your file and screenshots. I’d take a look at it and see if I can solve the issue.

      Liked by 1 person

  14. Hey,
    I have been trying to implement a status bar for a full day now, and I thought I might have finally found something that works here, but once again I am stuck. I downloaded the example and tried to plug it in to my document, but it wasn’t working. I was a little confused, so I went back to the example document and tried to run the testbar macro and that didn’t work either. I commented out a lot of the code to try to just display the userform and update the title, but that didn’t work. I am using mac excel 2011, which I have found to be much worse than windows excel because stuff just doesn’t work on it sometimes. Could this be the reason? Any idea how to do a progress bar on mac 2011? I am running out of ideas, and everything that people say will work has not worked. If I don’t figure it out by the end of today I am going to move on and come back to it in a month

    Liked by 1 person

    • Hi Grant, I have never worked on mac before. I am afraid I do not know how VBA works in MAC. I have not used any api calls to OS, so a change of operating system shouldn’t be a problem.

      I am afraid I do not have a computer running mac; I would not be able to test stuff on it.

      Anyway, what error message do you get ?

      Like

      • No error message; The userform just pops up identical to how it is when you edit it, with no custom title, no custom message, and at 0%. I think it is just a mac thing that it can not update, even when I run DoEvents

        Liked by 1 person

            • I am new-ish with VBA so I am not too familiar with break mode. I am trying to use it, but every time I enter it and try to work with the macro I get an error saying “Can’t execute code in break mode”. I have had similar problems with every other type of status bar I’ve tried to implement as well, so I think that the whole problem is with mac excel, which is significantly worse than pc, but oh well. Unfortunately my whole company uses macs and I am building a company tool, so I do not have much of a choice. Thanks for your help, I appreciate the effort

              Like

  15. New to VBA. Searched, found it, tried to add it – couldn’t get it to work – obviously doing something wrong.

    Like

  16. Pingback: VBA - Progress Bars

  17. Hi Ejaz, there is so much to learn in excel and I am not sure where to start 🙂

    Since you are an expert on the subject, can u please guide me where to start. I would like to master VBA as well.

    I can be reached at rajsaini7@gmail.com.

    Thank and Regards
    Raj

    Like

    • Hi Raj, I am surprised you consider me an expert. Infact I am very far from it; I am an enthusiast at the most.

      There are a lot of concepts in VBA that I have not even touched yet, like making my own classes, using API calls and integrating with a database. I use vba just to automate excel processes and make my work a lot less boring.

      I did not formally study VBA formally. Whenever I needed to automate something, I just google how to do it in VBA and implement it in my projects. The one thing that changed how I work with VBA was when I was working with an Actuarial Firm’s excel model building team.

      The team of experts there had an iron clad toolkit, general purpose modules and created lightning fast models. Their coding philosophy was amazing. They concentrated on Speed, Structure, Standards, Tractability and made the code audit friendly.

      So my advice is to look at a lot of code written by experienced VBA programmers, analyze why they decided to put in each and every line that they put in. Query them if you don’t understand and improve your skills.

      Becoming an expert should not be the first objective of a beginner if you ask me. Learn it, Use it, Build it and eventually you would master it.

      Finally, where do I start? is not the kind of question I expect from my reader. Get a basic VBA book and go through it. After you have done that, take up a few projects; either automate something at work, or envision your own project. After that, it is just a matter of optimizing your code.

      Take a look at the code experienced programmers have written and try to understand the intent behind each line of code. I tend to write a lot of comments in all the code that I post here, that should be a good start.

      Like

      • I learned my VBA exactly in the same manner. Trial and error and using existing code to change it to my own needs.
        VBA programming is nothing more then ‘telling’ the system what you want to do, it does exactly that, the only problem is that you need to ‘speak’ the same language, and that is the existing Functions and their correct syntax. I’m faraway from being an expert.
        Your own imagination will be the only limit. Google is a very good help in these matters

        Liked by 1 person

  18. Like it, I have a similar userform which I load first and enter the information of the task being performed.
    The Abort button when completed is not a nice choice
    I edited your code as follows:

    ‘Check of all the actions have been completed
    If ActionNumber = TotalActions Then
    With ufProgress
    .cmdAbort.Caption = “Completed”
    .lblStatus.Caption = “Completed”
    .cmdAbort.SetFocus
    End With
    End If

    Liked by 1 person

    • Hi Hans,

      Changing the caption at the end of the loop is a great idea. I have incorporated it into my code as well, thanks.

      However, the abort when complete option comes in handy in one situation. Assume you need to bring up the progress bar for a different set of actions, but want to change the title of the form, you can use use the option to close the form automatically, instead of having to wait for the user to close it.

      I chose to use ‘Abort’ as the caption because clicking it during the execution will ‘End’ the execution.

      Regards,
      Ejaz Ahmed

      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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s