Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 119

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

object.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 0. The example that follows enters a value of 12 into the cell directly below the active cell:

ActiveCell.Offset(1,0).Value = 12

The next example enters a value of 15 in the cell directly above the active cell:

ActiveCell.Offset(-1,0).Value = 15

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

The Offset property is useful, especially when you use variables in looping procedures. We 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, we used the macro recorder to generate the following code. We started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.

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

The macro recorder uses the FormulaR1C1 property. Normally, you want to use the Value property to enter a value in a cell. However, using FormulaR1C1 or even Formula produces the same result. Also, the generated code references cell A1—a cell that wasn't 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 ("A1"), 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, you can enter this much more efficient version of the macro. In this version, you don't do any selecting.

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