Tips for Word, Excel, Outlook and more

   

Tips To Date

1,634
Tips for   
Word   
Excel   
Outlook   
PowerPoint   
and more   

Tips, tricks, tools, and techniques for Microsoft Office

The Tip Archive
About Us
Partners



FAVORITE OFFICE LETTER TIPS:
WORD MACROS


Part 1: Recording a Macro       Part 2: Creating Macro Shortcuts       Part 3: Editing a Macro


There's nothing like a macro to get repetitive work done quickly. Creating macros can seem mysterious, but it needn't be. This three-part article will show you the basics of creating, running, and changing a macro in Word 97, 2000, and 2002 (aka XP).

Macros can be written from scratch, but if you're a typical Word user you'd probably need to take a crash course in Visual Basic for Applications (VBA). That's just impractical for most of us. A more efficient use of your time is to let Word do most of the work.

Early in Word's history, Microsoft provided a way to record keyboard and mouse activity and turn the actions into a macro. In Word 97 through 2003, such recordings are turned into VBA. Even if you need to do some special coding in VBA, the simplest solution may be to let Word lay the foundation -- create skeleton code you can modify – rather than starting from scratch.



PART 1: RECORDING A MACRO


THE TASK

Imagine you work for Acme Software which has just changed its name to AcmeSoft. We are going to write a macro that does a search and replace in an open document, changing Acme Software to AcmeSoft for all occurrences Word finds. This week you're going to record and run the macro. In Part 2 you'll assign the macro to a toolbar button so you can run it quickly. In Part 3 you will use the VBA editor to make a small change to the macro.

If you were to perform this search-and-replace task manually, you would:

  • Use the Edit/Replace command, or press Ctrl+H.
  • Enter "Acme Software" (without the quotes) in the "Find what" box.
  • Enter "AcmeSoft" (without the quotes) in the "Replace with" box.
  • Click on the Replace All button (or press Alt+A).
  • When Word displays the number of replacements made, you'd select OK and choose Close.

Begin by creating a short Word document that contains the words Acme Software several times. Save the file and name it ACME.DOC. If you prefer, download a sample file to work with:

   http://www.officeletter.com/favtips/acme.doc


RECORDING THE MACRO

To create the macro, you need only start the Macro Recorder, execute the five steps described above, stop the Macro Recorder, and save the result. Here's how.

To record a macro:

  1. Use the Tools/Macro/Record New Macro command.

  2. In the Record New Macro dialog box, enter "ChangeAcme" in the Macro Name box. (Note: your macro name cannot contain spaces or special characters. Stick with letters, numbers, and underscores.)

  3. In the "Store Macro in" drop-down menu, choose ACME.DOC. You're only going to work with this macro from within the test document. If you wanted to use the macro for multiple documents, and if your company is changing its name you certainly would, then choose "All Documents (Normal.dot)" instead.

  4. You can change the text in the Description box if you wish.

  5. Click on OK.

Figure 1 - Preparing to record the macro

