Share via

Wrapping a Built-in Object

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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 provides access to all the built-in properties and methods of the existing object. This way, you can add additional properties, methods, and events to a built-in object.

List boxes and combo boxes on a worksheet differ from those on UserForms; they are Microsoft® ActiveX® controls that belong to the OLEObjects collection of the Worksheet object. The BoundList custom object contains some custom properties and methods, and provides access to all the properties and methods of the OLEObject class that's built into Microsoft® 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 is named ActiveXControl because it sets and returns a reference to an ActiveX control on a worksheet.

Note that the Property Setprocedure makes the ActiveXControl property a write-once property. The Propertyprocedure 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 will not 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.

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.

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

When 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 as 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 cannot 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.

See Also

Why Build Your Own Objects? | Basic Class Concepts | Creating Property Procedures | Creating Events and Event Procedures | Extending Objects Through Interfaces | Designing Object Models | Creating Custom Objects for Web Pages