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.
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. |
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.
CD-ROM | This example, named progress indicator1.xlsm , is available on the companion CD-ROM. |
Follow these steps to create the UserForm that will be used to display the progress of your task:
Insert a new UserForm and change its Caption property setting to Progress .
Add a Frame control and name it FrameProgress .
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.
Add another label above the frame to describe what's going on (optional). In this example, the label reads, Entering random numbers
Adjust the UserForm and controls so that they look something like Figure 15-4.
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."
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
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) |
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.
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 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).
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.
Add a Frame control and name it FrameProgress .
Add a Label control inside the Frame and name it LabelProgress . Remove the label's caption and make its background color red.
Add another label to describe what's going on (optional).
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.
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. |
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.
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)
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.
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 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
Figure 15-8 shows the UserForm with the progress indicator section unhidden.