Microsoft Office Tutorials and References
In Depth Information
Excel 2007 and later worksheets contain more than 17 billion cells compared with a
mere 17 million in previous versions. Because of this dramatic increase, the Count
property — which returns a Long — may return an error if there are more than
2,147,483,647 cells to be counted. You can use the CountLarge property instead of
Count to be safe, but beware that CountLarge does not work in older versions of Excel.
In the CELLCOUNT function, the following statement will handle any size range (includ-
ing all cells on a worksheet):
CELLCOUNT = rng.CountLarge
The Parent property
The Parent property returns an object that corresponds to an object's container object. For a Range object, the
Parent property returns a Worksheet object (the worksheet that contains the range).
The following function uses the Parent property and returns the name of the worksheet of the range passed as an
argument:
Function SHEETNAME(rng)
SHEETNAME = rng.Parent.Name
End Function
The following formula, for example, returns the string Sheet1:
=SHEETNAME(Sheet1!A16)
The Name property
The Name property returns a Name object for a cell or range. To get the actual cell or range name, you need to
access the Name property of the Name object. If the cell or range does not have a name, the Name property re-
turns an error.
The following Function procedure displays the name of a range or cell passed as its argument. If the range or
cell does not have a name, the function returns an empty string. Note the use of On Error Resume Next. This
handles situations in which the range does not have a name.
Function RANGENAME(rng)
On Error Resume Next
RANGENAME = rng.Name.Name
If Err.Number <> 0 Then RANGENAME = “”
End Function
The NumberFormat property
The NumberFormat property returns the number format (as a string) assigned to a cell or range. The following
function simply displays the number format for the upper-left cell in a range:
Function NUMBERFORMAT(cell)
NUMBERFORMAT = cell.Range(“A1”).NumberFormat
Search JabSto ::




Custom Search