ADMINISTERING MACROS

MACROS

Record a Macro to Repeat a Task

The Annoyance:

I spend half the day creating the same type of document over and over again. I've streamlined things as much as possible with templates, AutoText, and AutoCorrect, but it's still duller than watching asphalt dry.

The Fix:

This sounds like a job for a macro or two. In short, you switch on the Macro Recorder, perform the series of actions that you want to be able to repeat at the touch of a button, and then stop the Macro Recorder. You can then play back the macro as often as needed. Here's how it works:

Start Word and set up the document you want to automate.
Choose Tools Macro Record New Macro to display the Record Macro dialog box (see Figure 8-1). Type a name for the macro (no spaces, but underscores are okay) in the "Macro name box and provide a description in the "Description" box. In the "Store macro in" drop-down list, make sure that "All Documents (Normal.dot)" is selected, unless you specifically want to store the macro in a different document or template. (If you do, the macro will be available only when that document, that template, or a document based on that template is open .)

Figure 8-1. Assign a descriptive name and a detailed description to each macro you recordotherwise, your list of macros can quickly become confusing.

Warning: The Record Macro dialog box also lets you choose a way of running the macro in the future: click the Toolbars button and use the Customize dialog box to assign the macro to either a toolbar button or a menu command, or click the Keyboard button and use the Customize Keyboard dialog box to assign a keyboard shortcut. Although this functionality is convenient , it's best not to use it, because you'll often need to move your recorded macros from the default location into which Word records them to a more permanent location. Moving them breaks any way you've assigned of running the macro, so it's best to move the macro before you assign it to a toolbar, menu, or keyboard shortcut.
Click the OK button to start recording the macro. Word displays the Stop Recording toolbar, which contains a Stop Recording button and a Pause Recording/Resume Recording button. The mouse pointer displays an audio-cassette icon to indicate that recording is on.
Perform the actions that you want the macro to repeat. You can perform almost all keyboard actionsfor example, moving the insertion point and selecting objectsand you can execute most commands from the menus . You cannot use the mouse to select objects or to drag the selection, although you can use the context menu to execute commands (for example, Cut or Paste).
If you need to perform an action that you don't want to record in the macro, click the Pause Recorder button on the Stop Recording toolbar, perform the action, and then click the Resume Recorder button.
When you've finished all the actions you want to record, click the Stop Recording button or double-click the REC indicator on the status bar to stop recording. (You can also double-click this indicator to start recording a macro.)

To play back the macro, open or activate the document you want to affect, choose Tools Macro Macros, select the macro in the listbox, and click the Run button. If you cant see the macro in the list, check the setting in the "Macros in" drop-down list. Choose "All active templates and documents" to see the full list of available macros.

Remove Unnecessary Commands from a Recorded Macro

The Annoyance:

Hey, that was easy! I recorded a long macro that sets up the tedious bits of the proposal to a client. The trouble is, I got so carried away that I saved the document and closed it without stopping the Macro Recorder, so now each time I run the macro, it tries to save the active document under the same name.

The Fix:

Right: time for you to bite the bullet and come to grips with the Visual Basic Editor. Don't worry, it doesn't bite; it tries to be friendly, and it threatens you with neither AutoCorrect nor AutoFormat As You Type.

You can quickly toggle into the Visual Basic Editor by pressing Alt+F11 from Word, but it's usually best to display the Macro dialog box, select the macro you want to edit, and then click the Edit button. That makes the Visual Basic Editor open the macro you want to edit, so you don't have to navigate to reach it. Figure 8-2 shows the Visual Basic Editor with a recorded macro open.

Figure 8-2. The Visual Basic Editor interface is fairly easy to come to grips with.

In the upper-left corner of the Visual Basic Editor window is the Project Explorer, which you use to navigate among open projects. A project is the VBA component of a document or template. Word places all recorded macros in the NewMacros module in the Normal projectthe VBA component of Normal.dot . A module is a container for VBA code.

Below the Project Explorer is the Properties window, which shows the available properties (attributes) of the selected item. In this case, the NewMacros module is selected, and the only available property is its (Name) property.

The main area of the Visual Basic Editor is the Code window, which shows either the code for the selected item (here, the NewMacros module) or the selected user form. (A user form is a custom dialog box.) Each macro begins with a Sub line that shows the name of the macro followed by empty parenthesesfor example, Sub Format_MD_Report() and ends with an End Sub line.

To change your recorded macro, you edit the appropriate commands in the Code window. In this case, look for the commands for saving the document (the lines starting with ActiveDocument.SaveAs ; an underscore at the end of a line indicates that the following line is a continuation) and closing it (the ActiveDocument.Close command). Select these lines using the mouse or the keyboard, and press the Delete key to delete them.

If you're satisfied with your changes, save them (click the Save button on the Standard toolbar, press Ctrl+S, or choose File Save), and then choose File Close and Return to Microsoft Word to close the Visual Basic Editor and return to Word. Alternatively, press Alt+F11 to flip back to Word, leaving the Visual Basic Editor open so that you can return to it later if you need to make further changes.

Tip: By default, the Visual Basic Editor displays your code in Courier New one of the clearest fonts to read, but also one of the most boring. To use a different font or size , choose Tools

Options, click the Editor Format tab, choose the font in the Font drop-down list, and choose the size in the Size drop-down list. You can also change the default colors for different types of code if you wish.

Macro

