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)
 
Search JabSto ::




Custom Search