Much too often, we find ourselves performing some tedious editing task for the fifth time in a month, a week, or even an hour — and wishing we could cash in on some of the productivity gains computers were supposed to give us.
In Microsoft Word, the powerful but often overlooked macro feature is a software robot that can automate a wide range of repetitive actions, from changing the names of three characters in each chapter of a novel to putting standard text into a sales report and exporting the result as a Web page.
One situation that screams out for a macro is cleaning up text that you’ve copied from an e-mail message and pasted into Word; such text is often littered with unnecessary line breaks and marred by unattractive straight quotation marks, double hyphens (instead of em dashes), and other clumsy characters.
Of course, there are plenty of shareware programs that can do this kind of basic text cleanup. But if you create a macro that does the dirty work for you, you never have to switch out of Word — and you can tailor your Word macros in ways that shareware programs can only dream about.
We’ll show you how to create a cleanup macro using Word’s errorproof Watch Me mode. We’ll also show you how to dabble with Visual Basic for Applications — the code Word uses to write macros — to further customize and troubleshoot your macro. Once you’ve learned how this macro works, making up your own should be simple. (The steps here work equally well in Word 98, Word 2001, and Word X.)
-Set Up the Document
Before you can create a macro, you must transfer text from an e-mail message into a Word document. In Word, you’ll get confusing, truncated lines of text. You can use a couple of quick tricks to make the text easier to read before creating the macro.
To begin, copy some text from an e-mail message and paste it into a new Word document. You’ll probably spot the line-break problem right away: every other line within each paragraph stops partway across the page. Most e-mail programs introduce these hard line breaks automatically when they send plain-text messages — a tradition left over from the days of text-only e-mail messages whose lines could be no longer than 80 characters wide without being chopped off by the edges of fixed-width windows.
You can do two things to help make sense of the strange formatting. First, turn on the Show Invisibles command by clicking on the Show/Hide paragraph button in the Standard toolbar (View: Toolbars: Standard). Now you can see all the extra paragraph marks at the end of each line.
Second, shrink the point size so that each hard-ended line sits on a single line (press Command-A to select all text, then press Command-shift-< repeatedly). Alternatively, widen the window and shrink the right margin. Doing so eliminates all the truncated half-lines and lets you see clearly how many lines of text you really have.
Designate a Macro Trigger
Before you begin building your macro, you must answer this question: How will you trigger your macro once it’s ready? You can choose any of Word’s three different methods. In this step, we’ll explain how to set up two of the methods: selecting the macro’s name from a menu, and clicking on a toolbar button. You can also press a key combination to trigger a macro.
From the Tools menu, choose Macro; then choose Record New Macro from the submenu (or you can double-click on the Rec button on the bottom edge of the Word 2001 or Word X window). The Record Macro dialog box appears.
Name the macro EmailCleanup or something else easy to remember A. (No spaces or punctuation are allowed. Thanks, Microsoft.)
Whether you want your macro’s trigger to be a toolbar button or menu command, start by clicking on the Toolbars button B. The Customize dialog box will open, along with a new (or duplicate) toolbar representing the Word menu bar C. From here, you can drag the name of your new macro to the Standard toolbar, where it appears as a new button. To stash it in a different toolbar, click on the Toolbars tab, turn on the toolbar you have in mind, and then drag the macro name onto it.
To turn your macro into a menu command, drag the macro name from the Customize window to one of the menu titles (such as File or Edit). Then, without releasing the mouse, drag downward to place the macro among the other menu commands where you’d like it to appear D. For instance, you might want to put it at the bottom of the Tools menu or near the top of the Edit menu.
Don’t worry about the clumsy macro name you get in your toolbar or menu (Normal.NewMacros.EmailCleanup). You’ll have a chance to change it later.
Add a Keyboard Trigger
Putting a macro’s name in a menu or toolbar is convenient because you don’t have to remember a key-combination trigger. But it also takes up a lot of screen space, and triggering your macro requires several mouse clicks, as opposed to the simultaneous pressing of two or three keys. Creating a keystroke trigger for your new command is quick and easy and adds a backup method, an especially handy feature for macros you use often.
Whether or not you added a toolbar or menu trigger, the Customize dialog box should still be open on screen. (If you did add your macro to a toolbar or menu, the toolbar or menu is also probably still hanging open on your screen. That’s perfectly OK.)
Click on the Keyboard button at the bottom of the Customize dialog box. Doing so opens the Customize Keyboard dialog box.
This box shows the name you gave your soon-to-be-recorded macro A and gives you a chance to assign a key combination that will trigger it. Put your cursor in the Press New Shortcut Key field B, and then press a key combination, which must include control, 1, Command-control, or control-shift, plus one or two other keys, such as letters and numbers. For example, you might press Command-control-E. Or if you prefer the unusual two-letter method, you could press control-E-C (the macro’s initials); whenever you want the macro to whirl into action, you’ll have to press all three of those keys.
If the combination you press is already assigned to another Word command, a message lets you know that you’re about to wipe out an existing keyboard command. In our example, the key combination is unassigned C.
Click on Assign. Then click on OK twice to close the two remaining dialog boxes and begin recording the macro.
Clean Up the Hard Line Breaks
You might think that fixing those ugly hard line breaks would be a simple matter — but you can’t just nuke all return characters with a search-and-replace operation, because you’ll also wipe out your legitimate returns. You’ll wind up with one gigantic paragraph, with no trace of the original paragraph breaks. You can use the Replace command, but you have to be sneaky about it.
At the moment, double return characters represent all legitimate paragraph breaks. You want to preserve these, wiping out only single return characters.
From the Edit menu, choose Replace. You can either type ^p^p (which represents two return characters) in the Find What box A or expand the box (click on the More button or the Expand button B, or press Command-M); then, from the Special pop-up menu at the bottom of the dialog box, choose Paragraph Mark twice.
In the Replace With box C, type a symbol that probably doesn’t appear in the text itself, such as § (option-6). Click on Replace All (Command-A) and then OK to dismiss the message box after Word has completed the replacements.
You’ve just succeeded in setting aside all double returns for safekeeping. Now perform another find-and-replace operation, this time searching for single returns (^p) and replacing them with nothing (leave the Replace With box empty).
Finally, it’s time to turn all those § characters back into standard single returns. Perform yet another find-and-replace operation, this time putting § into the Find What box and ^p into the Replace With box. When Word is finished with all these search-and-destroy missions, close the Find And Replace dialog box.
Make It Typographically Correct
There are just a few more find-and-replace operations involved in transforming your e-mail text to properly formatted Word text. Teach your macro to perform such operations as turning straight quotation marks into smart ones and replacing amateurish double hyphens (–) with proper em dashes ( — ).
It’s a little-known fact: if you search for apostrophes and replace them with apostrophes, Word makes them all smart (typographically correct) in the process — if that feature is turned on in the AutoCorrect dialog box.
To see if yours is already set for smart apostrophes and quotation marks, click on the Pause button A on the Record Macro toolbar — a useful button that pauses recording whenever you need to do something that’s not part of the macro, such as check out a menu command without recording it.
Open the AutoCorrect dialog box (Tools: AutoCorrect) and click on the AutoFormat tab. Make sure the “Straight Quotes” With “Smart Quotes” option is selected B.
Click on OK to close the dialog box. Click on the Pause button again to resume recording.
From the Edit menu, choose Replace. Type an apostrophe (‘) in the Find What box, press the tab key, and then type another apostrophe in the Replace With box. Click on Replace All and then on OK. You’ve just made all your document’s apostrophes smart.
Now make the quotation marks smart. Put a quotation mark (“) in both the Find What and Replace With boxes. Click on Replace All and then on OK.
Finally, use the Find And Replace box to turn all double hyphens into proper em dashes. (To specify the em dash, press shift-option-hyphen.)
You can keep recording more find-and-replace routines until you’ve satisfied your every obsessive e-mail–cleanup preference. Just leave the Find And Replace dialog box open and continue making substitutions. For example, you might consider replacing one, two, or three angle brackets (>) with nothing (leave the Replace With box empty), deleting any of the standard Internet “quoting” marks that may crop up in your messages.
When you’ve completed all the replacements you’d like the macro to do for you, close the Find And Replace dialog box and click on Stop Recording C to end the recording.
Test-Drive Your New Macro
The moment has come to make sure your new macro works. You’ll get your first opportunity to see the speed and efficiency of a macro in action.
To get ready, repaste the original text into a new document (or use the Undo command repeatedly until the document looks the way it did before your cleanup began). Place the insertion point at the beginning of the document or choose Select All from the Edit menu. (If you don’t, Word will start doing its search-and-replace routine from wherever the insertion point is. Each time it reaches the end of the text, a dialog box will ask you if it’s OK to wrap around to the first part of the document again.)
To run a macro that you’ve recorded, use the method you chose when you created it, such as pressing a key combination. If you’ve forgotten what trigger you created, you can always choose the macro by name from a list of all your macros in the Macros dialog box (Tools: Macro: Macros). Click on the macro’s name A; then click on Run B.
If your macro gets stuck or fails to perform one of the steps, either delete it and start over, or see step 9 for debugging help.
If you decide to delete your macro, this dialog box is the place to do it (select the macro’s name and click on Delete C ). You should also take this opportunity to do away with any toolbar button or menu command you created, since the macro associated with it no longer exists. Choose Customize from the Tools menu and then, once the Customize dialog box is open, drag the macro off the menu or toolbar. In other words, reverse the procedure you used when installing the macro on the menu or toolbar in step 2.
Add a Dialog Box
The macro you’ve built is just fine for your own purposes. You designed it, so you know what it’s about to do. But for the benefit of unsuspecting coworkers or family members, you may want to create a dialog box warning them that something automated is about to take place.
From the Macro submenu of the Tools menu, choose Macros. Click on the name of your macro; then click on Edit. Visual Basic Editor opens, containing the tools programmers use to hand-code macros. It shows all the code for any macros you’ve created, separated by dotted lines.
It may look intimidating, but a little study will show you that it’s fairly straightforward. The various lines containing equal signs, for example, show Word what the status of the various controls in the Find And Replace dialog box is supposed to be.
Now you’ll create the code that makes a dialog box appear. At the top of the code window, you’ll see a few lines of text that begin with an apostrophe A. These are the comments that define your macro. Just below the comments, type the following on a new line B: If MsgBox(“I’m about to eliminate extra line breaks from the text in your document. While I’m at it, I’m going to turn any quotation marks and dashes into typographically correct ones. OK for me to go ahead?”, vbExclamation + vbYesNo) = vbYes Then
When you finish typing, be sure to press the return key.
If you don’t leave a space between Then and the code that follows, you’ll get an error message when you run the macro. (While you’re at it, make sure your inserted line doesn’t begin with an apostrophe.)
The command MsgBox tells Word to open a dialog box; it’s followed by the actual text that will appear in the box. The text at the end of the parentheses (vbYesNo) gives your dialog box Yes and No buttons. The last two words (vbYes Then) tell the macro what to do when the user clicks on Yes. In this case, it should do all the find-and-replace operations you just recorded.
Of course, all you’ve created in your code so far is the IF statement of the standard IF-THEN-ELSE programming statement. You also need an ELSE statement to tell the macro what to do if the user clicks on No — to cancel the macro without altering the document. In the next step, you’ll fill in the conclusion to this programming cliff-hanger.
Complete the Code
By this time, you’re probably wondering what happens if the user clicks on No. Just a few more words of code take care of that alternative and complete the IF-THEN-ELSE argument you started coding in the previous step.
Scroll down in the Visual Basic window to the end of the macro. Taking care not to delete or alter any existing code, click just before the End Sub function A at the very end. Press return to create a new line just above it. On that line, type Else, press return to add another new line, and type End If B.
TIP: If you suspect you’ve typed something in error or deleted part of your recorded code, you can always use the Undo command repeatedly to back out of any changes you’ve made to the code.
From the Word menu (Mac OS X) or the File menu (Mac OS 9), choose Close And Return To Microsoft Word. Clicking on the pale-blue W in the upper left corner of the Visual Basic window does the same thing. You’ve just returned from Visual Basic Editor to the familiar comfort of Word.
Test and Debug the Macro
When you record a macro using Word’s Watch Me mode, Word ensures that your code is free of errors. When you type code yourself, however, you risk introducing typos and other glitches. Fortunately, since this is fairly simple code, the debugging process is simple, too.
Back in your document, choose Undo (or press Command-Z) until the e-mail text needs cleaning up again.
Open your macro in Visual Basic Editor, just as you did at the beginning of step 7. From the Tools menu, choose Macro and then Macros. Double-click on your macro’s name (EmailCleanup) to run it. If you made any typos when you typed code into Visual Basic Editor, you’ll get an error message, and part of your code will turn red or green.
The color tells you where to look for the error. Maybe you deleted part of the End Sub command or used incorrect punctuation in the MsgBox code. Using the code shown in steps 7 and 8 as a guide, correct the typos and try running the macro again.
When your code is perfect and you run the macro, you’ll see the dialog box you created. Click on Yes to make Word clean up your text.
TIP: Although the Visual Basic window is in the foreground when you do this debugging, the macro will actually run in Word behind the code window. To be safe, have only your test document open in Word as you debug and test the macro.
Customize the Macro Trigger
After you’ve used the macro for a while, you may decide you’d like to change the way you use it. Maybe you’d like to change its keyboard trigger or choose a better name than Normal.NewMacros.EmailCleanup.
To edit the trigger for your macro, begin by choosing Customize from the Tools menu.
If your macro is triggered by a keyboard combination, click on the Commands tab, and then click on Macros in the Categories list. Click on the macro name in the list at the right side of the dialog box, click on the Keyboard button, and then reassign the keystrokes — click in the Press New Shortcut Key field to specify the new key combination, click on Assign, and click on OK twice. (In Word 98, click on Close twice.)
If you turned your macro into a toolbar button, on the other hand, control-click on the toolbar button. From the resulting menu, choose Properties to open the Command Properties dialog box. In the Name box, type a different name for the button — and this time you can use spaces and punctuation.
(In Word 98, control-click on the toolbar button and choose Customize; then control-click on the toolbar button again and change the name in the resulting pull-down menu.)
Below that, you can choose to have the text display, or you can select Default Style to display an icon only. For the most fun, you can even use this dialog box to give your new macro an icon of its own: click on the button in the upper left corner of this box and choose an icon from the palette A. (In Word 98, after choosing Customize, control-click on the toolbar button and choose Change Button Image from the pull-down menu.)
Finally, if you gave your macro a menu command, click in the lower, duplicate menu bar to open the necessary menu. (In Word 98, if you don’t see the duplicate, customizable menu bar, click on the Toolbars tab in the Customize dialog box and select the Menu Bar option to make it appear.) You can now drag the macro command to a different position on a different menu or drag it off the menu completely to remove it. To change the menu command’s name, control-click on it in the phony, duplicate menu bar; then choose Properties to open the Command Properties dialog box, where you can edit its name.
Now it’s time to save all your hard work. When the macro is clean, shiny, and working perfectly, save it by quitting Word. Word stores the macro in your default template file — but not until you quit the program. If your Mac crashes before you’ve quit Word, you’ll lose the macro.