Excel Optimize

One of the most common Excel programming questions I hear is “How can I make a UserForm display the progress of a lengthy macro?”
Answer: Use a UserForm to create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks — which I’m about to show you.

Creating the progress indicator dialog box

The first step is to create your UserForm. In this example, the dialog box displays the progress while a macro inserts random numbers into 50 columns and 100 rows of the active worksheet. To create the dialog box, follow these steps:
1. Activate the VBE and insert a new UserForm.
2. Change the UserForm’s caption to Progress.
3. Add a Frame object and set the following properties:

• Property Value
• Caption 0%
• Name FrameProgress
• SpecialEffect 2 — fmSpecialEffectSunken
• Width 204
• Height 28

4. Add a Label object inside the Frame and set the following properties:

• Property Value
• Name LabelProgress
• BackColor &H000000FF& (red)
• Caption (no caption)
• SpecialEffect 1 — fmSpecialEffectRaised
• Width 20
• Height 13
• Top 5
• Left 2

5. Add another Label above the frame and change its caption to Entering random numbers. . .. The UserForm should resemble Figure 18-10.

The procedures
This example uses two procedures and a module level variable.
The module level variable: Located in a VBA module. This variable
holds the copy of the userform:

Dim ProgressIndicator as UserForm1
EnterRandomNumbers: It does all the work and is executed when the
UserForm is shown. Notice that it calls the UpdateProgress procedure,
which updates the progress indicator in the dialog box:
Sub EnterRandomNumbers ()
‘ Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single
‘ Create a copy of the form in a variable
Set ProgressIndicator = New UserForm1
‘ Show ProgressIndicator in modeless state
ProgressIndicator.Show vbModeless
If TypeName(ActiveSheet) <> “Worksheet” Then
Unload ProgressIndicator
Exit Sub
End If
‘ Enter the random numbers
Cells.Clear
Counter = 1
RowMax = 200
ColMax = 50
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 ProgressIndicator
Set ProgressIndicator = Nothing
End Sub

UpdateProgress: This procedure accepts one argument and updates the progress indicator in the dialog box:

Sub UpdateProgress(pct)
With ProgressIndicator
.FrameProgress.Caption = Format(pct, “0%”)
.LabelProgress.Width = pct * (.FrameProgress _
.Width – 10)
End With
‘ The DoEvents statement is responsible for the form
updating
DoEvents
End Sub

How this example works
When the EnterRandomNumbers procedure is executed, it loads a copy of Userform1 into the module variable named ProgressIndicator. Then it sets the width of the LabelProgress label to 0, and displays the UserForm in modeless state (so the code will continue to run).
The EnterRandomNumber procedure checks the active sheet. If it’s not a worksheet, the UserForm (ProgressIndicator) is closed and the procedure ends with no action. If the active sheet is a worksheet, the procedure does the following:

1. Erases all cells on the active worksheet.
2. Loops through the rows and columns (specified by the RowMax and ColMax variables) and inserts a random number.
3. Increments the Counter variable and calculates the percentage completed (which is stored in the PctDone variable).
4. Calls the UpdateProgress procedure, which displays the percentage completed by changing the width of the LabelProgress label and updating the caption of the frame control.
5. Last, the UserForm is unloaded.

If you adapt this technique for your own use, you need to figure out how to determine the macro’s progress, which varies, depending on your macro. Then call the UpdateProgress procedure at periodic intervals while your macro is executing.