| Microsoft Office 2000/Visual Basic Programmer's Guide | |
You can use the Property Set procedure to create an object that wraps an existing built-in object; that is, it contains unique properties and methods, and also provides access to all the built-in properties and methods of the existing object. In this way you can add additional properties, methods, and events to a built-in object.
For example, the ListComboWiz.xla add-in in the ODETools\V9\Samples\OPG\Samples\CH11 subfolder on the Office 2000 Developer CD-ROM contains the BoundList class, which can represent a list box or combo box on an Excel worksheet. List boxes and combo boxes on a worksheet differ from those on UserForms; they are ActiveX controls that belong to the OLEObjects collection of the Worksheet object. The BoundList custom object contains some custom properties and methods, and also provides access to all the properties and methods of the OLEObject class that's built into Excel. What makes this possible is an object property that sets and returns a reference to an object of type OLEObject, which refers to the list box or combo box on the worksheet. This property maintains the reference to the OLEObject object for the BoundList class.
The following example shows the Property Set and Property Get procedures for the ActiveXControl object property of the BoundList class. The ActiveXControl object property requires both a Property Set procedure and a Property Get procedure. The Property Set procedure stores a reference to an object of type OLEObject in a private module-level variable, p_oleCtl. The Property Get procedure returns that reference.
Private p_oleCtl As OLEObject
Property Set ActiveXControl(oleCtl As OLEObject)
If p_oleCtl Is Nothing Then
Set p_oleCtl = oleCtl
End If
End Property
Property Get ActiveXControl() As OLEObject
Set ActiveXControl = p_oleCtl
End Property
You can name this property whatever you want; in this case it's named ActiveXControl because it sets and returns a reference to an ActiveX control on a worksheet.
Note that the Property Set procedure makes the ActiveXControl property a write-once property. The Property procedure checks whether the private object variable that stores the reference to the OLEObject object already points to a valid object. If it does, the property has already been set and won't be set again. The write-once property ensures that the reference to the OLEObject object is set only once, when it is first added to the BoundLists collection.
In the ListComboWiz.xla add-in, the ActiveXControl property is set in the Add method of the BoundLists class. First, the Add method creates a new instance of the BoundList class. Then, in one line of code, it uses the built-in Add method of the OLEObjects collection to add a new OLEObject object (the new list box or combo box) to the worksheet, and assigns a reference to this OLEObject object to the ActiveXControl property of the BoundList object. Setting the ActiveXControl property passes the reference to the new OLEObject object to the Property Set procedure for the ActiveXControl property. The ActiveXControl property stores the reference to the OLEObject object, to be maintained for the lifetime of that BoundList object.
In essence, the Add method of the custom BoundLists collection wraps the built-in Add method of the OLEObjects collection, and the ActiveXControl property wraps the built-in OLEObject object. The code within the custom objects still uses the OLEObject object and OLEObjects collection to control a list box or combo box on a worksheet. A programmer working with the BoundList object and BoundLists collection, however, sees only the properties and methods of those custom objects, rather than the internal code that's managing OLEObject objects.
The Add method for the BoundLists collection is shown here. Also shown is the opgListType custom enumeration, which provides the constants that specify whether to create a list box or a combo box; one of these constants is passed to the procedure for the lngListType argument:
Enum opgListType
COMBO_BOX
LIST_BOX
End Enum
Public Function Add(strListName As String, _
lngListType As opgListType) As BoundList
' Add new BoundList object to BoundLists collection and return reference to object.
' This procedure not only creates the BoundList object but also creates a new
' OLEObject on the worksheet and returns a reference to it, which is then
' assigned to the ActiveXControl property of the new BoundList object. The BoundList
' object "wraps" the OLEObject object.
Dim objBndLst As BoundList
Set objBndLst = New BoundList
' Create either a list box or a combo box.
Select Case lngListType
Case opgListType.LIST_BOX
Set objBndLst.ActiveXControl = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=100, _
Top:=100, Width:=150, Height:=150)
Case opgListType.COMBO_BOX
Set objBndLst.ActiveXControl = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=100, _
Top:=100, Width:=150, Height:=25)
End Select
' Name the new list.
objBndLst.ListName = strListName
' Add BoundList object to BoundLists collection.
p_colLists.Add objBndLst, objBndLst.ListName
' Return added object.
Set Add = objBndLst
End Function
Once the ActiveXControl property has been set, you can use it to access the properties and methods of the OLEObject corresponding to the list box or combo box. For example, the following code fragment returns the height of the first bound list in the BoundLists collection to the Immediate window:
? BoundLists.Item("Combo1").ActiveXControl.Height
Note The Item property of the BoundLists collection is a custom property that's used to return a reference to a BoundList object in the collection. Because the custom BoundLists collection is created from a class module, it does not have a default Item property like built-in collections do. When you build a class to represent a collection, you must implement the Item property yourself, and to return a reference to an object in the collection, you must explicitly refer to the Item property. In addition, you can't use the For Each…Next statement to loop through a collection that is built from a custom class; instead, you must use a For…Next loop and maintain a counter variable that acts as an index into the collection.