Progress Bar for all your excel Applications

Progress Bar

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
Advertisements

74 Comments

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

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

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

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

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

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

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

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

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

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

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

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

    Like

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

        Like

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

    Like

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

      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