Word Hacks [Electronic resources]

Hack 65 Show Progress from VBA

When macros take a long time to run, people get nervous. Did it crash? How much longer will it take? Do I have time to run to the bathroom? Relax. This hack shows you two ways to create a macro progress bar using VBA.

Before adding a full-fledged progress bar to your macro, consider whether something more subtle might be effective enough to keep the macro user informed. Within a macro, you can use the StatusBar property to display text in Word's status barthe little area at the bottom of the window that displays the current page, line count, and so forth.

The following macro displays a personalized message in the status bar. Put the macro in the template of your choice [Hack #50] and run it from ToolsMacroMacros:

Sub SayHello( )
StatusBar = "Hello, " & Application.UserName & _
". My that's a nice shirt you're wearing." 
End Sub

You can take a tip from Word, which often displays messages in the status bar (e.g., when you save a document), and use the status bar as a means of communication from within a macro.

For example, the following macro uses a For Each loop [Hack #66] to highlight any paragraph set to outline Level 2 that contains more than 10 words. As it completes this task, it prints the text of the paragraph to the status bar:

Sub HighlightLongHeadings( )
Dim para As Paragraph
For Each para In ActiveDocument.Paragraphs
StatusBar = "Checking: " & para.Range.Text
If para.OutlineLevel = wdOutlineLevel2 Then
If para.Range.Words.Count > 10 Then
para.Range.HighlightColorIndex = wdBrightGreen
End If
End If
Next para
StatusBar = "
End Sub

This solution usually provides enough visual feedback to keep users assured that the macro's still hard at work and that Word hasn't crashed.

If you want a more specific, or just less subtle, feedback method, you can create a custom progress bar that appears in its own dialog box while your macro runs. The following sections describe two ways to create your own progress bar using VBA. Both adapt the HighlightLongHeadings macro shown above.

7.10.1 Continuous Progress

The first technique combines the code for the progress bar with the code for the macro.

To keep the example simple, you should put this code in your Normal template. Select ToolsMacroVisual Basic Editor, choose Normal in the Project Explorer (near the top left of the window), and then select InsertUserForm. Next, choose ViewToolbox to display the Toolbox (it may already be showing). Select the Label control (the one with the "A" on it). Now move your cursor to the UserForm and drag the cursor to create a new label, like the one shown in Figure 7-9. Try to position the top-left corner of the label near the top left of the UserForm.

Next, select ViewCode and insert the following code:

Private Sub UserForm_Activate( )
Dim lParaCount As Long
Dim i As Integer
Dim para As Paragraph
Dim lMaxProgressBarWidth As Long
Dim sIncrement As Single
' Resize the UserForm
Me.Width = 240
Me.Height = 120
' Resize the label
Me.Label1.Height = 50
Me.Label1.Caption = "
Me.Label1.Width = 0
Me.Label1.BackColor = wdColorBlue
lMaxProgressBarWidth = 200
lParaCount = ActiveDocument.Paragraphs.Count
sIncrement = lMaxProgressBarWidth / lParaCount
i = 1
For Each para In ActiveDocument.Paragraphs
Me.Label1.Width = Format(Me.Label1.Width + sIncrement, "#.##")
Me.Caption = "Checking " & CStr(i) & " of " & CStr(lParaCount)
Me.Repaint
If para.OutlineLevel = wdOutlineLevel2 Then
If para.Range.Words.Count > 10 Then
para.Range.HighlightColorIndex = wdBrightGreen
End If
End If
i = i + 1
Next para
Unload Me
End Sub

From the Project Explorer, select one of the code modules in Normal, as shown in Figure 7-10. If you don't have any code modules in Normal, select InsertModule to create one.

In the code module you've selected, insert the following code:

Sub HighlightLongHeadings( )
UserForm1.Show
End Sub

Now select FileClose and Return to Microsoft Word. To run the macro, select ToolsMacroMacros and choose HighlightLongHeadings. When you run the macro, you'll see a progress bar like the one shown in Figure 7-11.

One of the lines in the UserForm code deserves a closer look:

Format(Me.Label1.Width + sIncrement, "#.##")

The variable sIncrement is the final width of the progress bar divided by the total number of paragraphs in the document. As the macro visits each paragraph in the document, the width of the bar increases by the value of sIncrement. Since the maximum width of the bar in this example is 200 pixels (as defined in the variable lMaxProgressBarWidth), if there are 10 paragraphs in the document, the width of the bar will increase by 20 pixels as each paragraph is examined.

If there are hundreds or thousands of paragraphs in a document, the value of sIncrement can become quite smallsmaller than the measurements UserForms are designed to handle. When that happens, VBA will round the number according to its own internal rounding rules, which can cause the width of the progress bar to eventually exceed the width of the UserForm. However, if you use the Format function, the increment amount will be rounded more precisely, keeping it confined to the boundaries of the UserForm.

7.10.2 Incremental Progress

One drawback to the technique described in the previous section is that the code for the progress bar is mixed with the code used to modify the document. To create another macro that displays a similar progress bar, you'd need to create another, similar UserForm. But by separating the code for the progress bar from the code that works on the document, you can reuse your progress bar in a variety of situations.

This section shows you how to create a dialog that reports the progress of a macro as a percentage, in increments of 10%, as shown in Figure 7-12. You can use this same progress bar from within any macro whose progress can be translated into a percentage.

To keep the example simple, you should put this code in your Normal template. Select ToolsMacroVisual Basic Editor, choose Normal in the Project Explorer (near the top left of the window), and then select InsertUserForm. Next, choose ViewToolbox to display the Toolbox (it may already be showing).

On the Toolbox, select the Frame control (the box with "xyz" at the top), and then draw a single frame on your blank UserForm. With the frame selected, go to the Properties window. Change the frame's height to 30 and its width to 18 and set its Visible property to False. Then delete the frame's caption and change its background color to blue, as shown in Figure 7-13.

In the listbox at the top of the Properties window, select UserForm1 instead of Frame1, and then change the ShowModal property to False. While in the Properties window, change the name of the UserForm to IncrementalProgress.

Now go back to the UserForm itself and select the frame. Choose EditCopy, and then paste the frame nine times. Align the 10 frames as best you can in a single row. While holding down the Ctrl key, select all of the frames. Then select FormatAlign and align the centers and tops of all the frames.

Next, select the Label control from the Toolbox (the one with the "A" on it) and draw a label underneath the frames, as shown in Figure 7-14. From the Properties window, delete the label's caption.

With this method, you display the dialog when your macro starts, then periodically increment its progress as a percentage. It involves more code, but it's more versatile than the first method.

Now select ViewCode and insert the following:

Private Sub UserForm_Initialize( )
Me.Caption = "0% Complete"
End Sub
Public Function Increment(sPercentComplete As Single, _
sDescription As String)
On Error Resume Next
Me.Label1.Caption = sDescription
Me.Repaint
Dim iPercentIncrement As Integer
iPercentIncrement = Format(sPercentComplete, "#")
Select Case iPercentIncrement
Case 10
Me.Frame1.visible = True
Me.Caption = "10% Complete"
Me.Repaint
Case 20
Me.Frame2.visible = True
Me.Caption = "20% Complete"
Me.Repaint
Case 30
Me.Frame3.visible = True
Me.Caption = "30% Complete"
Me.Repaint
Case 40
Me.Frame4.visible = True
Me.Caption = "40% Complete"
Me.Repaint
Case 50
Me.Frame5.visible = True
Me.Caption = "50% Complete"
Me.Repaint
Case 60
Me.Frame6.visible = True
Me.Caption = "60% Complete"
Me.Repaint
Case 70
Me.Frame7.visible = True
Me.Caption = "70% Complete"
Me.Repaint
Case 80
Me.Frame8.visible = True
Me.Caption = "80% Complete"
Me.Repaint
Case 90
Me.Frame9.visible = True
Me.Caption = "90% Complete"
Me.Repaint
Case 100
Me.Frame10.visible = True
Me.Caption = "100% Complete"
Me.Repaint
End Select
End Function

You can now use the progress bar from within your macros. All you need to do is provide the percentage and any text you'd like displayed underneath the progress bars.

The following is the HighlightLongHeadings macro, revised to use this progress bar. The lines shown in bold are the ones that interact with the progress bar.

Sub HighlightLongHeadings( )
Dim lParaCount As Long
Dim sPercentage As Single
Dim i As Integer
Dim para As Paragraph
Dim sStatus As String
IncrementalProgress.Show
lParaCount = ActiveDocument.Paragraphs.Count
i = 1
For Each para In ActiveDocument.Paragraphs
sPercentage = (i / lParaCount) * 100
sStatus = "Checking " & i & " of " & lParaCount & " paragraphs"
IncrementalProgress.Increment sPercentage, sStatus
If para.OutlineLevel = wdOutlineLevel2 Then
If para.Range.Words.Count > 10 Then
para.Range.HighlightColorIndex = wdBrightGreen
End If
End If
i = i + 1
Next para
Unload IncrementalProgress
End Sub

Running this macro will display the progress bar shown in Figure 7-12.

Your macros will take longer to run, because the progress bar adds overhead. You should test versions of your macros with and without the progress bar to determine whether you find the performance hit acceptable.

...
Case 40
With Me
.Frame1.Visible = True
.Frame2.Visible = True
.Frame3.Visible = True
.Frame4.Visible = True
.Caption = "40% Complete"
.Repaint
End With
...