Page 155 - Excel Progamming Weekend Crash Course

h540629 ch10.qxd 9/2/03 9:34 AM Page 130

130 Saturday Morning

To read a cell’s comment, use the Comment.Text property. If you try to read this property
on a cell that does not have a comment, an error occurs; therefore you should verify that a
cell has a comment first, as shown here. This code uses the Debug.Print statement to dis-
play the comment text in the Immediate Window.
If Not SomeRange.Comment Is Nothing Then
Debug.Print SomeRange.Comment.Text
End If

Range Columns, Rows, and Size
You can determine the location of a range using its Column and Row properties. They work
as follows:
Column. Returns a number identifying the first column (the left-most column) in
the range (1 = A, 2 = B, and so on).
Row. Returns the number of the first (top-most) row in the range.

To determine the size of a range, use the Columns.Count and the Rows.Count properties.
These properties return the number of columns and rows, respectively, in the specified
range.

Reading Data from Ranges
If a range points to a single cell, its Value property returns whatever is in the cell. What
happens when a range points to a block of two or more cells? The Value property then
returns an array (as a type Variant) that contains the data in all the cells.
When you are not sure that a range refers to a single cell, your code needs to check to
see whether the data returned by the Value property is an array. This is done using the
IsArray function:
Dim x
x = SomeRange.Value
If IsArray(x) Then
‘ SomeRange pointed to multiple cells, and x contains an array.
Else
‘ SomeRange pointed to one cell, and x contains a single value.
End If

The array that is returned has two dimensions and is the same size as the range. The first
dimension of the array indexes the rows in the range, and the second dimension indexes the
columns. To illustrate, look at the selected range in Figure 10-5, comprising cells B4:C7. If
you created a range that included these cells and obtained its Value property, you would
get an array as follows:

The first dimension of the array has four elements, for the four rows in the range.
The second dimension of the array has two elements, for the two columns in the
range.