Macros, select the macro, and click the Edit button) and click in the Code window just after the comment lines if they are present, or after the Sub line if they are not. (The comment lines are the lines of text at the beginning of the macro that start with an apostrophe and are colored green by default. These are usually set off with a blank line above and below. You can use the comment lines to give the macros name and a brief description of what it does.)

Press Enter to create a new line, press to move back to the new line, and type the code for a message box (see Figure 8-3), which should look like this:

 If MsgBox(Prompt:="Format this document?",     Buttons:=vbYesNo + vbQuestion, _       Title:="Format MD Report") = vbNo Then       Exit Sub     End If

Figure 8-3. Use a message box to make sure neither you nor your colleagues runs a macro by mistake.

This MsgBox statement uses three arguments, named items that denote information used in the statement: Prompt , Buttons , and Title . Prompt receives a text string in double quotation marks (here, "Format this document" ), which is displayed in the body of the message box. Title receives another string ( "Format MD Report" ), which appears in the titlebar. The Buttons argument controls both the buttons displayed in the message box and the icon (if any). vbYesNo makes the message box display a Yes button and a No button. vbQuestion makes the message box display a question-mark icon.

The If condition makes VBA check which button the user clicks in the message box. If the user clicks the No button, the result of the message box is vbNo . In this case, the Then statement comes into effect, and the Exit Sub command makes VBA exit the subprocedurein other words, skip the rest of the code in the macro. If the user clicks the Yes button, the result of the message box is vbYes ; the Exit Sub command doesn't run in this case, so the rest of the macro does run. The End If statement marks the end of the If condition.

As you type the code for the message box, the Visual Basic Editor will help you out with prompts. When the Visual Basic Editor displays a drop-down list of possible options, you can choose from it by "typing down" (continuing typing) to reach your selection, by using and , or by using the mouse.

To test your message box, step into the macro by clicking anywhere between the Sub and End Sub lines and then pressing F8 to execute one command at a time. The Visual Basic Editor displays a yellow highlight on the command it's currently executing so you can track what's happening. When the message box is displayed, click the Yes button or the No button to dismiss it so you can continue executing the code.

Return to the Last Editing Position When Opening a Document

The Annoyance:

I began word processing using a Mac application called WriteNow. When you saved a document in which you were working, WriteNow also saved where you were (that is, where your cursor was positioned in the document). When you opened the document again to continue, the cursor started off where you had left it at the end of the previous session. It frustrates me with Word, especially with a loooong document, to always find myself back at the first character of the file when I reopen it.

The Fix:

A macro can make Word return to the last editing position. If you assign the macro the predefined name AutoOpen , it will run automatically when you open a document.

PREDEFINED MACRO NAMES

Word recognizes five predefined names for macros that run automatically:

AutoExec (when Word starts)
AutoExit (when Word quits)
AutoNew (when you create a new document based on a template containing an AutoNew macro)
AutoOpen (when you open a document that contains an AutoOpen macro, or a document based on a template that contains one)
AutoClose (when you close such a document)

By assigning these names, you can make macros run automatically. You can also make an automatic macro run another macro if needed. To do so, include the other macro's name on its own line. For example:

 Sub AutoOpen()       Configure_Document_for_Editing     End Sub

Tip: If you don't want to create a macro, you can return to the last editing position by pressing Shift+F5 after you open the document; see "Return to Your Last Three Edits" in Chapter 3.

Here's how to create the macro:

