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

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes.

object.Range(cell1) object.Range(cell1, cell2)

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). The following are a few examples of using the Range property.

You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell A1 on Sheet1 of the active workbook.

Worksheets("Sheet1").Range("A1").Value = 12.3

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:

Worksheets("Sheet1").Range("Input").Value = 100

The example that follows enters the same value in a range of 20 cells on the active sheet. If the active sheet isn't a worksheet, the statement causes an error message.

ActiveSheet.Range("A1:B10").Value = 2

The next example produces the same result as the preceding example:

Range("A1", "B10") = 2

The sheet reference is omitted, however, so the active sheet is assumed. Also, the Value property is omitted, so the default property (which is Value for a Range object) is assumed. This example also uses the second syntax of the Range property. With this syntax, the first argument is the cell at the top left of the range, and the second argument is the cell at the lower right of the range.

The following example uses the Excel range intersection operator (a space) to return the intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement enters 3 in cell C6:

Range("C1:C10 A6:E6") = 3

Finally, if the range you're referencing is a noncontiguous range (a range where not all the cells are adjacent to each other), you can use commas to serve as a union operator. For example, the following statement enters the value 4 in five cells that make up a noncontiguous range. Note that the commas are within the quote marks.

Range("A1,A3,A5,A7,A9") = 4

So far, all the examples have used the Range property on a Worksheet object. As mentioned, you can also use the Range property on a Range object. For example, the following line of code treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 in the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Range object. Therefore, the statement that follows enters a value of 5 into the cell directly to the right and one row below the active cell:

ActiveCell.Range("B2") = 5

Fortunately, you can access a cell relative to a range in a much clearer way—the Offset property. We discuss this property after the next section.