Pearson Software Consulting Services

    Progress Indicator For VBA  

         NOTE: There is a bug in the ProgressReporter DLL, so I have removed it from the web site until I can fix the bug.

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:



  The ProgressAlerter library exposes a single object called Progressor. This object has properties, methods, and events that you use to control the progress window.  First, declare a variable of type Progressor and set it to a New instance of the Progressor class:

Dim Prog As ProgressReporter.Progressor
Set Prog = New Progressor

Next, you have to tell Progressor what its parent window is. You use the SetParentWindow method.  In most cases, you can simply pass in 0.  See the SetParentWindow method description below for more details. 

Prog.SetParentWindow  0

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:


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


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
        For N = 1 To 1000
            ' your code here
            .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.

  Property Description  
BackColor Returns or sets the background color of the form.  Read-write Long.
  ButtonColor Returns or sets the background color of the button.  Read-write Long.
  ButtonText Returns or sets the text of the button.  Default is "Cancel".  Read-write String.
  Caption Returns or sets the caption of the form. Default is "Progress". Read-write String.
  CurrentPercent Returns the current percentage complete value.  Read-only Single.
  CurrentValue Returns or sets the current value of the progress.  Read-write Single.
  FillColor Returns or sets the fill color of the graph. Default is Red. Read-write Long.
  FrameColor Returns or sets the color of the border of the graph. Default is Black. Read-write Long.
  HWnd Returns the Window Handle of the form. Read-only Long.
  MaximumValue Returns the maximum value of the progress. Default is 100. Read-write Single.
  Message Returns or sets the message text. This appears below the "Percent Complete" text.  Read-write String.
  MinimumValue Returns or sets the minimum value of the progress. Default is 0. Read-write Single.
  ParentHWnd Returns the Windows Handle of the parent of the form.  Read-only Long.
  ShowFrame Indicates whether the graph border is visible. Default is True. Read-write Boolean.
  ShowPercent Indicates whether to display the "Percent Complete" text. Default is True. Read-write Boolean.
  TextColor Returns or sets the color of the text.  Default is Black. Read-write Long.
  Title Returns or sets the title text displayed just above the bar graph.  Read-write String.
  Decrement([Amount]) Decrements the current value by the specified Amount. If Amount is missing, 1 is used.
  Destroy Hides and unloads the form.
  Hide Hides the form.
  Increment([Amount]) Increments the current value by the specified Amount. If Amount is missing, 1 is used.
  Reset    Resets the values to their original values.
  SetParentWindow(HWnd) Sets the parent window of the form.  If HWnd is less than or equal to 0, the current foreground window is used.
  SetToPercent(Percent) Sets the progress of the graph to the specified percent.
  Show    Shows the form.
  Tick Raised when the value of the progress is changed.
  UserCancel Raised when the user clicks the Cancel button.  Cancellable.
         If you want to display an "LED" type progress bar in Excel's StatusBar, see John Walkenbach's Developer Dowloands page for a technique developed by David Wiseman, John Walkenbach, Jim Rech, and Ole Erlandsen. See the section entitled "Control the LED Display in the Status Bar".


Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers

Copyright 1997-2007  Charles H. Pearson