Displaying a Progress Indicator

One of the most common requests among Excel developers involves progress indicators. A progress indicator is a graphical thermometer-type display that shows the progress of a task, such as a lengthy macro.

Displaying a progress indicator is relatively easy. In this section, I describe how to create three types of progress indicators for:

■ A macro that's not initiated by a UserForm (a standalone progress indicator).

■ A macro that is initiated by a UserForm. In this case, the UserForm uses a MultiPage control that displays the progress indicator while the macro is running.

■ A macro that is initiated by a UserForm. In this case, the UserForm increases in height while the macro is running, and the progress indicator appears at the bottom of the dialog box.

Displaying Progress in the Status Bar

A simple way to display the progress of a macro is to use Excel's status bar. The advantage is that it's very easy to program. However, the disadvantage is that most users aren't accustomed to watching the status bar and prefer a more visual display.

To write text to the status bar, use a statement such as

Application.StatusBar = "Please wait..."

You can, of course, update the status bar while your macro progresses. For example, if you have a variable named Pct that represents the percent completed, you can write code that periodically executes a statement such as this:

Application.StatusBar = "Processing... " & Pct & "% Completed"

When your macro finishes, you must reset the status bar to its normal state with the following statement:

Application.StatusBar = False

If you don't reset the status bar, the final message will continue to display._

Using a progress indicator requires that you are (somehow) able to gauge how far along your macro might be in completing its given task. How you do this will vary, depending on the macro. For example, if your macro writes data to cells and you know the number of cells that will be written to, it's a simple matter to write code that calculates the percent completed. Even if you can't accurately gauge the progress of a macro, it's a good idea to give the user some indication that the macro is still running and Excel hasn't crashed.

Caution A progress indicator will slow down your macro a bit because of the extra overhead of having to update it. If speed is absolutely critical, you might prefer to forgo using a progress indicator.

0 0

Post a comment