Open the Visual Basic Editor and create a new module for storing your macros. (Word puts recorded macros in the NewMacros module by default, but you'll do better to keep your macros separate.) Right-click the Normal item in the Project Explorer and choose Insert Module. The Visual Basic Editor creates a new module called Module1 . Press F4 to move the focus to the Properties window, type a distinctive name for the module (again, no spaces, but underscores are okay), and press Enter to apply it.
Press Enter at the end of the line. The Visual Basic Editor automatically adds the parentheses after the macro name, a blank line, and the End Sub line:
```
 Sub AutoOpen()     End Sub 
```
Type the statement Application.GoBack between the Sub line and the End Sub line.

It's as easy as that. If you don't always want to return to the last editing position when you open a document, add a message box to let you choose whether to do so, as shown in Example 8-1.

Tip: To prevent a line of code from growing too long, you can break it by typing a space and an underscore at the appropriate point. The break must be between VBA terms rather than inside them, and it cannot be within a string.

Example 8-1. An AutoOpen macro to return to the location of the last edit after soliciting confirmation

 Sub AutoOpen()       If MsgBox(Prompt:="Return to the last edit?", Buttons:=vbYesNo + vbQuestion, _            Title:="Return to Last Edit") Then            Application.GoBack       End If     End Sub

Make Print Preview Open in Editing Mode

The Annoyance:

Call me weird, but I've taken to editing in Print Preview. But it takes three steps to switch to Print Preview, enter Edit mode, and zoom the window how I want it. I'd like to make it one step.

The Fix:

Easy enough. Actually, this is a good candidate for a recorded macro. Choose Tools Macro Record Macro, type a name (for example, PrintPreviewEdit ), type a description, and then click the OK button. Choose File Print Preview, and click the Magnifier button to enter editing mode. Choose View Toolbars Print Preview to toggle off the Print Preview toolbar (to save the space it takes up). Choose View Zoom, select your preferred zoom settings, and click the OK button. Click the Stop Recording button on the Stop Recording toolbar to stop the Macro Recorder.

If you open the macro in the Visual Basic Editor, it should look something like Example 8-2.

Here's what's going on in this macro:

The ActiveDocument.PrintPreview statement technically applies the PrintPreview method to the ActiveDocument object. The ActiveDocument object represents the active documentthat is, the document you're working with in the user interface. A method is a command or action that you can take with an object.
The CommandBars("Print Preview").Visible = False statement turns off the display of the Print Preview toolbar. A CommandBar object is either a toolbar (as it is here) or a menu bar. VBA groups similar objects into groups called collections to simplify access to them. To reach an object in a collection, you specify its name (as here: the object called "Print Preview" in the CommandBars collection) or its index number (for example, CommandBars(1) indicates the object with the index number 1 in the CommandBars collection). Objects have properties (attributes) that control how they behave. This statement sets the Visible property of the CommandBar object to False , making it invisiblein other words, hiding the toolbar.
With... End With is a structure for applying multiple methods , properties, or a combination of the two to the same object. By using a With statement, you tell VBA that you're referring to a particular object until you start referring to another object. (This enables VBA to work faster than if it has to work out the target object each time.) This With statement works with the Zoom object contained in the View object within the ActivePane object (the active pane) within the ActiveWindow object. The statements contained in the With statement set the number of page columns to 2 and the number of page rows to 1 in other words, the equivalent of clicking the Many Pages button in the Zoom dialog box and choosing two pages wide by one page deep.

Example 8-2. A macro that opens Print Preview in editing mode

 Sub PrintPreviewEdit()     ' PrintPreviewEdit Macro     ' Macro recorded 5/14/2005 by Teresa Ramirez         ActiveDocument.PrintPreview         ActiveDocument.ActiveWindow.View.Magnifier = False         CommandBars("Print Preview").Visible = False         With ActiveWindow.ActivePane.View.Zoom             .PageColumns = 2             .PageRows = 1         End With     End Sub

Tip: If you want this macro to supplant the built-in Print Preview command, rename it FilePrintPreview . You cannot record a macro with the name of a built-in command, but you can rename a recorded macro in the Visual Basic Editor so that it replaces a built-in command.You can also create a macro with the name of a built-in command by working in the Visual Basic Editor. A quick way to start such a macro is to press Alt+F8 to display the Macros dialog box, choose "Word Commands" in the "Macros in" drop-down list, select the command you want to replace, choose "Normal.dot" in the "Macros in" drop-down list, and then click the Create button. Word enters the code equivalent to the command in the Visual Basic Editor, providing a good starting point.

Make Outline View Open Expanded to a Specified Level

The Annoyance:

Word opens in Outline view with everything expanded, including the text. (See "Expand and Collapse Outline View Quickly" in Chapter 3 for more on this annoyance.) I only want it to show Level 1 headings.

The Fix:

Create the macro shown in Example 8-3 to replace Word's built-in ViewOutlineMaster command, which is used for the View Outline command and for the Outline View button on the horizontal scrollbar.

Example 8-3. A macro to make Outline view open expanded to Heading Level 1

 Sub ViewOutlineMaster()       ActiveWindow.View.Type = wdOutlineView       ActiveWindow.View.ShowHeading (1)     End Sub

The first statement changes the view of the active window to Outline view. The second statement collapses the outline so that it shows only Level 1 headings. Change the heading number if you want to display a different level of headingsfor example, ActiveWindow.View.ShowHeading (3) displays Level 1, Level 2, and Level 3 headings.

Copy and paste the macro in the same module you created in "Return to the Last Editing Position When Opening a Document," but change the name of the copy to "ViewOutline." This replaces the built-in Word command assigned to the Ctrl+Alt+O shortcut for switching to Outline view. (If you need to get the built-in Word command back, rename or delete your macro.)

Still in the Visual Basic Editor, click the Save button, or choose File Save, to save your changes.

Switch the Active Document to Read-Only Status

The Annoyance:

I receive lots of documents that I need to read but that I'm not supposed to change. I know I could open them as read-only from the Open dialog box by using the drop-down on the Open button, but I usually open them directly from email or from Explorer, so I don't have that choice. What I'd like is a box to check that tells Word I want to handle a document as "read only" after I've already opened it.

The Fix:

As you say, there's no simple checkboxor other controlto switch an open document to read-only status. But you can create a macro that does this well enough (see Example 8-4). Also, from Windows Explorer, you can right-click a Word document and choose "Open as Read-Only" from the shortcut menu.

Example 8-4 probably looks impenetrable, but once you get the hang of the If... Then statements, it's pretty straightforward. Here's what's happening:

Example 8-4. A macro to switch the active document to read-only status

 Sub Reopen_Active_Document_As_Read_Only()     'close the active document and reopen it as read-only     'store the page number and return to it when the document is reopened     Dim strDocName As String     Dim strDocFullName As String     Dim strTitle As String     Dim intPage As Integer     strDocName = ActiveDocument.Name     strDocFullName = ActiveDocument.FullName     strTitle = "Reopen Document As Read-Only"     intPage = Selection.Information(wdActiveEndPageNumber)     If MsgBox(Prompt:="Close " & strDocName & " and reopen as read-only?", _          Buttons:=vbYesNo + vbQuestion, Title:=strTitle) = vbYes Then          If ActiveDocument.Path = "" Then             MsgBox Prompt:="This document has never been saved." & vbCr & vbCr _                & "Please save it now.", _                Buttons:=vbOKOnly + vbExclamation, Title:=strTitle          If Dialogs(wdDialogFileSaveAs).Show = 0 Then              Exit Sub     End If     strDocFullName = ActiveDocument.FullName   End If   If Not ActiveDocument.Saved Then       If MsgBox(Prompt:="The document contains unsaved changes." & vbCr _            & vbCr & "Click Yes to save the changes; " & _            "click No to discard the changes.", Buttons:=vbYesNo + vbQuestion, _            Title:=strTitle) = vbYes Then _       ActiveDocument.Save   End If   ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges   Documents.Open FileName:=strDocFullName, ReadOnly:=True   Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=intPage   End If End Sub

The first few lines are comments explaining what the macro does.
The second group of lines uses Dim statements to declare variables , or storage slots for data used in the macro. The format is Dim variablename As variabletype . The first three declarations create String variables, which are variables that can hold text characters . The fifth declaration creates an Integer variable, which is a variable that can contain only a whole number (either positive or negative).
The third group of lines assigns information to the variables. The strDocName variable receives the document's name (without the path). The strDocFullName variable receives the document's name and path. The strTitle variable receives the text to be displayed in the titlebar of each message box. The intPage variable gets the page number of the end of the current selection. (This will be used for the macro to display this page again after reopening the document.)
The first If statement displays a message box to confirm that the user wants to run the macro. If the user clicks the Yes button, returning the value vbYes , the rest of the macro runs; if not, execution branches to the End If statement just before the End Sub statement, and the macro ends without taking any action.
Provided the user has clicked the Yes button in the first message box, the next If statement checks whether the active document's path is an empty string ( "" double quotation marks with no characters between them). If so, that means the document has never been saved, so the macro displays a notification message box (with just an OK button) followed by the Save As dialog box, which is the object named wdDialogFileSaveAs in the Dialogs collection. If the value returned by the Save As dialog box is , the user has clicked the Cancel button rather than saving the document, so the Exit Sub statement exits the macro. Otherwise , the strDocFullName = ActiveDocument.FullName statement assigns the document's name and path (now that it has one) to the strDocFullName variable.
The next If statement checks whether the document contains unsaved changes ( If Not ActiveDocument.Saved translates to "if the active document has not been saved") and, if so, displays a message box asking the user to click the Yes button to save the changes or the No button to discard the changes. If the user clicks the Yes button, the ActiveDocument.Save statement saves the changes.
After all those preliminaries , it's time for action. The ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges statement closes the document without saving changes. (If the user chose to save unsaved changes, they've already been saved.) The Documents.Open statement then uses the strDocFullName variable to open the document again, this time as read-only. The Selection.GoTo statement displays the page whose number is stored in the intPage variablethat is, the page that was displayed when the macro was run.

