How can I create a proper Collection in VBA?

user_14608750 0
Viewed 207

I am trying to convert a large 3 dimensioned Array into a series of class modules. I have each next class stored as an array in the previous class. It goes like Brand -> Products -> Lots.

I have successfully created this interaction and can access them by name like:

Sub test()
    Dim MyBrand As Brand
    Set MyBrand = New Brand
    MyBrand.Name = "Company1"
    MyBrand.AddProduct "Shoes"
    MyBrand.Products("Shoes").AddLot "240502"
    MsgBox MyBrand.Products("Shoes").Lots(0) 'Correctly Displays "240502"
End Sub

But then I wanted to create an object group that can save multiple Brand objects and access them like Brands("Company1").

If I used an array inside a class module, I'd end up with Brands.Brand("Company1"). If I used a Collection, I'd have to use indexes like Brands(1).

Is there a way to create a proper object group so that I can mimic the syntax of groups like Application.Workbooks and refer to members by Name?

vba excel collections
2 Answers

A lot of the magic behind custom collections depends on hidden attributes that you cannot edit from within the VBE; you need to export (and remove from the project when prompted) the class module, edit its magic member attributes in Notepad/Notepad++, save changes, and then re-import the module into the project.

That's obviously tedious and error-prone, but there's a (much) better way.

In order to support this:

Set shoesProduct = MyBrand.Products("Shoes")

You can define Products as a Dictionary and call it a day, but then encapsulation as a concept is... well, taking a beating here (whether the internal collection is a Dictionary, a Collection, or a .NET ArrayList should typically be an implementation detail that the rest of the code doesn't need to care about).

I suspect the Brand class has too many responsibilities and "is" the product collection; best practices would be to have the Brand.Products property defined as follows:

Public Property Get Products() As Products

So you'll want to have a Products class (very much like the Workbook.Worksheets and Workbook.Sheets properties both return a Sheets collection object) that encapsulates a private, module-level VBA.Collection field (possibly keyed, but you can't access or iterate the keys of a collection).

The Products custom collection class needs an Item default property (the name Item is a convention); the implementation just pulls the item from the private encapsulated Collection:

'@DefaultMember
Public Property Get Item(ByVal Index As Variant) As Product
    Set Item = ThePrivateCollection.Item(Index)
End Property

If you are using Rubberduck, this @DefaultMember annotation/comment is going to trigger an inspection result about the annotation and the corresponding hidden attribute(s) being "out of sync"; right-click that inspection result and pick "Adjust attribute values" to have Rubberduck generate the hidden code for you and deal with the annoying export/delete-edit-reimport cycle.

Otherwise, you'll want to manually edit the hidden VB_UserMemId member attribute that makes it the class' default member:

Public Property Get Item(ByVal Index As Variant) As Product
    Attribute Item.VB_UserMemId = 0
    Set Item = ThePrivateCollection.Item(Index)
End Property

And with that, MyBrand.Products("Shoes") becomes equivalent to MyBrand.Products.Item("Shoes").

Perhaps you want to iterate all the products in the collection, too?

For Each Product In MyBrand.Products
    Debug.Print Product.Name
Next

In order to do this, you need a special "enumerator" member that forwards the enumerator from the encapsulated collection:

'@Enumerator
Public Property Get NewEnum() As IUnknown
    Set NewEnum = ThePrivateCollection.[_NewEnum]
End Property

Again, Rubberduck annotations greatly simplify doing this, but everything Rubberduck does, you can also do manually if you like:

Public Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = ThePrivateCollection.[_NewEnum]
End Sub

And now For Each iteration works for your custom object collection!

If a Lot was more than just a String value (i.e. an actual object type), then the Product class could use a Lots custom collection too - but since a Lot is really just a String value (or is it?), then Product can simply encapsulate a Dictionary, and have a Lots property that exposes the Items array:

Public Property Get Lots() As Variant
    Lots = ThePrivateLotsDictionary.Items
End Property

Note, that's simpler than using a Collection, because with a collection you'd need to iterate it and copy each item to an array in order to return the items without exposing the collection itself (exposing Lots() As Collection makes the AddLot member completely redundant).

As for the Brands collection itself, heed Tim Williams' advice and use a Dictionary data structure.

user_11885130

You can use a Scripting.Dictionary with Name as the key:

Sub test()
    
    Dim MyBrand As Brand
    Dim Brands As Object
    
    Set Brands = CreateObject("scripting.dictionary")
    
    Set MyBrand = New Brand
    MyBrand.Name = "Company1"
    MyBrand.AddProduct "Shoes"
    MyBrand.Products("Shoes").AddLot "240502"
    
    Brands.Add MyBrand.Name, MyBrand
    
    MsgBox Brands("Company1").Products("Shoes").Lots(0)

End Sub
user_4788840
Related