Microsoft Office Tutorials and References
In Depth Information
The Macro Recorder
ActiveCell.FormulaR1C1 = “Mar”
ActiveCell.FormulaR1C1 = “Apr”
ActiveCell.FormulaR1C1 = “May”
ActiveCell.FormulaR1C1 = “Jun”
To test this macro, start by activating a cell other than cell B1. Then choose the Developer ➜
Code ➜ Macros command. Select the macro name and then click the Run button. The month
names are entered beginning at the active cell.
Notice that I varied the recording procedure slightly in this example: I activated the beginning
cell before I started recording. This step is important when you record macros that use the active
cell as a base.
Although it looks rather complicated, this macro is actually quite simple. The first statement
simply enters Jan into the active cell. (It uses the active cell because it’s not preceded by a
statement that selects a cell.) The next statement uses the Select method (along with the Offset
property) to move the selection one cell to the right. The next statement inserts more text, and
so on. Finally, the original cell is selected by calculating a relative offset rather than an absolute
cell. Unlike the preceding macro, this one always starts entering text in the active cell.
You’ll notice that this macro generates code that appears to reference cell A1 — which
may seem strange because cell A1 wasn’t even involved in the macro. This code is
simply a byproduct of how the macro recorder works. (I discuss the Offset property later
in this chapter.) At this point, all you need to know is that the macro works as it should.
The point here is that the recorder has two distinct modes, and you need to be aware of which
mode you’re recording in. Otherwise, the result may not be what you expected.
By the way, the code generated by Excel is more complex than it needs to be, and it’s not even
the most efficient way to code the operation. The macro that follows, which I entered manually, is
a simpler and faster way to perform this same operation. This example demonstrates that VBA
doesn’t have to select a cell before it puts information into it — an important concept that can
speed things up considerably.
ActiveCell.Offset(0, 0) = “Jan”
ActiveCell.Offset(0, 1) = “Feb”
ActiveCell.Offset(0, 2) = “Mar”
ActiveCell.Offset(0, 3) = “Apr”
ActiveCell.Offset(0, 4) = “May”
ActiveCell.Offset(0, 5) = “Jun”