Close All Documents Except the Current One

The Annoyance:

I tend to end up with a stack of documents open and need to close all except the one I'm working on. Clicking the Close button gets tedious, even though it could hardly be easier.

The Fix:

You can easily create a macro (see Example 8-5) to close all the documents except the current one. To do so, you use a loop , a structure that tells VBA to repeat an action as long as a condition is true or until a condition is met.

Example 8-5. A macro that closes all documents except the active document

 Sub Close_All_Except_Active_Document()     Dim i As Integer     Dim strKeepOpen As String     strKeepOpen = ActiveDocument.Name     For i = Documents.Count To 1 Step -1       If Documents(i).Name <> strKeepOpen Then Documents(i).Close     Next i End Sub

As you can see, this macro is short and friendly: it even lacks comments about what the macro does (add them at the beginning if you like) and a message box to confirm that the user wants to take the action (you could add this too). Here's how it works:

The first group of lines declares an Integer variable named i and a String variable called strKeepOpen . The next line then assigns to strKeepOpen the name of the active document, which is the document the macro will leave open.
The For... Next loop uses the variable i as its counter and runs from Documents.Count (which returns the number of open documents) to 1 , decrementing the counter by 1 ( Step -1 ) at each iteration. (By default, loops increment the counter by one unless you specify otherwise. This loop needs to run backwards because each time the macro closes a document, the number of documents in the Documents collection decreases by one.) The If statement compares the name of the current document with strKeepOpen and closes the document if the name doesn't match. For example, if you have 12 documents open, Word starts with Documents(12) the twelfth document in the Documents collectionand closes that document if its name does not match the value of strKeepOpen . The Next i statement indicates the end of the loop, making execution return to the For statement. The loop continues to execute until the counter reaches 1.

Create a "Paste as Unformatted Text" Macro

The Annoyance:

I often have to create documents that are cobbled together from various sources, and I need to strip out all the existing formatting.

The Fix:

As you saw in Chapter 3, pasting material as unformatted text can save you plenty of formatting annoyances. To automate the process, all you need is a single line of VBA code (see Example 8-6).

Example 8-6. A macro that pastes as unformatted text

 Sub Paste_As_Unformatted_Text()     Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _         Placement:=wdInLine, DisplayAsIcon:=False End Sub

See Which Keyboard Shortcuts Are Assigned

The Annoyance:

Our systems people have customized our main work template so that we can all use company-standard keyboard shortcuts for applying styles and running macros. The problem is that I was ahead of them on this and have been using my own shortcutswhich aren't all the same as the new ones. So now some of my shortcuts work, while others run commands I'm not expecting. How can I see what's assigned where? I'm sick of combing through the Customize Keyboard dialog box in the hope of identifying the apparently random command they've assigned to my shortcut.

