Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

Total = Total + cell.Offset(0, 1) ‘right

Total = Total + cell.Offset(1, –1) ‘lower left

Total = Total + cell.Offset(1, 0) ‘below

Total = Total + cell.Offset(1, 1) ‘lower right

Some useful properties of ranges

Previous sections in this chapter give you examples that used the
Value
property for a range.

VBA gives you access to many additional range properties. Some of the more useful properties

for function writers are briefly described in the following sections. For complete information on a

particular property, refer to Excel’s online help.

The Formula property

The
Formula
property returns the formula (as a string) contained in a cell. If you try to access the

Formula
property for a range that consists of more than one cell, you get an error. If the cell

does not have a formula, this property returns a string, which is the cell’s value as it appears in the

Formula bar. The following function simply displays the formula for the upper-left cell in a range:

Function CELLFORMULA(cell)

CELLFORMULA = cell.Range(“A1”).Formula

End Function

You can use the
HasFormula
property to determine whether a cell has a formula.

The Address Property

The
Address
property returns the address of a range as a string. By default, it returns the

address as an absolute reference (for example, $A$1:$C$12). The following function, which is not

all that useful, returns the address of a range:

Function RANGEADDRESS(rng)

RANGEADDRESS = rng.Address

End Function

For example, the following formula returns the string
$A$1:$C$3
:

=RANGEADDRESS(A1:C3)

The formula below returns the address of a range named
MyRange
:

=RANGEADDRESS(MyRange)