This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel XP/X

Automating Excel with Macros

The purpose of this document is to introduce you to another of Excel's features for automating your work: macros.

A macro is a shortcut for performing a series of actions and is useful for automating complex or repetitive tasks. Macros are helpful if the work is being shared with someone else, because it is easier to explain one step-activate (run) the macro-than it is to explain 20 steps. For a macro to be worthwhile, the series of actions you wish to accomplish must be consistent.

When creating a macro, planning can be very helpful. Knowing specifically what you want the macro to accomplish, and in what order, can save a lot of time, editing, and frustration.

When the macro is saved, the information is stored as a part of the current template and will be available for all documents based on that template. You can also copy macros to documents based on other templates.

Once a macro is created, you can activate (run) it by using the Macro dialog box. Or, if you choose to assign a shortcut to the macro, you can activate it by pressing a keyboard combination, selecting a menu choice, or making a choice from a toolbar.

The following topics are included in this document:

return to topMacro Tips

As you are creating a macro, you will be requested to assign a name to the macro and to indicate where the macro will be stored. The following guidelines and tips can help you make the best decision.

Naming a Macro

Macro names must:

AutoExec the macro runs when Excel is opened
AutoExit the macro runs when you exit Excel
AutoOpen the macro runs when you open a worksheet
AutoNew the macro runs when you create a new worksheet
AutoClose the macro runs when you close the current worksheet

Saving a Macro

A macro is saved as part of a template. If the use of the macro is limited to specific types of worksheets and you have a template for that type of worksheet, it should be stored in that template.

Assigning Macros

While it is not required to assign a macro to a second location, doing so can make the macro easier to use and save more time. Depending on your personal style of working with Excel, you may want to assign the macro to a keystroke combination, a menu, or a toolbar. You can assign the macro to all three if you want.

Recording Notes

Macros can be created by recording keystrokes (not mouse actions like selecting text) or by writing the macro using the Visual Basic programming language. This document focuses on macros that can be created through the recording method.

WARNING: If you are working with a multi-layer dialog box (one with tabs at the top), only the changes on the current tab are recorded when you click OK. If you want to record changes on more than one layer, you will need to access the dialog box once for each layer, clicking OK as you complete the changes on that layer.

return to topCreating & Recording a New Macro

The Record New Macro command of Excel creates macros using the Record Macro dialog box pictured below. Using this dialog box, you will name, describe, and assign the storage and access locations of the macro(s) you create.

  1. From the Tools menu, select Macro» Record New Macro…
    The Record Macro dialog box appears.
    Record Macro dialog box

  2. In the Macro name text box, type the name for the new macro
    For information on naming conventions see Naming a Macro.

  3. OPTIONAL: In the Shortcut key text box, type a letter or number that can be used to activate the macro

  4. OPTIONAL: In the Description text box, type a short narrative description for the macro

  5. From the Store macro in pull-down list, select the appropriate option
    See Saving a Macro.

  6. Click OK
    The Macro Record toolbar should appear in the window with your document.
    Macro Record toolbar

  7. Perform the activity that you want the macro to accomplish

  8. When done, on the Macro Record toolbar, click STOP RECORDING
    OR
    From the Tools menu, select Macro » Stop Recording
    The macro will be saved as part of the template you chose.

  9. Continue with Running a Macro

return to topRunning a Macro

We recommend that you save your file before running the macro. Then, if the results are undesirable you can close the file without saving it and reopen the version that was saved just prior to running the macro.

Running a Macro: Dialog Box Option

  1. If the insertion point is critical, set the insertion point in the appropriate location

  2. From the Tools menu, select Macro » Macros…
    The Macro dialog box appears.

  3. Under Macro name, select the macro you want to run
    If you do not see the macro, make sure you are looking for it in the correct workbook by using the Macros in pull-down list.
    HINT: The Description text box contains the description of the macro which you provided while creating it.

  1. Click RUN

Running a Macro: Keyboard Option

  1. If the insertion point is critical, set the insertion point in the appropriate location

  2. Press the appropriate keyboard combination

return to topSuspending a Macro

If you are running a macro and need to stop it, you may do so.

  1. Press [Esc]
    OR
    On the Macro toolbar, click STOPStop button

return to topDeleting a Macro

WARNING: If you delete a macro, it will be removed from the template and will not be available to any workbook.

  1. From the Tools menu, select Macro » Macros…
    The Macro dialog box appears.

  2. From the list of macros, select the macro you want to delete

  3. Click DELETE
    A confirmation dialog box appears.

  4. Click YES
    NOTE: Deleting a macro does not remove the effects of the macro once it has been run; it makes the macro unavailable for future use.

return to topAdvanced Macros

Interactive Macros: Some macros allow you to ask the user for input. In order to do this, you need to work with the Visual Basic programming language.

Editing a Macro: The editing of macros requires some programming knowledge. Simple macros are often easier to recreate than edit.

Excellence. Our Measure. Our Motto. Our Goal.