The Fix:

Good newsyou can print out a list of assignments. Choose Tools Macro Macros, type listcommands , and press Enter or click the Run button. In the List Commands dialog box (see Figure 8-4), choose the "Current menu and keyboard settings option (to see the full list of commands, choose the "All Word commands" option). You can print out the resulting table for reference, search through it, or sort it by the column that interests you (using Table Sort).

Figure 8-4. The List Commands command produces a table of the keyboard shortcuts currently assigned in Word.

Remove Typed Numbering from Lists and Headings

The Annoyance:

My boss emails me numbered lists of tasks for everyone in the office. When I put them into our Assignment template, I need to delete all the numbers he's typed in, because the lists get automatic numbering from the styles. I know I can go through selecting each list and then clicking the Numbering button on the Formatting toolbar twice, once to remove the numbers and once to reapply them, but that messes up the list templates.

The Fix:

Create the short macro shown in Example 8-7 to remove the manually applied numbers from a selected list.

WordBasic was the programming language used in early versions of Word, before Microsoft applied VBA to Word. VBA includes a WordBasic object that enables you to use WordBasic commands, such as this one, via VBA.

Example 8-7. A macro to remove manual numbering from a list

 Sub Remove_Manual_List_Numbering()     WordBasic.ToolsBulletsNumbers Replace:=0, Type:=1, Remove:=1 End Sub

Apply Proper Capitalization

The Annoyance:

The Title Case option in the Change Case dialog box (Format Change Case) makes the first letter of each word uppercase and the remaining letters lowercase. But conjunctions and short prepositions (five letters or fewer, according to the magazine I work for) should be all lowercase.

The Fix:

A macro can fix most of these errors, although you will need to check prepositions that need initial capitals because they are part of a verb. Example 8-8 shows the macro.

Tip: If you're familiar with VBA, you'll probably find the extended Or condition cumbersomenot to mention a lot of typing. You can make the code more compact by using a Select Case statement instead of the if statement, using Case "a", "above", "after", "an" , and so on.

The code in the macro is fairly straightforward:

