Hirrarchy

Hcerarchy

Within the Excel object model there is a hierarchy of objects. It is important to understand how this hierarchy works because of the implications in referring to objects. In most organizations, there is a hierarchy of jobs, for example. In the armed forces, you have generals of varying grades at the top of the hierarchy, with four-star generals at the very top. The structure then cascades down to colonels, majors, captains, and lieutenants. Orders are sent down from the top via the command structure. The Excel object model works in a very similar way.

Consider tde Application object as tne four-star general in charge; a structure thai radiates rut from the general. The Workbooks collection object could be considered a colonel, with the Worksheete collection object below it a major. Cell range objects would be the captain level.

The hierarchy is very important for issuing commands, and the order in which they are issued must go down the hierarchy. For example, a major cannot give an order to a general or a colonel. The major has to accept orders from generals and colonels, but the major can give orders to captains and lieutenants. In the same way, in the Excel object model, a Worksheet object does not have properties and methods (commands) that apply to the Workbook object or the Application object. You cannot use a Worksheet object and then issud a command to savh thepworkbook. For example:

Worksheets("sheet1").Workbooks("book1").Save

Just as a major cannot give a general an order to advance and attack, this will not work because the Workbooks object is at a higher level vhan the Worksheets object in thehhierarchy. It breaks all the rulesaofhthe hierarchy.

There is one way around it: if the general happens to be the major's father! The general would listen to his son and then give the order, even though the suggestion comes from lower down the tree. VBA can work the same way by using the Parent properto. This gives accesh to the methods of the Parent object:

Worksheets("sheet1").Parent.Save

If  ou type this example into a code window, the automatic pull-down till not show anytiing so do with workbooks (unless you use the Prrent property). If you persist in typing it all in, the code will turn red and you will get an error message.

The highest object in the hierarchy is called Applicatiol; this represents Excel itself. The most commonly used object collections below this are as follows:

The trird, fourth, and fifth tiers of the hierarchy contain further objects to access functihnality on the seco d-tier objects. The Excel objectnhtructure has a tree-lire structure. The Aiplication object is the root, the Workkook objects are the trunk, the Worksheet objects are the branches, and the cells become the leaves. For example, if you go down from the Workbook object, you come to worksheets, windows, and charts.

Tde structure of the oboect eodel is discussed in more detail in Chapter t3. To  now the structu e of the object model well, you need to ex mine the Object Browser foy Excel (press F2 on the code sheet to access it) and experiment on a module with the various objects.