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.
Set up the minimum and maximum values for Progressor:
Progressor Object Model
Returns or sets the background color of the form.
Returns or sets the background color of the button.
Returns or sets the text of the button. Default is
"Cancel". Read-write String.
Returns or sets the caption of the form. Default is
"Progress". Read-write String.
Returns the current percentage complete value.
Returns or sets the current value of the progress.
Returns or sets the fill color of the graph. Default is
Red. Read-write Long.
Returns or sets the color of the border of the graph.
Default is Black. Read-write Long.
Returns the Window Handle of the form. Read-only Long.
Returns the maximum value of the progress. Default is
100. Read-write Single.
Returns or sets the message text. This appears below the
"Percent Complete" text. Read-write String.
Returns or sets the minimum value of the progress.
Default is 0. Read-write Single.
Returns the Windows Handle of the parent of the form.
Indicates whether the graph border is visible. Default is
True. Read-write Boolean.
Indicates whether to display the "Percent Complete" text.
Default is True. Read-write Boolean.
Returns or sets the color of the text. Default is
Black. Read-write Long.
Returns or sets the title text displayed just above the
bar graph. Read-write String.
Decrements the current value by the specified Amount. If
Amount is missing, 1 is used.
Hides and unloads the form.
Hides the form.
Increments the current value by the specified Amount. If
Amount is missing, 1 is used.
Resets the values to their original values.
Sets the parent window of the form. If HWnd is less
than or equal to 0, the current foreground window is used.
Sets the progress of the graph to the specified percent.
Shows the form.
Raised when the value of the progress is changed.
Raised when the user clicks the Cancel button.
|If you want to display an "LED" type progress bar in Excel's StatusBar, see John Walkenbach's Developer Dowloands page http://j-walk.com/ss/excel/files/developer.htm 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".||