In the first group of statements, the macro declares two variables: a String variable named strL and an Integer variable named i .
In the second group of statements, the macro selects the appropriate paragraph. The first statement compares the selection type to wdInsertionPoint the "selection" being a point in the text rather than an actual selection of characters or other objects. If the selection is not an insertion point, the macro collapses the selection to an insertion point. The second statement selects the first paragraph of the ( collapsed ) selectionin other words, the paragraph in which the insertion point is located. Selecting the paragraph also selects the paragraph mark at its end, and this paragraph mark counts as a "word." So, to work with only the real words, the third statement shortens the selection by one character to the left, deselecting the paragraph mark.
The third group of statements applies title case (first letter capitalized) to the first and last words in the selection, because the first and last words in a heading must always be capitalized even if they're short prepositions or conjunctions.
The fourth group of statements uses a For... Next loop to check each of the remaining words in the selection against a list of short prepositions and conjunctions. (Adjust this list to meet your company's style demands.) The loop runs from 2 (the second word) to Selection.Words.Count - 1 , or the number of words in the paragraph minus 1 (so as not to affect the last word, to which title case has already been applied). The second statement assigns to strL the lowercase ( LCase ) version of the currently selected word, with any leading or trailing spaces trimmed off it (when you double-click to select a word, VBA includes any spaces after the word). Using the lowercase version of the word is necessary because the comparison is case-sensitive. The If statement then compares strL to the list of prepositions and conjunctions. If there's a match, Word applies lowercase to the word; if not, the Else statement applies title case to the word.
Finally, the Selection.Collapse statement collapses the selection to its end, which is equivalent to pressing to deselect a selection.

Example 8-8. A macro to apply proper capitalization to a title

 Sub Apply_Proper_Capitalization()    'applies proper capitalization to the first paragraph in the selection     Dim strL As String     Dim i As Integer     If Selection.Type <> wdSelectionIP Then Selection.Collapse     Selection.Paragraphs(1).Range.Select     Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend     Selection.Words(1).Case = wdTitleWord     Selection.Words(Selection.Words.Count).Case = wdTitleWord     For i = 2 To Selection.Words.Count - 1       strL = LCase(Trim(Selection.Words(i)))       If strL = "a" Or strL = "above" Or strL = "after" Or strL = "an" Or _       strL = "and" Or strL = "as" Or strL = "at" Or strL = "below" Or _       strL = "but" Or strL = "by" Or strL = "down" Or strL = "for" Or _       strL = "from" Or strL = "in" Or strL = "into" Or strL = "of" Or _       strL = "off" Or strL = "on" Or strL = "onto" Or strL = "or" Or _       strL = "out" Or strL = "over" Or strL = "the" Or strL = "to" Or _       strL = "under" Or strL = "up" Or strL = "with" Then         Selection.Words(i).Case = wdLowerCase       Else         Selection.Words(i).Case = wdTitleWord      End If   Next i   Selection.Collapse Direction:=wdEnd End Sub

Tip: This macro isn't perfect, as it ignores the possibility of periods or other punctuation requiring prepositions or conjunctions to be title case in the middle of a paragraph. You might adapt the macro to take care of this need. You might also add a list of uppercase terms (for example, XP) or intercapitalized terms (for example, iPod) that require special treatment.

Double-Save a Document

The Annoyance:

Call me paranoid , but I think Word may really be out to get meor perhaps it's just that I'm working with master documents. Anyway, Word seems unstable, and I want to keep a backup copy of each document I'm working with while I'm working with it. It seems to me that Word's automatic backup copy would be just about ideal, if it were a copy of my most recent save rather than the last-but-one save. With the speed at which I'm trying to make progress on my documents, even losing a couple of minutes' worth of changes is painful.

The Fix:

Create a macro to force Word to save the document twice in immediate succession. Call the macro FileSave so that it replaces Word's built-in Save command and picks up its keyboard shortcut (Ctrl+S).

If you simply want to double-save the document, all you need is the macro shown in Example 8-9.

Example 8-9. A macro to save the active document twice in succession

 Sub FileSave()      Options.CreateBackup = True      ActiveDocument.Save      ActiveDocument.Saved = False      ActiveDocument.Save   End Sub

The Options.CreateBackup = True statement ensures that the "Always create backup copy" feature is on (you can also set it by choosing Tools Options, clicking the Save tab, and checking the "Always create backup copy box).

But if you really need to safeguard your work, you can adapt the macro so that it automatically copies the backup document to a safe locationfor example, a network drive, as in Example 8-10.

This macro declares three String variables to store the text it uses for naming the documents. It assigns to strDocName the name of the active document and assigns to strWordBackupDoc a string consisting of the full name (including the path) that Word gives the backup document for the active document. For example, if the active document is named Example.doc and is located in the C:\Samples folder, its backup document is named C:\Samples\Backup of Example.wbk .

The macro assigns to strMyBackupDoc the path and name for the backup document that you want to create. In this example, the macro assigns the backup document the path Z:\Publi-c\Backups\ (change it as needed, and use the notation \\server\folder\ if the drive isn't mapped to a letter), the basic filename, the word "backup," the date in yyyy-mm-dd format, and the time in hh-mm-ss AMPM format. In other words, the backup document for Example.doc will have a name such as Example backup 2005-05-27 04-14-38 PM.doc .

Finally, the FileCopy statement copies the file specified by strWordBackupDoc to strMyBackupDoc . If there's already a file with that name in the backup folder, it will be overwritten, but unless you save twice within a second, this shouldn't happen.

Example 8-10. A macro to back up the active document's default backup copy to a network drive

 Sub FileSave()     Dim strDocName As String     Dim strWordBackupDoc As String     Dim strMyBackupDoc As String     Options.CreateBackup = True     ActiveDocument.Save     ActiveDocument.Saved = False     ActiveDocument.Save     strDocName = ActiveDocument.Name     strWordBackupDoc = ActiveDocument.Path & "\Backup of " & _        Left(strDocName, Len(strDocName) - 3) & "wbk"     strMyBackupDoc = "Z:\Public\Backups\" & Left(strDocName, _       Len(strDocName) - 4) & " backup " & Format(Date, "yyyy-mm-dd") & _       " " & Format(Time, "hh-mm-ss AMPM") & ".doc"     FileCopy strWordBackupDoc, strMyBackupDoc   End Sub

Print a Document in Monochrome

The Annoyance:

Changing the Compatibility settings in the Options dialog box so that I can legibly print a document that contains color text on my monochrome printer (see "Print Colored Text as Black on a Monochrome Printer" in Chapter 6) is getting kinda old. Automate it for me already!

The Fix:

Right you are. Create a macro that looks like Example 8-11.

This macro creates two Boolean variables, which are variables that can only be true or False . It then checks to see if the number of open documents is ; if so, it stops running the macro so as to avoid the error that results from trying to print without a document open.

Example 8-11. A macro to print the active document in monochrome

 Sub Print_Active_Document_in_Monochrome()     Dim blnBWOn As Boolean     Dim blnDocClean As Boolean    If Documents.Count = 0 Then Exit Sub    With ActiveDocument       blnDocClean = .Saved       blnBWOn = .Compatibility(wdPrintColBlack)       .Compatibility(wdPrintColBlack) = True       Dialogs(wdDialogFilePrint).Show       .Compatibility(wdPrintColBlack) = blnBWOn       .Saved = blnDocClean    End With  End Sub

For the bulk of its code, the macro uses a With statement referring to the active document. It stores the current state of the document's Saved property ( TRue if the document contains no unsaved changes, False if it does contain some) in the blnDocClean variable, and the state of the Compatibility (wdPrintColBlack) setting (whether the "Print colors as black on noncolor printers" checkbox on the Compatibility tab of the Options dialog box is checked) in the blnBWOn variable.

The macro then sets Compatibility(wdPrintColBlack) to true and displays the Print dialog box so that the user can print the document. Finally, the macro restores Compatibility(wdPrintColBlack) and Saved to the values stored in the two variables.

Display the Print Dialog Box with the "Current Page" or "Pages" Option Selected

The Annoyance:

When I'm working on a document, I often want to finish editing a page, print it, check it, and then print another page. But the Print dialog box always appears with the All option selected in the Page Range group box.

The Fix:

With a short macro (see Example 8-12), you can make the Print dialog box appear with the Current Page option, the Pages option, or the Selection option selected instead. If you select the Pages option, you can specify the range of pages to print, as in the example.

Example 8-12. A macro to display the Print dialog box with print options preselected

 Sub Display_Print_Dialog_Custom()      With Dialogs(wdDialogFilePrint)          .Range = wdPrintRangeOfPages          .Pages = "3,5,7-11"          If .Show = -1 Then .Execute      End With  End Sub

The With statement works with the wdDialogFilePrint member of the Dialogs collectionin other words, the Print dialog box. The commands inside the With statement control the options selected:

To select the Current Page option, use this statement:
```
 .Range = wdPrintCurrentPage 
```
To select the Pages option and specify a simple range of pages, use these statements, substituting suitable numbers on the From and To lines:
```
 .Range = wdPrintFromTo     .From = "2"     .To = "4" 
```
To select the Pages option and specify a complex range of pages, use these statements, substituting suitable numbers on the Pages line:
```
 .Range = wdPrintRangeOfPages     .Pages = "3,5,7-11" 
```

The Show method displays the dialog box and returns a value for the button the user clicks. If the value is -1 , which indicates that the user clicked the OK button, the Execute command executes the instructions contained in the dialog box. If the user clicks the Cancel button, VBA does not execute the instructions.

Perform Find and Replace Operations with Complex Formatting

The Annoyance:

I tried the replace-with-subscript trick mentioned in Chapter 4 (see "Replace with a Subscript"), and it's fine as far as it goes. But what I need to do is find each table caption; check to see if the paragraph before it uses a boxed style; and, if it doesn't, apply a top border to the table caption. I can't do that with Replace, now can I?

The Fix:

Not as you describe it, no. But a macro using Find can locate the table captions, check them, and apply the border as needed. The macro shown in Example 8-13 searches for each paragraph using the Table Caption style and applies a top border to that paragraph if the previous paragraph doesn't have a bottom border. You'll need to adapt the specifics, but the principle should work for you.

Example 8-13. A macro to search for specific formatting and check nearby formatting

 Sub Check_Table_Captions()      'go through the document for paragraphs in the Table Caption style      'check if the paragraph before has a bottom border      'if not, apply a top border to the Table Caption paragraph      Dim i As Integer      Dim strMTitle As String      On Error GoTo ErrorHandler      strMTitle = "Apply Upper Border to Table Caption Style"     If MsgBox(Prompt:="Apply the upper borders to the Table Caption style?", _        Buttons:=vbYesNo + vbQuestion, Title:=strMTitle) = vbNo Then        Exit Sub   End If   For i = 2 To ActiveDocument.Paragraphs.Count        With ActiveDocument.Paragraphs(i)            .Range.Select           If .Style = "Table_Caption" Then               If .Previous.Borders(wdBorderBottom).LineStyle <> _                  wdLineStyleSingle Then                 .Borders(wdBorderTop).LineStyle = wdLineStyleSingle                 .Borders(wdBorderTop).LineWidth = wdLineWidth100pt                 .Borders(wdBorderTop).Color = wdColorBlack              End If          End If       End With    Next i  ErrorHandler:   If Err.Number = 5834 Then       MsgBox Prompt:="This document doesn't use the Table Caption style.", _          Buttons:=vbOKOnly + vbInformation, Title:=strMTitle       Exit Sub   Else       MsgBox Prompt:="The following error has occurred:" & vbCr & vbCr & _           "Error Number: " & Err.Number & vbCr & vbCr & _           "Error Description: " & Err.Description, _           Buttons:=vbOKOnly + vbInformation, Title:=strMTitle       Exit Sub     End If   End Sub

Much of this macro is straightforward if you've been following along through this chapter, but the following points are worth noting:

The On Error GoTo ErrorHandler line tells VBA to trap errors and directs them to an error handler (a section of code designed to deal with errors). Unhandled errors in your macros usually display error message boxes, which are confusing for users, as many of the errors are hard to interpret. The error this macro is most likely to encounter is the active document not containing the Table Caption style. If this happens, the code after the ErrorHandler: label at the end of the macro runs, checking the error number against the number for a missing style ( 5834 ); if it matches, the error handler displays a message box explaining what has happened and then exits the macro. If there's a different error, the Else statement makes the macro display a message box giving the error's number and description. This information tends to be neither intelligible nor helpful, but it's preferable to having an error message box named Microsoft Visual Basic appear unexpectedly on the screen.
The For loop uses a counter ( i ) and runs from i = 2 to ActiveDocument.Paragraphs.Count , which is the number of paragraphs in the document. This loop enables the macro to check every paragraph in the document (skipping the first paragraph, which doesn't have a paragraph before it) for the Table Caption style.
The With statement works with the paragraph the loop is currently examining ( ActiveDocument.Paragraphs(i) ). If the paragraph's style is Table Caption, the nested If statement checks to see if the previous paragraph ( .Previous ) is lacking a bottom border. If it is, the three .Borders statements apply the necessary border.

Print Samples of Each Font Installed on Your PC

The Annoyance:

I need a printout of all the fonts that Word can offer me. I'm sick of scrolling through the font list trying to find one that looks okay.

The Fix:

Try the macro shown in Example 8-14.

Here's what the macro does:

First, it declares two String variables, one to store the current font name and the other to store the sample text string.
It then displays an input box prompting the user to enter the sample text string that she wants to use, providing her with default text that she can accept. If the input box returns an empty string ( "" ), the user either clicked the Cancel button, or deleted the default string and failed to replace it. Either way, the macro quits without further ado.
Next, the macro creates a new document ( Documents.Add ) and uses a For... Next loop that runs once for each font (i.e., for each item in the FontNames collection). The loop resets the font, types the font's name followed by "parahere," applies the font, and types the sample text string. The word "parahere" is a placeholder that enables the macro to separate the font names from the sample text that follows ; you can use any other unique text string instead.
The macro then selects all the content of the document and sorts it alphabetically by paragraph.
The With Selection.Find structure clears formatting and options that might cause problems and then performs two replace operations. Some Find properties may already be set in Word, so it's best to clear formatting, specify a zero-length text string, and turn off options such as "Match case," "Use wildcards," and "Find all word forms." The Execute command executes each search. The first replace operation replaces the placeholder text "parahere" with "parahere" and an extra paragraph, which splits each font name from its sample text. The second replace operation replaces "parahere" with a Heading 2 paragraph. This has the effect of applying the Heading 2 style to the paragraphs that contain the font names.
The three Selection statements collapse the selection, apply the Heading 1 style to the first paragraph in the document, and enter a heading for the list in that paragraph.
The message box tells the user what the macro has done and prompts her to save the document if she wants to keep it.

Example 8-14. A macro to list all the fonts available to Word, with a sample of each font

 Sub List_All_Fonts()       'Lists all the fonts available to Word with the current printer driver       Dim strFont As Variant       Dim strText As String       strText = InputBox(Prompt:= _           "Type the sample text you want to use in the font listing:", _           Title:="List All Fonts", _           Default:="The five boxing wizards jump quickly.")       If strText = "" Then Exit Sub       Documents.Add       For Each strFont In FontNames           Selection.Font.Reset           Selection.TypeText strFont & "parahere"           Selection.Font.Name = strFont           Selection.TypeText strText & vbCr       Next       ActiveDocument.Content.Select       Selection.Sort FieldNumber:="Paragraphs", _           SortFieldtype:=wdSortFieldAlphanumeric       With Selection.Find           .ClearFormatting           .MatchCase = False           .MatchAllWordForms = False           .MatchWholeWord = False           .MatchSoundsLike = False           .MatchWildcards = False           .Forward = True           .Wrap = wdFindContinue           .Text = "parahere"           .Replacement.Text = "parahere^p"           .Execute Replace:=wdReplaceAll           .Text = "parahere"           .Replacement.Style = "Heading 2"           .Execute Replace:=wdReplaceAll           .Replacement.ClearFormatting           .Replacement.Text = ""           .Execute Replace:=wdReplaceAll           .Text = ""       End With       Selection.Collapse Direction:=wdCollapseStart       Selection.Paragraphs(1).Style = "Heading 1"       Selection.TypeText "List of Fonts Available to Word"       MsgBox "The macro has created a list showing the fonts currently " _          & "available to Word." & vbCr & vbCr & _         "Please save this document if you want to keep it.", _         vbOKOnly + vbInformation, "List All Fonts Macro"     End Sub

Record a Macro to Repeat a Task

The Annoyance:

The Fix:

Figure 8-1. Assign a descriptive name and a detailed description to each macro you recordotherwise, your list of macros can quickly become confusing.

Remove Unnecessary Commands from a Recorded Macro

The Annoyance:

The Fix:

Figure 8-2. The Visual Basic Editor interface is fairly easy to come to grips with.

Figure 8-3. Use a message box to make sure neither you nor your colleagues runs a macro by mistake.

Return to the Last Editing Position When Opening a Document

The Annoyance:

The Fix:

PREDEFINED MACRO NAMES

Example 8-1. An AutoOpen macro to return to the location of the last edit after soliciting confirmation

Make Print Preview Open in Editing Mode

The Annoyance:

The Fix:

Example 8-2. A macro that opens Print Preview in editing mode

Make Outline View Open Expanded to a Specified Level

The Annoyance:

The Fix:

Example 8-3. A macro to make Outline view open expanded to Heading Level 1

Switch the Active Document to Read-Only Status

The Annoyance:

The Fix:

Example 8-4. A macro to switch the active document to read-only status

Close All Documents Except the Current One

The Annoyance:

The Fix:

Example 8-5. A macro that closes all documents except the active document

Create a "Paste as Unformatted Text" Macro

The Annoyance:

The Fix:

Example 8-6. A macro that pastes as unformatted text

See Which Keyboard Shortcuts Are Assigned

The Annoyance:

The Fix:

Figure 8-4. The List Commands command produces a table of the keyboard shortcuts currently assigned in Word.

Remove Typed Numbering from Lists and Headings

The Annoyance:

The Fix:

Example 8-7. A macro to remove manual numbering from a list

Apply Proper Capitalization

The Annoyance:

The Fix:

Example 8-8. A macro to apply proper capitalization to a title

Double-Save a Document

The Annoyance:

The Fix:

Example 8-9. A macro to save the active document twice in succession

Example 8-10. A macro to back up the active document's default backup copy to a network drive

Print a Document in Monochrome

The Annoyance:

The Fix:

Example 8-11. A macro to print the active document in monochrome

Display the Print Dialog Box with the "Current Page" or "Pages" Option Selected

The Annoyance:

The Fix:

Example 8-12. A macro to display the Print dialog box with print options preselected

Perform Find and Replace Operations with Complex Formatting

The Annoyance:

The Fix:

Example 8-13. A macro to search for specific formatting and check nearby formatting

Print Samples of Each Font Installed on Your PC

The Annoyance:

The Fix:

Example 8-14. A macro to list all the fonts available to Word, with a sample of each font