Objects Range, Selection and ActiveCell

2.11. Objects Range, Selection and ActiveCell

Object Range follows the Worksheet object in the object hierarchy. It allows working with the following elements of Excel:

range of cells;

range of columns;

range of rows;

single cell.

Let us consider some properties of the Range object.

 Formula — the Excel formula with operands (cell addresses) in the A1 reference style.

For example, operators

Range("C2:F8").Formula = "=$A$4+COS($A$10)"

Range("D:E").Formula = "=$A$4+COS($A$10)"

Range("2:2").Formula = "=$A$4+COS($A$10)"

Range("B3").Formula = "=$A$4+COS($A$10)"

set the Formula property. These operators are respectively used to put formula

=$A$4+COS($A$10)

into the following parts of the active worksheet:

range C2:F8;

columns D and E;

the 2nd row;

the B3 cell.

 FormulaR1C1 — the Excel formula with operands (cell addresses) in the R1C1 reference style.

For example, operator

Worksheets("Sheet1").Range("G1:H4").FormulaR1C1 = _ "=SQRT(R5C8)^3+7.3"

sets the FormulaR1C1 property. This operator is used for entering formula

=SQRT($H$5)^3+7.3

into the G1:H4 range on the Sheet1 worksheet (the R1C1 reference style option may be turned on or off, see p. 143).

 Address — the cell address.

 Offset — the range shifted relative to the selected (active) range accord-ing to two integers in parentheses.

In the Offset property, the first parameter (in parentheses) is the vertical shift, and the second parameter is the horizontal shift. A comma is placed between these parameters.

 Value — one of the following:

the array of the range values;

the cell value, if the Range object corresponds to the single cell.

 Columns — the collection of the range columns.

 Rows — the collection of the range rows.

 Cells — the collection of the range cells.

Note that we considered the Cells property regarding the Worksheet object in the previous section.

Below, we will list some methods of the Range object.

 Clear — the removal of the range contents.

For example, the following application of the Clear method clears cells A1:F7 on the Sheet1 worksheet:

Worksheets("Sheet1").Range("A1:F7").Clear

 Select — the selection (activation) of the range.

Objects of the Range type have properties and methods of the Range object.

When working with the Range type objects, it is convenient to use the ForEach…Next cycle, which is similar to cycle For…Next (p. 58). The syntax of this operator may be studied by means of the Excel help system started by pressing the F1 key when the VB window is active.

In the following program example, operator ForEach…Next is used for squaring the values of range A1:A6 on Sheet1 of the active workbook:

Listing 2.11 Sub Square()

Dim x As Range

For Each x In Worksheets("Sheet1").Range("A1:A6") x.Value = x.Value ^ 2

Next End Sub

2.11. Objects Range, Selection and ActiveCell

The Selection object allows working with the active (selected) cells. As the Range type object, the Selection object has properties Columns, Rows and Cells.

Thus, properties

Selection.Columns Selection.Rows Selection.Cells

are the collections of columns, rows and cells of the selected range, respectively.

The following example program inserts the multiplication table into the selected range on the active worksheet:

Listing 2.12 Sub MultiplicationTable()

Dim m As Integer, n As Integer Dim i As Integer, j As Integer

1: m = Selection.Rows.Count 'quantity of rows 2: n = Selection.Columns.Count 'quantity of columns 3: For i = 1 To m the quantity of objects in the Selection.Rows and Selection.Columns collections. Because of executing these operators, the quantities of rows and columns in the selected range are assigned to the m and n variables, respectively.

Operator 5 may have the following form:

Selection.Cells(i, j) = i * j Here, .Value is present implicitly.

We advise the reader to do the following:

1) enter program MultiplicationTable into the code window;

2) go to the Excel window and select the range for the multiplication table;

3) run program MultiplicationTable;

4) looking the execution result, verify the program correctness.

The Selection object can be used for recovering the selection. For example, in the graph subroutine (Section 4.8):

in the beginning, operator

Set wbOldSelection = Selection

assigns the selected range to the wbOldSelection variable of the Range type;

in the end, operator wbOldSelection.Select selects the wbOldSelection range.

It was mentioned in Section 2.8 that ActiveCell is a property of the Applica-tion object, corresponding to the active cell on the active worksheet of the active workbook. As the Range type object, the ActiveCell property has the properties and methods of the Range object.

The PropofRange program, given below, contains examples of using the properties and methods of the Range object.

1. Put 100 into cell B1, 200 into B2 and 300 into B3 on the Sheet1 work-sheet.

2. Put formula

=SUM(B1:B3)

into cell B4 on Sheet1, and click on the tick button of the Excel formula bar.

3. Go to Visual Basic Environment and insert a module into the active workbook.

4. Enter the following text into the code window:

Listing 2.13 Sub PropofRange()

1: Worksheets("Sheet1").Range("A1").Select 2: ActiveCell.Offset(2, 3).Select

3: MsgBox "Current cell — " & ActiveCell.Address 4: MsgBox "Value in cell B4 = " & _

Range("B4").Value

5: MsgBox "Formula in cell B4: " & _ Range("B4").Formula

End Sub

5. Run the PropofRange program execution after activating the Sheet1 worksheet. The window with message Current cell - $D$3 and the OK button appears (Fig. 2.21a).

2.11. Objects Range, Selection and ActiveCell

6. Click on the OK button. The window with message Value in cell B4 = 600 and the OK button appears (Fig. 2.21b).

7. Click on the OK button. The window with message Formula in cell B4:

=SUM(B1:B3) and the OK button appears (Fig. 2.21c).

8. Click on the OK button for terminating the execution.

Operator 1 of the PropofRange program selects cell A1 on the Sheet1 worksheet. Operator 2 selects the D3 cell, shifted 2 vertically and 3 horizontally relative to the A1 cell (property Offset and method Select of the Range object are figured in this operator). Further, three windows with the message and OK but-ton are sequentially displayed (Fig. 2.21).

Fig. 2.21a. The Excel worksheet with the first window

Fig. 2.21b. The second window

Fig. 2.21c. The third window