What Is a Macro?

As you work with Excel, you might discover yourself repeating many actions and commands. For example, every time you create a new worksheet, you might immediately enter a series of titles (such as months) across one row or format a set of numbers using the currency style.

Although you can make some repetitive work more efficient by using the toolbar or templates (discussed in Hour 6, "Using Excel Templates"), you might find it easier to create a macro to repeat a sequence of actions and commands.

Macro? graphics/newterm_icon.gif A macro is a sequence of keystrokes, mouse actions, and other commands that you record for later use.

You store macros in a macro sheet, a special type of Excel worksheet that is very similar to a regular worksheet. You must have a macro sheet open to be able to use the macros written in that file.

Each macro has three parts:

  • Macro name

  • Macro shortcut key

  • Macro steps

The macro name is a description you use to manage and run the macro. For example, a macro you create to change the font for data on the worksheet can be called Font_change.

The macro shortcut key is an optional key combination you can use to run the macro. For example, you can assign the shortcut key Ctrl+Shift+F to run the Font_change macro.

The macro steps are simply the commands expressed in the Visual Basic language that execute when you run the macro. These steps are a list of instructions that Excel executes in sequence, starting from the first line and moving down to the last line.

The first command should be Sub, a special command that tells Excel the macro has begun its operation. The last command should be End Sub, a special command that tells Excel the macro has finished its operation. For example, examine the macro in Figure 20.1. Notice the three parts to the macro.

Figure 20.1. Macro instructions for changing the font.


Macros are useful for automating repetitive or complex tasks. Although a macro is a series of programming instructions, you do not need to know anything about programming to create one. Excel offers a macro-recording feature that translates your actions into macro instructions.

    Part I: Excel Basics