Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

Cnt = Range(“A1:C300”).Count

The
Range
property is also useful for referencing a single cell in a multicell range. For example,

you may create a function that is supposed to accept a single-cell argument. If the user specifies

a multicell range as the argument, you can use the
Range
property to extract the upper-left cell

in the range. The following example uses the
Range
property (with an argument of
“A1”
) to

return the value in the upper-left cell of the range represented by the cell argument.

Function Square(cell as Range)

CellValue = cell.Range(“A1”).Value

Square = CellValue ^ 2

End Function

Assume that the user enters the following formula:

=Square(C5:C12)

The
Square
function works with the upper-left cell in C5:C12 (which is C5) and returns the value

squared.

Many Excel worksheet functions work in this way. For example, if you specify a

multicell range as the first argument for the LEFT function, Excel uses the upper-left cell

in the range. However, Excel is not consistent. If you specify a multicell range as the

argument for the SQRT function, Excel returns an error.

The Cells property

Another way to reference a range is to use the
Cells
property. The
Cells
property accepts

two arguments (a row number and a column number), and returns a single cell. The following

statement assigns the value in cell A1 to a variable named
FirstCell
:

FirstCell = Cells(1, 1).Value

The following statement returns the upper-left cell in the range C5:C12:

UpperLeft = Range(“C5:C12”).Cells(1,1)

If you use the
Cells
property without an argument, it returns a range that consists of

all cells on the worksheet. In the following example, the
TotalCells
variable contains

the total number of cells in the worksheet: