Microsoft Office Tutorials and References
In Depth Information
The Macro Recorder
ActiveCell.FormulaR1C1 = “Mar”
ActiveCell.FormulaR1C1 = “Apr”
ActiveCell.FormulaR1C1 = “May”
ActiveCell.FormulaR1C1 = “Jun”
To execute this macro from within Excel, choose Developer ➜ Code ➜ Macros (or press Alt+F8)
and select Macro1 (or whatever the macro is named) and click the Run button.
The macro, when executed, re-creates the actions that you performed when you recorded it.
These same actions occur regardless of which cell is active when you execute the macro.
Recording a macro using absolute references always produces the exact same results.
In some cases, however, you want your recorded macro to work with cell locations in a relative
manner. For example, you’d probably want such a macro to start entering the month names in
the active cell. In such a case, you want to use relative recording to record the macro.
You control how references are recorded by using the Developer ➜ Code ➜ Use Relative
References button. This button is a toggle. When the button appears in a different color, the
macro recorder records relative references. When the button appears in the standard color, the
macro recorder records absolute references. You can change the recording method at any time,
even in the middle of recording.
To see how relative referencing is recorded, erase the cells in B1:G1 and then perform the
Activate cell B1.
Choose Developer ➜ Code ➜ Record Macro.
Click OK to begin recording.
Click the Use Relative Reference button to change the recording mode to relative.
After you click this button, it appears in a different color.
Enter the first six months’ names in B1:G1, as in the previous example.
Select cell B1.
Stop the macro recorder.
With the recording mode set to relative, the code that Excel generates is quite different:
ActiveCell.FormulaR1C1 = “Jan”
ActiveCell.FormulaR1C1 = “Feb”