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.
Post a comment