Excel VBA Macro Programming
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 the Application object as the four-star general in charge; a structure that radiates out from the general. The Workbooks collection object could be considered a colonel, with the Worksheets 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 issue a command to save the workbook. 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 than the Worksheets object in the hierarchy. It breaks all the rules of the 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 property. This gives access to the methods of the Parent object:
Worksheets("sheet1").Parent.Save
If you type this example into a code window, the automatic pull-down will not show anything to do with workbooks (unless you use the Parent 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 Application; this represents Excel itself. The most commonly used object collections below this are as follows :
| Object Collection | Description |
|---|---|
| Dialogs | Collection of built-in dialogs in Excel |
| Windows | Used to access different windows in Excel |
| Workbooks | Collection of all current workbooks loaded into Excel |
| CommandBars | Collection of menu items in Excel |
| Worksheets | Collection of all worksheets within current workbooks in Excel |
The third, fourth, and fifth tiers of the hierarchy contain further objects to access functionality on the second- tier objects. The Excel object structure has a tree-like structure. The Application object is the root, the Workbook 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 .
The structure of the object model is discussed in more detail in Chapter 13. To know the structure of the object model well, you need to examine the Object Browser for Excel (press F2 on the code sheet to access it) and experiment on a module with the various objects.