Relative or absolute

When recording your actions, Excel normally records absolute references to cells. In other words, when you select a cell, it will remember that exact cell (not the cell relative to the current active cell). To demonstrate how this works, perform these steps and examine the code:

1. Activate a worksheet and start the macro recorder.

2. Activate cell B1.

3. Enter Jan into cell B1.

4. Move to cell C1 and enter Feb.

5. Continue this process until you've entered the first six months of the year in B1:G1.

6. Click cell B1 to activate it again.

7. Stop the macro recorder.

Excel generates the following code:

Sub Macro1()

Range("B1").Select ActiveCell.FormulaR1C1 Range("C1").Select ActiveCell.FormulaR1C1 Range("D1").Select ActiveCell.FormulaR1C1 Range("E1").Select ActiveCell.FormulaR1C1 Range("F1").Select ActiveCell.FormulaR1C1 Range("G1").Select ActiveCell.FormulaR1C1 Range("B1").Select End Sub

To execute this macro, choose the Tools ^ Macro ^ Macros command (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'll 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'll want to use relative recording to record the macro.

The Stop Recording toolbar, which consists of only two buttons, is displayed when you are recording a macro. You can change the manner in which Excel records your actions by clicking the Relative Reference button on the Stop Recording toolbar. This button is a toggle. When the button appears in a pressed state, the recording mode is relative. When the button appears normally, you are recording in absolute mode. You can change the recording method at any time, even in the middle of recording.

To see how this works, erase the cells in B1:D1 and then perform the following steps:

1. Activate cell B1.

2. Choose Tools ^ Macro ^ Record New Macro.

3. Click OK to begin recording.

4. Click the Relative Reference button (on the Stop Recording toolbar) to change the recording mode to relative.

When you click this button, it appears pressed.

= "Jan" = "Feb" = "Mar" = "Apr" = "May" = "Jun"

5. Enter the first six months' names in B1:G1, as in the previous example.

6. Select cell B1.

7. Stop the macro recorder.

With the recording mode set to relative, the code that Excel generates is quite different:

Sub Macro2()

ActiveCell

.FormulaR1C1 =

"Jan"

ActiveCell

.0ffset(0, 1).

Range("

A1"

)

.Select

ActiveCell

.FormulaR1C1 =

"Feb"

ActiveCell

.0ffset(0, 1).

Range("

A1"

)

.Select

ActiveCell

.FormulaR1C1 =

"Mar"

ActiveCell

.0ffset(0, 1).

Range("

A1"

)

.Select

ActiveCell

.FormulaR1C1 =

"Apr"

ActiveCell

.0ffset(0, 1).

Range("

A1"

)

.Select

ActiveCell

.FormulaR1C1 =

"May"

ActiveCell

.0ffset(0, 1).

Range("

A1"

)

.Select

ActiveCell

.FormulaR1C1 =

"Jun"

ActiveCell

.0ffset(0, -5)

.Range(

"A1

"

End Sub

You can execute this macro by activating a worksheet and then choosing the Tools ^ Macro command. Select the macro name and then click the Run button.

You'll also notice that I varied the procedure slightly in this example: I activated the beginning cell before I started recording. This is an important step 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 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 might seem strange because cell A1 was not even involved in the macro.This is simply a by-product 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.

By the way, the code generated by Excel is much more complex than it need be, and it's not 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 also speed things up considerably.

Sub Macro3()

ActiveCell

.0ffset(0,

0) =

"Jan"

ActiveCell

.0ffset(0,

1) =

"Feb"

ActiveCell

.0ffset(0,

2) =

"Mar"

ActiveCell

.0ffset(0,

3) =

"Apr"

ActiveCell

.0ffset(0,

4) =

"May"

ActiveCell

.0ffset(0,

5) =

"Jun"

End Sub

In fact, this macro can be made even more efficient by using the With-End With construct:

Sub Macro4()

With ActiveCell

0ffset(0,

0)

= "Jan"

0ffset(0,

1)

= "Feb"

0ffset(0,

2)

= "Mar"

0ffset(0,

3)

= "Apr"

0ffset(0,

4)

= "May"

0ffset(0,

5)

= "Jun"

End With End Sub

Or, if you're a VBA guru (like the technical editor for this book), you can impress your colleagues, and do it in one statement:

Sub Macro54()

ActiveCell.Resize(,6)=Array("Jan","Feb","Mar","Apr","May","Jun") End Sub

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 will not be what you expected.

Was this article helpful?

0 0

Post a comment