Sunday, October 07, 2012

How to create a collection class in VBA

What you need is a NewEnum property of type IUnknown, which is a type exposed by the stdole library which Access, and presumably other VBA environments, references by default.
Private m_items As VBA.Collection

Private Property Get Items() As VBA.collection
   If m_items Is Nothing Then Set m_items = New VBA.Collection
   Set Items = m_items
End Property

Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = Items.[_NewEnum]
End Property
Note how the NewEnum property returns an enumerator for the collection object that is contained by the class module.  Also note that the code to populate that collection is not shown. You could define an Add method...
Sub Add(Item, Optional Key, Optional Before, Optional After)
    Items.Add Item, Key, Before, After
End Sub 
...that simply leverages the Add method of the collection contained by your class, but the cool thing is that consumers won't be able to distinguish your custom collection from a VBA.Collection.  But note also the Attribute line.  To add an attribute, export the module to text, add the Attribute in a text editor, and re-import the module.

No comments:

Post a Comment