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.

How to control Excel’s Status Bar?

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:

Sub StatusBarExample()

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

'Initilaizing Variables
lngNumberOfTasks = 1000

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

End Sub

How to display a progress bar?

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

How to use Struggling To Excel’s Progress bar in your projects?

So here is what you need to do, to use this in your code. Download the workbook from the Downloads section below. 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:

Sub TestTheBar()

'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

    'You can add your code 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

End Sub

Note that you can press the Abort button anytime to stop the execution. I urge you to use them in your projects and let me know if you like it.


Update

I have released an update to this 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.

Check out First Class Progress Bar for all your Macros for the updated version


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. If you are a multi-display user, test it and let me know how it works.


Bonus

How to check whether a form is open?

And the workbook also has a simple user defined function that you can use to check if a form has been already opened.

Function isFormOpen(ByVal FormName As String) As Boolean

'Declare Function level Objects
Dim ufForm As Object

'Set the Function to False
isFormOpen = False

'Loop through all the open forms
For Each ufForm In VBA.UserForms
    'Check the form names
    If ufForm.Name = FormName Then
        'if the form is open, set the function value to True
        isFormOpen = True
        'and exit the loop
        Exit For
    End If
Next ufForm

End Function
Categories: Applications, MacrosTags: , , , , , ,

74 comments

  1. Great work, Ejaz

    Liked by 1 person

  2. Thanks Sajjad bhai. You were the inspiration behind this post. Let me know if you end up using this in your applications.

    Like

  3. I am glad you like it Pavithra. Let me know if you run into any bugs.

    Like

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

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

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

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

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

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

    Like

  10. What did you do exactly? Let me see if I can help you out! Worst case, send your model over to strugglingroexcel@outlook.com, I shall try to fix it.

    Like

  11. Thanks so much for the kind offer. Got it working late last night, very easy once I got my head around it – had to take the loop out. Nice sub routine – thanks for sharing adds a nice looking touch and lets the user know something is taking place. Easy enough even a beginner can apply it.

    Liked by 1 person

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

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

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

  15. Did you try entering the break mode and executing the code line by line? That usually helps.

    Like

  16. I will try that now, thanks for the suggestion

    Liked by 1 person

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

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

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

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

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

  22. I put your controls into a frame, and changed from form controls to frame controls, it works like a champ! Thanks

    Like

  23. Hi Brinerd! It is awesome that it worked. But, I am confused. I don’t understand how putting stuff into a frame would help. Would you explain to me how the frame solved the problem you had?

    Like

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

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

  26. Fantastic example which saved me a lot of time and anguish! Thanks for sharing and allowing us to use this!!!

    Liked by 1 person

  27. Thanks a lot for sharing this piece of code this has helped me a lot. Thanks again.

    Like

  28. Can you send to me via e-mail – I cannot access drop-box…

    Liked by 1 person

  29. Is dropbox blocked in your network? Or is the link not working?

    I’d be happy to send this over. To which id should I send it to?

    Like

  30. dropbox is blocked – can you send to amy.jones@goodrich.com?

    Like

  31. I sent you a mail with the file. I also attached another file to the mail, which is an updated version of this Progress Bar that is easier to use.

    Check the following link out, for a post on how to use the other file: https://strugglingtoexcel.wordpress.com/2014/09/22/class-progress-bar-excel-vba/

    Like

  32. Hi. Thanks for sharing. Can you send me this file to my email address, please?

    Liked by 1 person

  33. This is brilliant!!! Thank you for this gem and smart manipulation of userform to make this work!

    Like

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

  35. Perfect! Thank you so much good sir!

    Like

  36. Forever at your service my lady.

    I also recommend the class based progress bar, for it is easier to use. This post helps you understand how to create one, while the other is far more easier to use in your code.

    https://strugglingtoexcel.wordpress.com/2014/09/22/class-progress-bar-excel-vba/

    Like

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

  38. I am happy that you liked my work. And thanks for noticing how carefully I considered the abort function.

    I request you to try out my class based progress bar too.

    Like

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

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

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

  42. Try adding it before the unload statement.

    Like

  43. Nope. Same problem.

    Like

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

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

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

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: