ProgressIndicator.vba

Excel VBA class for a Reusable Progress Indicator.

This is essentially a republish of Mathieu Guindon’s (aka Mat’s Mug) article https://rubberduckvba.wordpress.com/2018/01/12/progress-indicator/. So all credits go to him!

Because I couldn’t find a repository for this project yet, here now is one. The main reason for this repository is to bundle all improvements at one point.

Features

• modeless

• reusable

Prerequisites / Dependencies

Since this is an Excel VBA class you obviously need Microsoft Excel.

How to install / Getting started

You have two choices. Either you add the files to a project directly where you want to use it, or you create an AddIn which than can be used by every project, if needed. Nonetheless, the necessary steps to be able to prepare the usage of the progress indicator are the same.

Add the file(s) from the src folder to your project. Yes, it’s that simple.

If you need some more advice on how to do that, expand me.

1. Open Microsoft Excel.

2. Open the Visual Basic Editor (VBE) (Alt+F11).

3. Add the file(s) in the src folder to your VBA project.

   • With Rubberduck:

     1. Right-click on the project to which you want to add the file(s) in the “Code Explorer” (to show it press Ctrl+R) and click on Add › Existing Files…​.

     2. Select all files in the src folder and click on Open.

   • Without Rubberduck:

     1. Select all except the .frx files in the src folder in Windows File Explorer.

     2. Drag-and-drop them to the corresponding project in VBE’s “Project Explorer”.
        (To show it press Ctrl+R. Hit it twice if the Code Explorer shows up first.)

4. Check, if there are obvious errors by compiling the project (Debug › Compile ‹project name›).

5. Save the file/project.

   1. Be sure that the file/project you want to save is “active” in the VBE by checking, if its name is shown in VBE’s title bar.
      (If it’s not, open a (class) module of the corresponding project (and close it again).)

   2. Press the “Save” button (the disc symbol similar to 💾) in VBE’s toolbar.

   3. Check that the file (really) was saved by having a look at the “last modified date” of the (project) file in the Windows File Explorer.

Usage / Show it in action

To show the progress indicator in action, you may add the “worker” code to a project. It is located in the demo folder. It is a (slightly modified) dummy example given in the above mentioned article.

💡	Have a look at the collapsible block in the How to install / Getting started section.

If you just want to see the code for now, to get an impression on what to do, please expand me.

Option Explicit

Public Sub DoSomething()
    'comment the next line if you also want to see the values changing
    'on the worksheet. Of course this will take much longer then.
    Application.ScreenUpdating = False

    With ProgressIndicator.Create("DoWork", canCancel:=True)
        .Execute
    End With

    Application.ScreenUpdating = True
End Sub

Public Sub DoWork(ByVal progress As ProgressIndicator)
    Dim i As Long
    For i = 1 To 10000
        If ShouldCancel(progress) Then
            'here more complex worker code could rollback & cleanup
            Exit Sub
        End If
        ActiveSheet.Cells(1, 1) = i
        progress.Update i / 10000              'show only the bar
'        progress.UpdatePercent i / 10000       'show also percentage value
    Next
End Sub

Private Function ShouldCancel(ByVal progress As ProgressIndicator) As Boolean
    If progress.IsCancelRequested Then
        If MsgBox("Cancel this operation?", vbYesNo) = vbYes Then
            ShouldCancel = True
        Else
            progress.AbortCancellation
        End If
    End If
End Function

• If you added the worker code to the same project, where also the progress indicator source files are present, then it will immediately work, i.e. run the DoSomething procedure.

• If you added the worker code to another project, you first need to add a reference to the AddIn where the progress indicator source is stored in.

  If you don’t have a clue how to do that, expand me.

  I’ll abbreviate the AddIn which contains the progress indicator source “PI” for simplicity.

  • With Rubberduck:

    1. Right-click somewhere on the project in the Code Explorer and click on Add/Remove References…​.

    2. Switch to the Projects tab.

    3. Add the reference.

       1. Type (parts of) the library name in the search box until you see it in below (left) list or it is empty.

          • If the PI AddIn file name is in the lower left list box:

            1. Select the library in the (left) list and

            2. click on the ➨ button to add it to the project references.
               

          • If the the PI AddIn file name is not in the list:

            1. Click on the Browse…​ button.

            2. Browse to the folder where the PI AddIn is located.

            3. Select the PI AddIn and

            4. press the Open button.

    4. Click on the OK button to close the window.

  • Without Rubberduck:

    1. Open the Reference manager in the VBE (Tools › References…​).

       • If the PI AddIn project name is in the list of available references:

         1. Add a checkmark to the corresponding library.

       • If it’s not in the list:

         1. Click on the Browse…​ button.

         2. Most likely change the file extension filter in the lower right to Microsoft Excel Files (*.xlsm;*.xlam;*.xls)

         3. Browse to the folder where the PI AddIn is located.

         4. Select the PI AddIn and

         5. press the Open button.

    2. Click on the OK button to close the window.

    3. Save the file/project.

Running Tests

Yes, Unit Tests in Excel are possible. (For that you need to have the awesome Rubberduck AddIn installed (and enabled).)

Unfortunately Mathieu missed to add them to his code (shame on you 😇). And my skills aren’t good enough to know how to create automated tests/unit tests for this project. If you have an idea, I’d love to see it! Please add an issue or – even better – a pull request (see the Contributing section).

But of course one can manually test it, as I already stated in the previous section (Usage / Show it in action).

Used By

No public projects that I am aware of. But I use it for some non-public code.

Known issues and limitations

None that I am aware of.

Contributing

All contributions are highly welcome!!

If you are new to git/GitHub, please have a look at https://github.com/firstcontributions/first-contributions where you will find a lot of useful information for beginners.

I recently was pointed to https://www.conventionalcommits.org which sounds very promising. I will use them from now on too (and hopefully don’t forget it in a hurry).

FAQ

1. What are the '@... comments good for in the code?

   You should really have a look at the awesome Rubberduck project!

Similar Projects

At present I am aware of

• https://github.com/cristianbuse/Excel-VBA-ProgressBar

which is very impressive. You should really check it out.

If you know more similar projects, please let me know.

License

MIT