3. 
Creating Macros
3.1 Where Macros Are Stored
Macros in Excel are saved in Excel workbooks. You can save macros along with the contents of a workbook you create, or in a special workbook which you can use to store macros that you want available all the time, known as the "Personal Macro Workbook." The file name of that workbook is "personal.xlt" (2003) or "personal.xlsb" (2007/2010). This workbook is created the first time you record a macro to be stored in the Personal Macro Workbook, and will be opened (but hidden) each time you start Excel.
Macros stored in the Personal Macro Workbook are always available to run. Macros stored in any other workbook are available only when that particular workbook is open in Excel. Macros can also be stored in templates, but for to simplify matters templates will not be discussed in this tutorial.
In this tutorial we will assume that your macros are stored in the Personal Macro Workbook.
3.2 Creating a Macro
Macros can be created in two ways: by recording keystrokes
and mouse clicks ("recording"), or by typing the macro
commands in the VBA editor ("programming").
Only program operations done by keystroke or mouse
click can be recorded. Decision processing, variables, loops and
other features of the programming language must be typed into the
macro code in the VBA editor. (Inserting programming commands is discussed later
in the tutorial, in the section on editing macros.)
3.3 Macro Recording
To begin recording a macro, click on Tools/Macro/Record (2003) or on the Developer tab of the ribbon, click on Record Macro in the Code group (2007/2010). A dialog box will appear, asking you for the a
name for the macro, an optional description, and where to store the macro (see above). You can also optionally assign
the macro to a ctrl+key combination. The default macro name is
"Macro" with a number appended. You should change this to a descriptive name that you will recognize and understand.
From this point the actions you take in the program are saved in the macro; that is what is meant by "recording." Once you start recording, the results of your keystrokes and/or mouse clicks will be stored in the macro, until you end the recording by clicking on Tools/Macro/Stop Recording (2003) or on Stop Recording on the Ribbon (2007/10).
Note that what will be recorded is the result of your keystrokes or mouse clicks, not the keystrokes or mouse clicks themselves. For example, if you record the mouse/key sequence to enter a value in cell B4, the macro will record the action as changing the value in that particular cell, but not any extra keys you might have pressed and canceled or deleted. If you begin an action and then cancel it entirely there is no "result," so nothing is recorded in the macro.
Also note that with certain actions that involve entering settings on a dialog box, such as page setup and find, when you record a macro all the settings on the dialog box are recorded, not just the one(s) you change. This subject is covered in detail later in this tutorial.
3.4 Macros to Record as Examples
Later chapters use examples from macro code, so you will probably want to have some examples to refer to. I suggest recording macros that perform the following actions (the name to give each macro is in parentheses). Save them in the Personal Macro Workbook.
- Select row 2 (TUT1)
- Insert a worksheet by clicking on Insert/Worksheet on the menu (2003), or by clicking Insert/Sheet on the Home tab of the Ribbon (2007/2010) (TUT2)
- Enter "hello" in Cell C2 (TUT3)
- Position the cursor in Cell A1, then use Find to move to the cell containing "hello" (TUT4)
- Change the workbook's print orientation to landscape (TUT5)