The Range Object as a Collection

The Range object is rather unusual in that it often acts like a collection object as well as a noncollection object. For instance, it has an Item method and a Count property. On the other hand, the Range object has many more noncollection-type members than is typical of collection objects. In particular, the average member count among all other collection objects is 19, whereas the Range object has 158 members.

Indeed, the Range object should be thought of as a collection object that can hold other Range objects. To illustrate, consider the following code:

Dim rng as Range

Set rng = Range("A1", "C5").Cells

MsgBox rng.Count ' displays 15

Set rng = Range("A1", "C5").Rows

MsgBox rng.Count ' displays 5

Set rng = Range("A1", "C5").Columns

MsgBox rng.Count ' displays 3

In this code, we alternately set rng to the collection of all cells, rows, and columns of the range A1:C5. In each case, MsgBox reports the correct number of items in the collection. Note that the Excel model does not have a cell, row, or column object. Rather, these objects are Range objects; that is, the members of rng are Range objects.

When we do not specify the member type, a Range object acts like a collection of cells. To illustrate, observe that the code:

Dim rng As Range

MsgBox rng.Count

MsgBox rng(6).Value ' row-major order displays the number of cells in the range and then the value of cell 6 in that range (counted in row-major order; that is, starting with the first row and counting from left to right). Also, the code:

Dim rng As Range

Dim oCell As Range

For Each oCell In rng

Debug.Print oCell.Value Next will cycle through each cell in the range rng, printing cell values in the Immediate window.

0 0

Post a comment