Word is now in record mode. A very small toolbar (I'll call it the Stop Toolbar for lack of a better name) is placed on top of your document; it contains the title Stop, and has two buttons.


Figure 2 -
The Record Toolbar

The square button is used to Stop the macro recording. You'll click it when you're done recording the steps you want for turning your actions into VBA code.

The other button looks like two vertical lines and a dot. It's the Pause button, used when you want to temporarily interrupt the recording process. When you pause the recording, the button remains depressed until it is pressed again (and recording resumes). (When the Pause button is depressed, the Tool Tip displayed when you hover over the button reads Resume Recording.)

With the Macro Recorder turned on (that is, not paused), execute the Search and Replace command manually:

  1. Move to the beginning of the document (press Ctrl+Home). You want to make sure the macro executes from the top of your file so all occurrences are changed. Otherwise, Word will perform some replacements, reach the bottom of the file, and pop-up a dialog box and ask if you want to continue through the rest of the document (that is, move to the top and continue replacements until it reaches the point where it began). That's an added complication you don't want to deal with, so be sure your searches start at the beginning of the document.

  2. Use the Edit/Replace command, or press Ctrl+H.

  3. Enter "Acme Software" (without the quotes) in the "Find what" box.

  4. Enter "AcmeSoft" (without the quotes) in the "Replace with" box.

  5. Click on the Replace All button (or press Alt+A).

  6. Word displays the number of replacements made. Choose OK, then select Close. (If you are using the Office Assistant, such as the Paper Clip, the replacements are shown as a text bubble. Click on the text bubble to close it, then choose the Close button on the Find and Replace dialog box.)

  7. Stop the macro recording: Click on the Stop Recording button of the Stop Toolbar. The Stop Toolbar disappears. Alternatively, choose the Tools/Macro/Stop Recording command.


RUNNING THE MACRO

At this point, the Word document no longer contains the words Acme Software, so if you run the macro, there won't be anything for it to replace. Set the document back to its original state:

  1. Use the Edit/Undo Replace All command (or click on the Undo button or press Ctrl+Z).

So where is the macro? It's only in memory at the moment. It won't be saved until you save ACME.DOC. DON'T DO THAT YET!

It's time to test the macro. To run it:

  1. Use the Tools/Macro/Macros command or press Alt+F8.

  2. Choose the macro name (ChangeAcme) from the list of macros.

  3. Click on Run or press Alt+R.

Word runs the macro. It begins at the top of the document, makes the replacements, and stops.


SAVING YOUR MACRO

  1. Repeat step 13, changing all occurrences of AcmeSoft back to Acme Software.

  2. Use the File/Save command to save ACME.DOC.

Because you chose to save the macro with the document itself (in Step 3 above), not as part of Normal.dot, the macro can only be run when you open ACME.DOC. It cannot be run from within any other document.

Congratulations! You now have a file that contains text and a macro.

If you close ACME.DOC and open it again, you may encounter a warning screen. Word detects when a file contains a macro and asks if you want to enable macros. If you see this screen, click on Enable macros.




ENTERING YOUR MACRO DIRECTLY

Often times you have macro code that you want to use in Word. That's not something you can record, using the techniques above. Instead, experienced users (and those new users not afraid to experiment) can enter macro code directly into the VBA editor. Here's how.

  1. Open the Visual Basic for Applications editor by using the Tools/Macro/Visual Basic Editor command. (See Figure 7 later in this article.)

  2. In the Project panel (in the upper-left corner), click on the Project (documentname) node to expand the hierarchy, where documentname is the name of the currently-open document where you want to place the macro code.

  3. Click on the Modules node, and then the NewMacros node.

  4. In the main editing panel, be sure the left pulldown menu at the top of the workarea reads (General). (If it doesn't, change it to this option.)

  5. If there is no text present in the editing area, skip to the next step. Otherwise, position your cursor at any line after an "End Sub" statement and before another "Sub" statement.

  6. Paste or type your macro code into the editing area. You may notice that the editor changes the color and spacing of your code in order to make it uniform and easier to read.

  7. Use the File/Save (documentname) command to save your macro code and return to your document.

Note: If you want your macro to be available to all Word documents, not just the current document, click on Normal, not Project (documentname) in Step 1 above. Continue with Step 2, opening the NewMacros node of the Normal level, not the Project (documentname) level.


Tip: Handling Long Lines of VBA Code

Many times an individual line of code in a macro may be quite long. To make it fit in a print publication (or online), macro authors split lines, placing an underscore (_) character at the end of one or more lines as a continuation character. When you encounter such line breaks, be sure you enter them exactly as given.

For example, suppose you have the following macro code:

Sub InsPix()
   Selection.InlineShapes.AddPicture FileName:= _
      "C:\Folder\mysig.jpg", LinkToFile:=False, _
      SaveWithDocument:=True
End Sub

You'll want to enter each line shown as a separate line in the VBA editor. That is, when you get to the end of the second line, you'll enter the underscore, then press Enter to move to the next (blank) line and enter the code for the third line. (If the editor displays a "Compile error" message, don't worry; just choose OK and proceed with your work.

Alternatively, you can remove the underscore character at the end of a line and continue entering the text on the same line.For example, you could enter the macro as:

Sub InsPix()
   Selection.InlineShapes.AddPicture FileName:="C:\Folder\mysig.jpg", LinkToFile:=False, _
      SaveWithDocument:=True
End Sub

^ Top of page ^



PART 2: CREATING MACRO SHORTCUTS

In Part One you learned how to use the Macro Recorder to turn your keystrokes and mouse movements into Visual Basic for Application (VBA) code. You also learned how to run the macro using the Tools/Macro/Macros command. In this section we'll learn how to run the macro from a toolbar button, menu command, or keyboard shortcut.

Open the file you created from Part 1. If you didn't create the file, you can download it at

    http://www.officeletter.com/favtips/acme.doc

The macro, ChangeAcme, is the only macro in the document. To follow this discussion, please be sure that ACME.DOC is the ONLY Word document you have open.


ASSIGNING THE MACRO TO A TOOLBAR BUTTON


    Figure 3 - Select the
    macro for the shortcut




    Figure 4 - Macro as a toolbar button

  1. Right-click within the gray area at the top of Word -– inside the menu bar, within the toolbar (but not on a button), or in the gray, empty area to the right of the menus and toolbar buttons. Choose the Customize command. (Alternative: use the Tools/Customize command.)

  2. Choose the Command tab in the Customize dialog box. In the Save in pull-down menu at the bottom, choose ACME.DOC. In the Categories list, choose Macros, and in the right panel you'll find your ChangeAcme macro (it's listed as Project.NewMacros.ChangeAcme). See Figure 3.

  3. Drag the macro's icon (it looks like a small organization chart or flowchart) to the toolbar. Don't click on Close just yet. You need to be in "customize" mode to make further changes.

  4. Word displays the macro name (Project.NewMacros.ChangeAcme) on the button's face (see Figure 4). Right-click on the button, select Name, and enter the text you want to appear on the button (for example, Acme). In the menu that appears, be sure you have checked the "Image and Text" option. This allows you to display a name and an icon on the button's face. (Note: If the menu options disappear when you select "Image and Text", simply right-click on your new toolbar button to re-display it.)

  5. Choose Change Button Image to assign a predefined image (a heart or a smiley-face, for instance). If you wish, you can change the icon using the basic icon editor -- launch the editor using the "Edit Button Image..." option.

  6. After you are satisfied with the appearance of the button, choose the Close button on the Customize dialog box.

To undo the change, use the Tools/Customize command, then right-click the button and choose Delete. Alternatively, hold down the Alt key and drag the icon off the toolbar.


ADDING A MACRO AS A MENU OPTION

You can also add the ChangeAcme macro to the menu system. For example, since your macro performs a search and replace operation, you might want to put the macro under the same menu where the Find and Replace command is found: under the Edit menu.

  1. Right-click within the gray area at the top of Word -– inside the menu bar, within the toolbar (but not on a button), or in the gray, empty area to the right of the menus and toolbar buttons. Choose the Customize command. (Alternative: use the Tools/Customize command.)

  2. Choose the Command tab in the Customize dialog box. In the Save in pull-down menu at the bottom, choose ACME.DOC. In the Categories list, choose Macros, and in the right panel you'll find your ChangeAcme macro (it's listed as Project.NewMacros.ChangeAcme).

    Figure 5 - Macro as option
    in the Edit menu

  3. Drag the macro icon (it looks like a small organization chart or flowchart) to the Edit menu. Word opens the Edit menu.

  4. Move down the Edit menu. Your mouse cursor changes into a button, and as you move a dark vertical line appears in the menu at the position where you will be "dropping" (inserting) the new command. When you are satisfied with the location, release the mouse, dropping the line in the desired position (see Figure 5). The line changes to text: Word displays the Macro name. I chose to add the command between the Replace and the Go To commands.

  5. The Edit menu remains displayed. Right-click on your macro and change the name. I chose Ac&me. This allows me to press Alt+E (to open the Edit menu), then press M (the Acme shortcut) to execute the macro.

  6. In the Customize dialog box, choose Close.

To undo the change, use the Tools/Customize command, then click on the menu item (to select it) and right-click your mouse and choose the Delete command.


RUNNING THE MACRO FROM A KEYBOARD SHORTCUT

  1. Right-click within the gray area at the top of Word –- inside the menu bar, within the toolbar (but not on a button), or in the gray, empty area to the right of the menus and toolbar buttons. Choose the Customize command. (Alternative: use the Tools/Customize command.)

  2. Choose the Command tab in the Customize dialog box. Choose the Keyboard... button at the bottom of the Customize dialog box.

  3. In the "Save changes in" pull-down menu at the bottom, choose ACME.DOC. In the Categories list, choose Macros, and in the right panel you'll find your ChangeAcme macro.

  4. Click inside the "Press new shortcut key" text box. Press the shortcut key you'd like to use, such as Ctrl+Alt+A (this combination will appear in the text box). If the key combination hasn't been assigned to another command, Word displays "Currently assigned to: [unassigned]". Click on the Assign button.

  5. The shortcut appears in the "Current keys" list. Select Close, then Close again.

To undo the change, use the Tools/Customize command, then click on Keyboard, choose Macros in the left-hand list, and choose your macro from the right-hand list. Select the shortcut from the Current keys list and click on Remove.


DELETING A MACRO

To remove a macro from your file, use the Tools/Macro/Macros command (shortcut: Alt+F8). Choose the macro from the list, then click on the Delete button.

If you delete a macro you have assigned to a toolbar button, menu, or keyboard shortcut, running the shortcut has no effect. That is, nothing happens. (Word does, however, generate a warning message.) You'll have to remove the toolbar button, menu item, or keyboard shortcut manually.


SAVING YOUR CHANGES

When you save ACME.DOC, your toolbar, menus, and keyboard shortcuts are saved with your document. Since we assigned the changes to ACME.DOC, rather than NORMAL.DOT, the changes will be reflected only in ACME.DOC. Open any other document (or create a new one) and your menus, toolbars, and keyboard shortcuts won't reflect the shortcut changes you've made in this lesson.


ASSIGN A SHORTCUT AT MACRO CREATION?

In Part One of these instructions, you could have created the toolbar or menu shortcut at the time you created the macro, but that would have been far too much to absorb in a single lesson. Now that you know how macros can be assigned, take a look at your options when you start the Macro Recorder.

  • Use the Tools/Macro/Record New Macro command.

  • In the Record Macro dialog box, notice the "Assign macro to" box. Here you can assign the yet-to-be-created macro to a keyboard shortcut or toolbar button.

    • If you choose Toolbars, drag the icon from the Commands panel to the toolbar. Then right-click on the button and you'll see the same options as were described earlier in this story.

    • If you choose Keyboard, you'll see the Customize Keyboard dialog box previously described.

    You cannot assign the macro to a menu until the macro has been created.

^ Top of page ^



PART 3: CHANGING MACROS WITH THE VBA EDITOR

In the last two parts of this series you've seen how to record a macro and assign it to a toolbar, menu, or keyboard shortcut. In this part you'll learn how to make a change using the VBA (Visual Basic for Applications) Editor.


CHANGE IS INEVITABLE

Uh, oh. Suppose your supervisor stops by your desk and says that Acme Software has decided to change its name to AcmeSoftInc. Time to panic? Not at all. No need to re-create the original macro from scratch. Instead, as you'll learn this week, the Visual Basic for Applications text editor is all you need.

Yes, the VBA editor is a highly complex tool. But you can "get in and get out" and make changes without much trouble. Here's how.

If you haven't worked through the previous two sections, you can download the file you'll need at

   http://www.officeletter.com/favtips/acme2.doc

Be sure to rename it ACME.DOC. The ACME2.DOC file contains the sample text, the macro, and a toolbar button (with a heart icon) on the toolbar.

To follow this discussion, please be sure that ACME.DOC is the ONLY Word document you have open.


OPENING THE MACRO EDITOR

  1. Use the Tools/Macro/Macros command or press Alt+F8.

  2. Choose the macro name (ChangeAcme) from the list of macros (see Figure 6).

    Figure 6 - Select the macro you want to change

  3. Word opens a brand new window (see Figure 7). Inside the editor is a window with your macro. The editor is similar to a text editor.

    Figure 7 - The VBA editor

    The first line reads

       Sub ChangeAcme()
    

  4. Many of the commands probably won't make sense, but you'll see Selection.Find.Replacement.ClearFormatting that should give you a hint that it is associated with the find-and-replace command. Look for this text:
       .Replacement.Text = "AcmeSoft"
    

    Change "AcmeSoft" to "AcmeSoftInc" in that line.

  5. Use the File/Save command (or press Ctrl+S). This saves ACME.DOC, including your macro.

  6. Press Alt+F11 to return to your Word document. Run the ChangeAcme macro: use any of the shortcuts we discussed last week, or use the Tools/Macro/Macro command (or press Alt+F8), choose ChangeAcme, and press Run.

    Now Acme Software is replaced with AcmeSoftInc.

    The Microsoft Visual Basic editor window is still open, so switch back to it (or press Alt+F11) and, unless you want to make another change, close the VBA editor window (click on the "X" in the upper-right corner or choose the File/Close and Return to Microsoft Word command).

    If you keep the editor open and close ACME.DOC, your document will be removed from the Project panel (at the left), allowing you to edit macros in other documents. The editor is a separate application, but intimately tied to Word.

That's all there is to it!


COPYING THE MACRO

Now let’s suppose that you're satisfied with the ChangeAcme macro and want it to be available for every document you open in Word. You need to copy the Macro from ACME.DOC to NORMAL.DOT.

Press Alt+F8, then click on the Organizer button. On the left-hand side of the window is the ACME.DOC file and its macros, called NewMacros. On the right is the destination, which in this case is NORMAL.DOT by default.

You could choose the Close File button, then press the Open File button and choose another document file, but in this case you want the macro available everywhere, which means it belongs in NORMAL.DOT.

Click on the Copy button and the macro is copied to NORMAL.DOT.


EXPERIMENT

There's much more you can do with macros, of course. I encourage you to use the Macro Recorder feature to copy your keyboard and mouse movements, then examine the text of the resulting macro within the VBA editor. While it won't all make sense, as you've seen here, simple changes ARE easy to make.

-- James E. Powell

 

Links


 ©2001-2003 Masterware, Inc. All Rights Reserved.