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.
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:
- 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:
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
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
Great work, Ejaz
Thanks Sajjad bhai. You were the inspiration behind this post. Let me know if you end up using this in your applications.
very nice . thank u
I am glad you like it Pavithra. Let me know if you run into any bugs.
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
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
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
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.
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
Pingback: VBA - Progress Bars
New to VBA. Searched, found it, tried to add it – couldn’t get it to work – obviously doing something wrong.
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.
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.
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
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 ?
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
Did you try entering the break mode and executing the code line by line? That usually helps.
I will try that now, thanks for the suggestion
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
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
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.
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.
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
I put your controls into a frame, and changed from form controls to frame controls, it works like a champ! Thanks
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?
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.
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
Fantastic example which saved me a lot of time and anguish! Thanks for sharing and allowing us to use this!!!
My Pleasure!
Thanks a lot for sharing this piece of code this has helped me a lot. Thanks again.
Can you send to me via e-mail – I cannot access drop-box…
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?
dropbox is blocked – can you send to amy.jones@goodrich.com?
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: http://strugglingtoexcel.wordpress.com/2014/09/22/class-progress-bar-excel-vba/
Hi. Thanks for sharing. Can you send me this file to my email address, please?
Done!
This is brilliant!!! Thank you for this gem and smart manipulation of userform to make this work!
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.
Perfect! Thank you so much good sir!
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/
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.
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.
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?
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.
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?
Try adding it before the unload statement.
Nope. Same problem.
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!
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
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!
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”.
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?
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
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″)
…
Hello there! It’s master-coding sir! Thank you for sharing this masterpiece 😉
You are too kind. And you are welcome!
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
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.
awsome
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.
Does this happen when you turn off screen 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?
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
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
Pingback: Progress Bar Not Updating
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
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
Hello Ejaz, I am using call functions. i.e without loop. I assigned 5 tasks totally. After completing all the tasks. The code is continue running from somewhere middle of my macro and throwing error. Why it is not stopping in the last task macro?
Please check out my post on an updated progress bar. It’s much easier to use.
I think I commented this in one of the very first posts, you should include the code to at least center the form on the active monitor if you have more than one monitor.
Simple addition the last two lines before End With
‘Set all the Properties that need to be set before the
‘ProgresBar is Shown
With MainBar
.Title = “Main Bar”
.ExcelStatusBar = True
.StartColour = rgbGreen
.EndColour = rgbRed
.TotalActions = TotalCount
.Left = (Application.Width / 2) – (.Width / 2)
.Top = (Application.Height / 1) – (.Height / 2)
End With
Regards,
Hans
Thank you Hans. I will add this to my code.
Ejaz Are you available for hire to implement progress bar in my Excel project.
Hello, I am getting an object required Error Code 424… This is my current version of your code implemented with mine. Is there any change you would know what is wrong? Thank you for all the help!
Sub TestTheBar()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim StartTime As Double
Dim SecondsElapsed As Double
‘Declaring Sub Level Variables
Dim lngCounter As Long
Dim lngNumberOfTasks As Long
‘Initilaizing Variables
lngNumberOfTasks = 23
‘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
‘Remember time when macro starts
StartTime = Timer
Close_Source_Files
Clear_Old_Data
Open_Source_Files
Get_B_Data
Get_O_Data
Get_S_Data
Get_C_Data
Close_Source_Files
Eliminate_Deleted_Os
Format_Dates_In_All_Data
Extend_Formulas
Adjust_O_Pivot
Adjust_Percentage_Pivot
Adjust_Normalize_Pivot
‘RefreshPivotShowMessageComplete()
ThisWorkbook.RefreshAll
Sheets(“All_Data”).Select
[A1].Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.CutCopyMode = False
‘Determine how many seconds code took to run
SecondsElapsed = Round(Timer – StartTime, 2)
‘Notify user in seconds
MsgBox “Data Pull Successful. Time Taken: ” & SecondsElapsed & ” Seconds”, vbInformation
‘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
I don’t think you are using the for loop correctly. It looks like you don’t need a loop at all. Are you sure you need a loop in your macro?