Using macros to speed up your work

Power User Corner

By Paul Cornell

Do you have a series of tasks that you perform over and over again in Microsoft Office? Did you know that you can automate these tasks in Office by using macros?

Applies to
Microsoft Office XP

In this month's column, I introduce you to macros and show you how to use a tool called the Macro Recorder that helps you create and manage macros to speed up your work.

What's a macro?

A macro is a set of computer instructions that you can record and associate with a shortcut key combination or a macro name. Then, when you press the shortcut key combination or click the macro name, your computer program carries out the instructions of the macro. This saves you time by replacing an often-used, sometimes lengthy series of actions with a shorter action. For example, instead of clicking several menus and buttons to add text to your business documents in Microsoft Word, you can record those steps in a macro and then just click the macro to add the text in one step.

Office lets you automate not only keystrokes, but also most of the actions you can perform in Office. Almost anything you can do with the Office menu choices, with the toolbar buttons, and in the document editing environment can be automated with macros.

Try it out!

Let's record a simple macro. For this example, let's say that your company requires you to insert some standard legal text at a particular position of every Microsoft Word document you create. Here's how you can automate this process in Word with a macro:

  1. On the Tools menu in Word, point to Macro, and then click Record New Macro. This displays the Record Macro dialog box.

Word Macro Recorder dialog box

  1. In the Macro name box, type InsertLegalText.
  2. In the Store macro in box, click All Documents (Normal.dot).
  3. Click OK. The Recording toolbar appears.

Word Macro Recording toolbar

  1. Type the following: Copyright XYZ Corporation. You may not modify, copy, or distribute any information contained in this document without our prior permission.
  2. On the Recording toolbar, click Stop Recording.
  3. Open another new Word document and, on the Tools menu, point to Macro, and then click Macros. This displays the Macros dialog box.

Word Macro dialog box

  1. In the Macros in list, click Normal.dot (global template).
  2. In the Macro name list, click InsertLegalText, and then click Run. The text you typed in step 5 appears in the new document.

How does it work?

The Macro Recorder acts like a tape recorder. As you work, it records your keystrokes and mouse button clicks by translating them into Microsoft Visual Basic® for Applications (VBA) code. If you're curious about what was recorded in the Try it out! section, on the Tools menu, point to Macro, and then click Macros. Click InsertLegalText, and then click Edit. Here's the VBA code:

Sub InsertLegalText()
'
' InsertLegalText Macro
' Macro recorded {date} by {name}
'
    Selection.TypeText Text:= _
        "Copyright XYZ Corporation. You may not modify, copy, or dist"
    Selection.TypeText Text:= _
        "ribute any information contained in this document without ou"
    Selection.TypeText Text:="r prior permission."
    
End Sub

Let's explore the tools we used in the Try it out! section.

Using the Macro Recorder

As its name implies, the Macro Recorder allows you to record macros. It is available in Microsoft Excel, Microsoft PowerPoint®, and Word. To start the Macro Recorder in one of those programs, on the Tools menu, point to Macro, and then click Record New Macro.

The buttons that we didn't use in the Try it out! section (and that are not obvious) include:

Button Purpose
Toolbars (Available only in Word) Lets you assign the macro to a toolbar button. You can also use the Customize dialog box (on the Tools menu) in Office to assign a macro to a corresponding menu choice or a toolbar button.
Keyboard (Available only in Word) Lets you assign the macro to a shortcut key combination.

Using the Macros dialog box

The Macros dialog box allows you to create, edit, and manage your macros in Office. The Macros dialog box is available in Excel, Microsoft Outlook®, PowerPoint, and Word. To access the Macros dialog box in one of those programs, on the Tools menu, point to Macro, and then click Macros.

 Note   There is no Macros dialog box in Microsoft Access or Microsoft FrontPage®.

The buttons that we didn't use in the Try it out! section (and that are not obvious) include:

Button Purpose
Step Into Allows you to execute the macro's instructions line by line.
Organizer (Available only in Word) Opens the Organizer dialog box, which lets you copy macros between documents, rename macros, or delete macros.
Options (Available only in Excel) Opens the Macro Options dialog box, which allows you to associate an available shortcut key combination with the macro.

In next month's column

Next month, I will show you how to use the Visual Basic Editor and some of its common features. The Visual Basic Editor is the tool in which macros are recorded by the Macro Recorder. You can also create, edit, and manage your own macros manually in the Visual Basic Editor.

In the months ahead, I will share some of my favorite macros with you. Until then, experiment with the Macros dialog box and the Macro Recorder.


About the author

Paul Cornell works for the Office Help team.