The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:

obj ect.Offset(rowOffset, columnOffset)

The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or zero. The example that follows enters a value of l2 into the cell directly below the active cell:

ActiveCell.Offset(l,0).Value = l2

The next example enters a value of l5 into the cell directly above the active cell:

ActiveCell.Offset(-l,0).Value = l5

If the active cell is in row 1, the Offset property in the preceding example generates an error because it cannot return a Range object that doesn't exist.

The Offset property is quite useful, especially when you use variables within looping procedures. I discuss these topics in the next chapter.

When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, I used the macro recorder to generate the following code. I started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.

Sub Macrol()

ActiveCell.FormulaRlCl = "l" ActiveCell.Offset(l, 0).Range("Al").Select ActiveCell.FormulaRlCl = "2" ActiveCell.Offset(l, 0).Range("Al").Select ActiveCell.FormulaRlCl = "3"

ActiveCell.Offset(-2, 0).Range("Al").Select End Sub

Notice that the macro recorder uses the FormulaRlCl property. Normally, you want to use the Value property to enter a value into a cell. However, using FormulaRlCl or even Formula produces the same result.

Also notice that the generated code references cell A1 - a cell that was even involved in the macro. This notation is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range("Al"), and the macro still works perfectly:

Sub Modified Macro1()

ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Select End Sub

In fact, here's a much more efficient version of the macro (which I wrote myself) that doesn't do any selecting:

Sub Macro1()

ActiveCell = 1 ActiveCell.Offset(1, 0) = 2 ActiveCell.Offset(2, 0) = 3 End Sub

4 PREV

NEXT

+1 0

Responses

  • ruta
    How to use relative reference with range in VB?
    8 years ago

Post a comment