Microsoft Office Tutorials and References
In Depth Information
Objects in Excel are arranged in a hierarchy. At the top of the hierarchy is the Application object (Excel itself). Ex-
cel contains other objects; these objects contain other objects, and so on. The following hierarchy depicts how a
Range object fits into this scheme:
Application object (Excel)
Workbook object
Worksheet object
Range object
In the lingo of object-oriented programming (OOP), a Range object's parent is the Worksheet object that contains
it. A Worksheet object's parent is the workbook that contains the worksheet. And a Workbook object's parent is
the Application object. Armed with this knowledge, you can make use of the Parent property to create a few useful
functions.
Returning the application's name
The following function, although not very useful, carries this discussion of object parents to the next logical
level by accessing the Parent property three times. This function returns the name of the Application object,
which is always the string Microsoft Excel.
Function APPNAME() As String
‘ Returns the application name of the cell that contains the
function
APPNAME = Application.Caller.Parent.Parent.Parent.Name
End Function
Returning Excel's version number
The following function returns Excel's version number. For example, if you use Excel 2013, it returns the text
string 15.0 .
Function EXCELVERSION() as String
‘ Returns Excel's version number
EXCELVERSION = Application.Version
End Function
Note that the EXCELVERSION function returns a string, not a value. The following function returns TRUE if
the application is Excel 2007 or later (Excel 2007 is version 12). This function uses the VBA Val function to
convert the text string to a value:
Function EXCEL2007ORLATER() As Boolean
EXCEL2007ORLATER = Val(Application.Version) >= 12
End Function
Returning cell formatting information
This section contains a number of custom functions that return information about a cell's formatting. These
functions are useful if you need to sort data based on formatting (for example, sorting all bold cells together).
Search JabSto ::




Custom Search