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.

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

 

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