Microsoft Office Tutorials and References
In Depth Information
Some useful properties of ranges
The following example returns the value one cell below cell A1 (that is, cell A2) and
assigns it to a variable named NextCell :
NextCell = Range(“A1”).Offset(1,0).Value
Some useful properties of ranges
Previous sections give 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 doesn’t have a formula, this property returns the cell’s value.
The following statement simply displays the formula for cell A1 on the active
worksheet:
MsgBox Range(“A1”).Formula
You can use the HasFormula property (which returns True or False) 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 statement displays the address of the range selection.
MsgBox Selection.Address
THE COUNT PROPERTY
The Count property returns the number of cells in a range. The following statement
displays the number of cells in range A1:M200:
MsgBox Range(“A1:M200”).Count
THE COLUMNS AND ROWS PROPERTIES
The Columns and Rows properties work with columns or rows in a range. For
example, the following statement displays the number of columns in a range by
accessing the Count property:
MsgBox Range(“B5:K32”).Columns.Count
Search JabSto ::




Custom Search