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 2003/2004

Automating Excel with Macros

The purpose of this document is to introduce you to macros, another Excel feature for automating your work.

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 several 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.

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 topAccessing the Record New Macro Command

You can access the Record New Macro command through a menu or from a toolbar.

Accessing the Record New Macro Command: Menu Option

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

Accessing the Record New Macro Command: Toolbar Option

If you would prefer to have the Record New Macro command available on a toolbar, follow the instructions:

Windows:

  1. From the View menu, select Toolbars » Customize...
    The Customize dialog box appears.
    Customize dialog box - Commands tab

  2. Select the Commands tab

  3. From the Categories scroll box, select Tools

  4. From the Commands scroll box, click and drag MACROS... and RECORD NEW MACRO... to the Standard toolbar
    NOTE: You may only click and drag one command to the toolbar at a time.

  5. Click CLOSE

  6. On the Standard toolbar, click RECORD MACRO...Record Macro button
    The Record Macro dialog appears.

Macintosh:

  1. From the View menu, select Toolbars » Customize Toolbars/Menus...
    The Customize Toolbars/Menus dialog box appears.
    Customize dialog box - Commands tab

  2. Select the Commands tab

  3. From the Categories scroll box, select Tools

  4. From the Commands scroll box, click and drag MACROS... and RECORD NEW MACRO... to the Standard toolbar
    NOTE: You may only click and drag one command to the toolbar at a time.

  5. Click OK

  6. On the Standard toolbar, click RECORD MACRO...Record Macro button
    The Record Macro dialog appears.

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. Access the Record Macro dialog box
    The Record Macro dialog box appears.
    Record Macro dialog box

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

  3. OPTIONAL: In the Shortcut key text box, type a letter 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
    NOTE: For more information, refer to Saving a Macro.

  6. Click OK
    The Macro Record toolbar appears 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 is 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…
    OR
    On the Standard toolbar, click RUN MACRORun Macro button
    The Macro dialog box appears.
    HINT: If the Run Macro button does not appear on the Standard toolbar, follow steps 1-5 in Accessing the Record New Record: Toolbar.

  3. From the Macro name scroll box, select the macro you want to run
    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]

  2. In the Microsoft Visual Basic dialog box, click END

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…
    OR
    On the Standard toolbar, click RUN MACRORun Macro button
    The Macro dialog box appears.
    HINT: If the Run Macro button does not appear on the Standard toolbar, follow steps 1-5 in Accessing the Record New Record: Toolbar.

  2. From the Macro name scroll box, 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.

  5. Repeat steps 2-4 until all the unwanted macros are deleted

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.