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.
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:
- 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.
- TotalActions – This denotes the total number of actions you need to perform.
- 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.
- 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.
- 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.
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
LikeLike
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
LikeLike
Pingback: Progress Bar Not Updating
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?
LikeLike
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
LikeLike
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
LikeLiked by 1 person
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.
LikeLike
Does this happen when you turn off screen updating?
LikeLike
awsome
LikeLiked by 1 person
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
LikeLike
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.
LikeLike
Hello there! It’s master-coding sir! Thank you for sharing this masterpiece 😉
LikeLiked by 1 person
You are too kind. And you are welcome!
LikeLike
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
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
LikeLiked 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″)
…
LikeLike
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”.
LikeLiked 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?
LikeLike
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?
LikeLike
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.
LikeLike
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?
LikeLike
Try adding it before the unload statement.
LikeLike
Nope. Same problem.
LikeLike
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!
LikeLiked 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
LikeLike
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!
LikeLike
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.
LikeLiked by 1 person
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.
LikeLike
Perfect! Thank you so much good sir!
LikeLike
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/
LikeLike
This is brilliant!!! Thank you for this gem and smart manipulation of userform to make this work!
LikeLike
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.
LikeLike
Hi. Thanks for sharing. Can you send me this file to my email address, please?
LikeLiked by 1 person
Done!
LikeLike
Can you send to me via e-mail – I cannot access drop-box…
LikeLiked by 1 person
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?
LikeLike
dropbox is blocked – can you send to amy.jones@goodrich.com?
LikeLike
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/
LikeLike
Pingback: First Class Progress Bar for all your Macros #Excel #VBA | Struggling To Excel
Thanks a lot for sharing this piece of code this has helped me a lot. Thanks again.
LikeLike
Fantastic example which saved me a lot of time and anguish! Thanks for sharing and allowing us to use this!!!
LikeLiked by 1 person
My Pleasure!
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
I put your controls into a frame, and changed from form controls to frame controls, it works like a champ! Thanks
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLiked by 1 person
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
LikeLiked 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 ?
LikeLike
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
LikeLiked by 1 person
Did you try entering the break mode and executing the code line by line? That usually helps.
LikeLike
I will try that now, thanks for the suggestion
LikeLiked 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
LikeLike
New to VBA. Searched, found it, tried to add it – couldn’t get it to work – obviously doing something wrong.
LikeLike
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.
LikeLike
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.
LikeLiked by 1 person
Pingback: VBA - Progress Bars
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
LikeLike
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.
LikeLike
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
LikeLiked by 1 person
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
LikeLiked 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
LikeLike
very nice . thank u
LikeLiked by 1 person
I am glad you like it Pavithra. Let me know if you run into any bugs.
LikeLike
Great work, Ejaz
LikeLiked by 1 person
Thanks Sajjad bhai. You were the inspiration behind this post. Let me know if you end up using this in your applications.
LikeLike