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.

image from book
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.

image from book
 

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.

Creating a standalone progress indicator

This section describes how to set up a standalone progress indicator - that is, one that is not initiated by displaying a UserForm - to display the progress of a macro. The macro simply clears the worksheet and writes 20,000 random numbers to a range of cells:

 Sub GenerateRandomNumbers() '   Inserts random numbers on the active worksheet     Const RowMax As Integer = 500     Const ColMax As Integer = 40     Dim r As Integer, c As Integer     If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub     Cells.Clear     For r = 1 To RowMax         For c = 1 To ColMax             Cells(r, c) = Int(Rnd * 1000)         Next c     Next r End Sub 

After you make a few modifications to this macro (described below), the UserForm, shown in Figure 15-3, displays the progress.

image from book
Figure 15-3: A UserForm displays the progress of a macro.
CD-ROM  

This example, named image from book  progress indicator1.xlsm , is available on the companion CD-ROM.

BUILDING THE STANDALONE PROGRESS INDICATOR USERFORM

Follow these steps to create the UserForm that will be used to display the progress of your task:

  1. Insert a new UserForm and change its Caption property setting to Progress .

  2. Add a Frame control and name it FrameProgress .

  3. Add a Label control inside the Frame and name it LabelProgress . Remove the label's caption and make its background color ( BackColor property) something that will stand out. The label's size and placement don't matter for now.

  4. Add another label above the frame to describe what's going on (optional). In this example, the label reads, Entering random numbers

  5. Adjust the UserForm and controls so that they look something like Figure 15-4.

image from book
Figure 15-4: This UserForm will serve as a progress indicator.

You can, of course, apply any other type of formatting to the controls. For example, I changed the SpecialEffect property for the Frame control to make it "sunken."

CREATING THE EVENT HANDLER PROCEDURES FOR THE STANDALONE PROGRESS INDICATOR

The trick here involves running a procedure automatically when the UserForm is displayed. One option is to use the Initialize event. However, this event occurs before the UserForm is actually displayed, so it's not appropriate. The Activate event, on the other hand, is triggered when the UserForm is displayed, so it's perfect for this application.

Insert the following procedure in the code window for the UserForm. This procedure simply calls a procedure named GenerateRandomNumbers when the UserForm is displayed. This procedure, which is stored in a VBA module, is the actual macro that runs while the progress indicator is displayed.

 Private Sub UserForm_Activate()     Call GenerateRandomNumbers End Sub 

The modified version of the GenerateRandomNumber procedure (which was presented earlier) follows . Notice that additional code keeps track of the progress and stores it in a variable named PctDone .

 Sub GenerateRandomNumbers() '   Inserts random numbers on the active worksheet     Dim Counter As Integer     Const RowMax As Integer = 500     Const ColMax As Integer = 40     Dim r As Integer, c As Integer     Dim PctDone As Single     If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub     Cells.Clear     Counter = 1     For r = 1 To RowMax         For c = 1 To ColMax             Cells(r, c) = Int(Rnd * 1000)             Counter = Counter + 1         Next c         PctDone = Counter / (RowMax * ColMax)         Call UpdateProgress(PctDone)     Next r     Unload UserForm1 End Sub 

The GenerateRandomNumbers procedure contains two loops . Within the inner loop is a call to the UpdateProgress procedure, which takes one argument (the PctDone variable, which represents the progress of the macro). PctDone will contain a value between and 100 .

 Sub UpdateProgress(Pct)     With UserForm1       .FrameProgress.Caption = Format(Pct, "0%")       .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)       .Repaint     End With End Sub 

CREATING THE START-UP PROCEDURE FOR A STANDALONE PROGRESS INDICATOR

All that's missing is a procedure to display the UserForm. Enter the following procedure in a VBA module:

 Sub ShowUserForm()     With UserForm1         .LabelProgress.Width = 0         .Show     End With End Sub 
Tip  

An additional accoutrement is to make the progress bar color match the workbook's current theme. To do so, just add this statement to the ShowUserForm procedure:

 .LabelProgress.BackColor = ActiveWorkbook.Theme. _         ThemeColorScheme.Colors(msoThemeAccent1) 

HOW THE STANDALONE PROGRESS INDICATOR WORKS

When you execute the ShowUserForm procedure, the Label object's width is set to . Then the Show method of the UserForm1 object displays the UserForm (which is the progress indicator). When the UserForm is displayed, its Activate event is triggered, which executes the GenerateRandomNumbers procedure. The GenerateRandomNumbers procedure contains code that calls the UpdateProgress procedure every time the r loop counter variable changes. Notice that the UpdateProgress procedure uses the Repaint method of the UserForm object. Without this statement, the changes to the label would not be updated. Before the GenerateRandomNumbers procedure ends, the last statement unloads the UserForm.

