How to Record and Run a Macro
Part 2: Recording a Macro
(page 2 or 2)
Storing Your Macro
If you click on the drop down arrow for the Store macro in selection box, you will notice that you have three options in which to store your macro:
- Personal Macro Workbook
- New Workbook
- This Workbook
Let's say you are working in a workbook named Expenses.xls. You want to create a macro that will be used only in the Expenses workbook that is currently opened. You would choose the This Workbook option to store your macro. If, on the other hand, you want your macro to be available to any workbook, you would choose to store your macro in your Personal Macro Workbook. The New Workbook option is seldom used. You could use it if creating a new workbook is part of your macro.
Many Excel users think that if you choose the This Workbook option to store your macro in the Expenses workbook, you can only run your macro in that workbook. That is inaccurate. Here's the rule: The workbook in which the macro is stored must be opened in order to run that macro in other workbooks. So, as long as the Expenses workbook is opened, you can use the macro stored in that workbook in any other workbook that is opened.
Excel creates your Personal Macro Workbook the first time you choose this option for storage. Remember that the workbook in which macros are stored has to be opened to use its macros, so where is the Personal Macro Workbook when other workbooks are opened? Excel hides it. The Personal Macro Workbook is saved to the XLStart folder with the filename Personal.xls. When you launch Excel, this workbook is opened automatically, but it's hidden. To unhide it, you can click on the Windows menu and then click Unhide (but you probably want to keep it hidden unless you need to edit a macro).
Absolute versus Relative Settings
After you name your macro, choose where to store it, and provide a description in the Record Macro dialog box and then click OK, the Record Macro dialog box closes and the Stop Recording toolbar appears.
The Macro toolbar has a Relative Reference button that can be turned on or off. When the Relative Reference setting is turned on, it's shaded and surrounded by a blue border.
As a general rule, if you want the macro to run in the same cell or range each time, record it absolutely. If you want the macro to run in any cell or range of your choosing, record it relatively.
Selecting a cell or range before or after you start the macro recorder also affects how your macro will run. In Part 1, we recorded a simple macro. Our first step after we started recording was to select cell A1 even if it was already selected. By recording the macro absolutely and selecting A1 after the we started recording, our macro will always run in cell A1.
Had we recorded your macro with the Relative Reference setting, we could run our macro in any cell.
Recording Your Macro
You understand by now that preparation is a very important part of recording your macro. Before you start recording you will choose a name for your macro, you might assign a shortcut key, you choose where your macro will be stored, and you will give your macro a description. You also will have written down the steps you want your macro to perform and will have practice following those steps. Finally, you may also choose to record your macro absolutely or relatively.
Now all you need to do is to record your macro. After you record your macro, you can run it. In Part 3, we discuss the different ways you can run your macros.