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 bar—the 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 Tools -> Macro -> Macros:
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
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 Tools -> Macro -> VisualBasic Editor, choose Normal in the
Project Explorer (near the top left of the window), and then select Insert
UserForm. Next, choose View -> Toolbox to display the Toolbox (it may
already be showing). Select the Label control (the one with the “A” on
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
the label near the top left of the
Next, select View -> Code 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
Figure 7-9. Creating a simple progress bar with a UserForm
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 Insert -> Module to create one.
Figure 7-10. Select one of the code modules in your Normal template
In the code module you’ve selected, insert the following code:
Sub HighlightLongHeadings( ) UserForm1.Show End Sub
Now select File -> Close and Return to Microsoft Word. To run the macro,
select Tools -> Macro -> Macros and choose
you run the macro, you’ll see a progress bar like the one shown in
Figure 7-11. A simple progress bar in action
TIP: If the document is very short, you probably won’t see the progress bar—it’ll finish filling in too fast. Test this out on a long document to really see it in action.
One of the lines in the UserForm code deserves a closer look:
Format(Me.Label1.Width + sIncrement, "#.##")
sIncrement is the final width of the progress bar divided
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
sIncrement can become quite small—smaller 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
However, if you use the Format function, the increment amount will be
rounded more precisely, keeping it confined to the boundaries of the
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.
Figure 7-12. A progress bar that displays percentage increments
To keep the example simple, you should put this code in your Normal template. Select Tools -> Macro -> VisualBasic Editor, choose Normal in the Project Explorer (near the top left of the window), and then select Insert -> UserForm. Next, choose View -> Toolbox to display the Toolbox (it may already be showing).
On the Toolbox, select the Frame control (the box with “xyz” at the
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
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.
Figure 7-13. Change the frame’s caption and background color from the Properties window
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
Now go back to the UserForm itself and select the frame. Choose Edit -> Copy, 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 Format -> Align 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 View -> Code 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
Figure 7-14. Creating an incremental progress bar
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
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.
WARNING: The above code assumes you will hit each percentage stop along the way. If you expect to skip increments, modify the code to make sure you “turn on” all the increment frames lower than the current one. For example:
... Case 40 With Me .Frame1.Visible = True .Frame2.Visible = True .Frame3.Visible = True .Frame4.Visible = True .Caption = "40% Complete" .Repaint End With ...