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 and examine the new code in the VBE. Excel generates the following code:

Sub Macro1()

Range("B1" ActiveCell Range("C1" ActiveCell Range("D1" ActiveCell Range("E1" ActiveCell Range("F1" ActiveCell Range("G1" ActiveCell Range("B1" End Sub

.Select

FormulaR1C1

.Select

FormulaR1C1

.Select

FormulaR1C1

.Select

FormulaR1C1

.Select

FormulaR1C1

.Select

FormulaR1C1

.Select

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

To execute this macro, choose Developer Code Macros (or press Alt+F8) and select Macrol

(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 this works, erase the cells in B1:D1 and then perform the following steps:

1. Activate cell B1.

2. Choose Developer

Code

Record Macro.

3. Click OK to begin recording.

4. Click the Use Relative Reference button to change the recording mode to relative. After you click this button, it appears in a different color.

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.Offset(0, 1) .Range( ActiveCell.FormulaR1C1 = "Feb" ActiveCell.Offset(0, 1) .Range( ActiveCell.FormulaR1C1 = "Mar" ActiveCell.Offset(0, 1) .Range( ActiveCell.FormulaR1C1 = "Apr" ActiveCell.Offset(0, 1) .Range( ActiveCell.FormulaR1C1 = "May" ActiveCell.Offset(0, 1) .Range( ActiveCell.FormulaR1C1 = "Jun" ActiveCell.Offset(0, -5).Range("A1").Select End Sub

"A1").Select "A1").Select "A1").Select "A1") .Select "A1") .Select

You can execute this macro by activating a worksheet and then choosing the Developer ■-v Code LV Macros 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 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.

Note 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.

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.

By the way, the code generated by Excel is more complex than it need 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.

Sub Macro3()

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)

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

Sub Macro4()

With ActiveCell

.Offset

0,

0)

= "Jan

.Offset

0,

1)

= "Feb

.Offset

0,

2)

= "Mar

.Offset

0,

3)

= "Apr

.Offset

0,

4)

= "May

.Offset

0,

5)

= "Jun

End With End Sub

End With End Sub

Or, if you're a VBA guru, you can impress your colleagues by using a single statement:

Sub Macro5()

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

0 0

Post a comment