To customize this technique, you need to figure out how to determine the percentage completed and assign it to the PctDone variable. This will vary, depending on your application. If your code runs in a loop (as in this example), determining the percentage completed is easy. If your code is not in a loop, you might need to estimate the progress completed at various points in your code.

Showing a progress indicator by using a MultiPage control

In the preceding example, the macro was not initiated by a UserForm. In many cases, your lengthy macro is kicked off when the user clicks the OK button on a UserForm. The technique that I describe in this section is a better solution and assumes the following:

  • Your project is completed and debugged .

  • Your project uses a UserForm (without a MultiPage control) to initiate a lengthy macro.

  • You have a way to gauge the progress of your macro.

CD-ROM  

The companion CD-ROM contains an example that demonstrates this technique. The file is named image from book  progress indicator2.xlsm .

Like the previous example, this one enters random numbers into a worksheet. The difference here is that the application contains a UserForm that allows the user to specify the number of rows and columns for the random numbers (see Figure 15-5).

image from book
Figure 15-5: The user specifies the number of rows and columns for the random numbers.

MODIFYING YOUR USERFORM FOR A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL

This step assumes that you have a UserForm all set up. You'll add a MultiPage control. The first page of the MultiPage control will contain all your original UserForm controls. The second page will contain the controls that display the progress indicator. When the macro begins executing, VBA code will change the Value property of the MultiPage control. This will effectively hide the original controls and display the progress indicator.

The first step is to add a MultiPage control to your UserForm. Then move all the existing controls on the UserForm and paste them to Page1 of the MultiPage control.

Next, activate Page2 of the MultiPage control and set it up as shown in Figure 15-6. This is essentially the same combination of controls used in the example in the previous section.

  1. Add a Frame control and name it FrameProgress .

  2. Add a Label control inside the Frame and name it LabelProgress . Remove the label's caption and make its background color red.

  3. Add another label to describe what's going on (optional).

  4. Next, activate the MultiPage control itself (not a page on the control) and set its Style property to 2 “ fmTabStyleNone . (This will hide the tabs.) You'll probably need to adjust the size of the MultiPage control to account for the fact that the tabs are not displayed.

image from book
Figure 15-6: Page2 of the MultiPage control will display the progress indicator.
Tip  

The easiest way to select the MultiPage control when the tabs are hidden is to use the drop-down list in the Properties window.

INSERTING THE UPDATEPROGRESS PROCEDURE FOR A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL

Insert the following procedure in the code module for the UserForm:

 Sub UpdateProgress(Pct)     With UserForm1       .FrameProgress.Caption = Format(Pct, "0%")       .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)       .Repaint     End With End Sub 

The UpdateProgress procedure is called from the macro that's executed when the user clicks the OK button, and it performs the updating of the progress indicator.

MODIFYING YOUR PROCEDURE FOR A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL

You need to modify the procedure that is executed when the user clicks the OK Button - the Click event handler procedure for the button named OKButton_Click . First, insert the following statement at the top of your procedure:

 MultiPage1.Value = 1 

This statement activates Page2 of the MultiPage control (the page that displays the progress indicator).

In the next step, you're pretty much on your own. You need to write code to calculate the percent completed and assign this value to a variable named PctDone . Most likely, this calculation will be performed inside of a loop. Then insert the following statement, which will update the progress indicator:

 Call UpdateProgress(PctDone) 

HOW A PROGRESS INDICATOR WITH A MULTIPAGE CONTROL WORKS

This technique is very straightforward and, as you've seen, it involves only one UserForm. The code switches pages of the MultiPage control and converts your normal dialog box into a progress indicator. Because the MultiPage tabs are hidden, it doesn't even resemble a MultiPage control.

Showing a progress indicator without using a MultiPage control

The example in this section is similar to the example in the preceding section. However, this technique is simpler because it doesn't use a MultiPage control. Rather, the progress indicator is stored at the bottom of the UserForm - but the UserForm's height is reduced so that the progress indicator controls are not visible. When it's time to display the progress indicator, the UserForm's height is increased, which makes the progress indicator visible.

CD-ROM  

The companion CD-ROM contains an example that demonstrates this technique. The file is named image from book  progress indicator3.xlsm .

Figure 15-7 shows the UserForm in the VBE. The Height property of the UserForm is 172 . However, before the UserForm is displayed, VBA code changes the Height to 124 (which means the progress indicator controls are not visible to the user). When the user clicks OK, VBA code changes the Height property to 172 with the following statement:

 Me.Height = 172 
image from book
Figure 15-7: The progress indicator will be hidden by reducing the height of the UserForm.

Figure 15-8 shows the UserForm with the progress indicator section unhidden.

image from book
Figure 15-8: The progress indicator in action.



Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net