Understanding object parents

As you know, Excel's object model is a hierarchy: Objects are contained in other objects. At the top of the hierarchy is the Application object. Excel contains other objects, and these objects contain other objects, and so on. The following hierarchy depicts how a Range object fits into this scheme:

Application object Workbook object Worksheet object Range object

In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's parent is the Workbook object that contains the worksheet, and a Workbook object's parent is the Application object.

How can this information be put to use? Examine the SheetName VBA function that follows. This function accepts a single argument (a range) and returns the name of the worksheet that contains the range. It uses the Parent property of the Range object. The Parent property returns an object: the object that contains the Range object.

Function SheetName(ref) As String

SheetName = ref.Parent.Name End Function

The next function, workbookName , returns the name of the workbook for a particular cell. Notice that it uses the Parent property twice. The first Parent property returns a worksheet object, and the second Parent property returns a workbook object.

Function WorkbookName(ref) As String

WorkbookName = ref.Parent.Parent.Name End Function

The AppName function that follows carries this exercise to the next logical level, accessing the Parent property three times. This function returns the name of the Application object for a particular cell. It will, of course, always return Microsoft Excel .

Function AppName(ref) As String

AppName = ref.Parent.Parent.Parent.Name End Function

0 0

Post a comment