An object class belonging to a COM component may contain other classes or class
collections. Subclasses or collection elements might repeat this nesting structure and could,
in turn, contain other object classes and collections.
Only objects in the topmost level of the component's hierarchy can be created
directly with CreateObject(). The subordinate objects in the hierarchy either already exist
as subobjects of the higher objects or must be created with methods of the higher objects.
In ActiveX terminology, the highest-level objects are said to be Public and Creatable,
whereas subobjects are Dependent or Public but Not Creatable. That is these subobjects can
be seen by client applications, but the only way to reference them or create them is to go
through the Public objects above them in the object model hierarchy.
For instance, one of the Excel component hierarchy's topmost objects is the
Application object. An Application object can contain (among other objects) a collection object
called WorkBooks, which in turn can contain individual WorkBook objects. Each WorkBook object
of the collection can in turn contain a collection of WorkSheet objects, and each WorkSheet object
can contain a Range object. You would initialize the Application object as you've seen in the
previous two sections:
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
You must always refer to the WorkBooks collection indirectly through the Application
object, adding a new WorkBook to the WorkBooks collection with the Add method:
objExcel.Workbooks.Add
As an alternative, you can declare another variable to point to the newly added
element of the Workbook collection. The Excel server contains a Workbook class. You can declare
a variable of this class and use the Set keyword to assign the declared object variable to the
results of the Add method:
Dim wb As Excel.Workbook
Set wb = objExcel.Workbooks.Add
You could add a Worksheet object to the Workbook object's Worksheet collection.
A full example might look like Listing 10.2.
LISTING 10.2
USING THE EXCEL OBJECT MODEL TO MANIPULATE EXCEL OBJECTS
'General
declarations section
Option Explicit
Private objExcel As Excel.Application
Private wb As Excel.Workbook
Private ws As Excel.Worksheet
Private Sub Form_Load()
Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Add
Set ws = wb.Worksheets.Add
End Sub
You can use the Object Browser or refer to a COM component application's documentation
to find out about its object model hierarchy, as mentioned in the section of this
chapter on the Object Browser.
