While a long procedure is executing, it is often desirable to indicate the progress to the user. The simplest way is to use the Excel's Status Bar area to display text messages, but if you want a graphical representation, you can use the ProgressReporter library described here.  ProgressReporter will display a simple window in Excel, and update a simple bar graph to display your procedures progress.  Of course, you'll have to tell ProgressAlerter how far along you are in your procedure.

When you call ProgressReport, it will display the window shown below:

Set up the minimum and maximum values for Progressor:

Prog.MinimumValue = 0
Prog.MaximumValue = 100

Finally, show the window and call the Increment method periodically in your procedure to increment the progress bar:

Prog.Show
Prog.Increment

When your procedure is finished, you can hide the window using the Hide method:

Prog.Hide
 

Download ProgessReporter

Click here to download a zip file containing the ProgressReporter DLL file.
 

Installing ProgessReporter

Unzip and save the file to some directory.  Then, go to the Windows Start menu, choose Run, and enter the following command:
     RegSvr32 "C:\folder\ProgressReporter.dll"

Change the folder to the complete folder name in which you saved the file. Then, in your VBA Project, go to the Tools menu, choose References, and put a check next to "ProgressReporter".

Example Usage:

The following is some example code that uses Progressor.

Dim Prog As ProgressReporter.Progressor
Sub Test()
    Dim N As Long

    If Prog Is Nothing Then
        Set Prog = New ProgressReporter.Progressor
    End If
    With Prog
        .MinimumValue = 0
        .MaximumValue = 1000
        .SetParentWindow 0
        .CurrentValue = 0
        .Show
        For N = 1 To 1000
            '
            ' your code here
            '
            DoEvents
            .Increment 1
        Next N
    End With
End Sub
 

Performance Note

There is a known problem when Progressor is run in extremely tight loops which causes message flooding. This occurs when you call Increment or Decrement repeatedly with no intervening code.   This floods Progressor's message queue and does not give it enough time to properly draw the progress bar.  You should not use Progressor in loops that don't execute other code.   The DoEvents statement help the program run smoothly on Windows 95, 98, and ME systems, but is generally not necessary on Windows NT, 2000, and XP systems.

Progressor Object Model

The following are the properties, methods, and events of the Progressor object.