VBA: Is possible to create a class module which behave like a Collection?

Question

I'm wonder if is possible to create my-own Class which behave like a Collection. Better to show on the example:

My current knowledge:

Class Module MyClass

Public oCollection As Collection
Public OtherData as Long
... 'Another Variables, Properties, Functions and Subs

Module

...
For Each oItem In MyClass.oCollection 'I need to address Collection inside the object
  If oItem.OtherData = 0 Then
...

What I like to achieve:

...
For Each oItem In MyClass 'Want to be able to iterate over oCollection just from Class
  If oItem.OtherData = 0 Then
...

Thanks for your advice.

why? other than reducing the line by a few characters and reducing the readability (or at least the 'self documentation' of the original implementation), what would be the benefit? — sous2817, Dec 08 '20 at 14:37
Take a look [here](https://stackoverflow.com/questions/63848617/bug-with-for-each-enumeration-on-x64-custom-classes). It will show exactly how it's done but it will also show you the relevant bug on x64 — Cristian Buse, Dec 08 '20 at 14:45
@sous2817 You are probably right. I thought it againt and I wanted it mostly because poor design of my classes. The original reason was that `MyClass` props could return "modified" `MyClass`, or modified `oCollection` Collection. It was hard to decide (from prop name) what is returned, so I often forgot to add `.oCollection` and it throws error. But I didn't realize that problem is wrong object returned and tend to search for error in property code. — Lluser, Dec 09 '20 at 09:37

score 1 · Answer 1 · answered Dec 08 '20 at 15:57

You can avoid the bug described by @CristianBuse by using a Scripting.Dictionary and using the .items or .Keys. Methods to return arrays that can be iterated over using for each.

In answer to you question the wrapping of a collection or scripting dictioanry is good practise as it allows you to have strong typing for the host collection. You can also use the wrapping code to add validation to your input values or to extend the functionality of the host collection

The code below shows the wrapping of a scripting dictionary, validation on the add method and an extension that simplifies adding data to the scripting dictionary. These are just examples to demonstrate a point

Option Explicit

Private Type State

    Host                As scripting.Dictionary
    
End Type

Private s               As State

Private Sub Class_Initialize()

    Set s.Host = New scripting.Dictionary
    
End Sub


Public Property Get Item(ByVal ipKey As Long) As String
    Item = s.Host.Item(ipKey)
End Property

Public Property Let Item(ByVal ipValue As String, ByVal ipKey As Long)
    s.Host.Item(ipKey) = ipValue
End Property


Public Sub Add(ByVal ipKey As Long, ByVal ipValue As String)

    If ipKey > 10 Then
    
        Err.Raise _
            5 + vbObjectError, _
            "Size Error", _
            "Object based on Class " & TypeName(Me) & " is limited to 0-9 members"
            
        End
        
    End If
    
    s.Host.Add ipKey, ipValue
    
End Sub

Public Sub AddByIndex(ByVal ipArray As Variant)

    Dim myArray As Variant
    
    If Not IsArray(ipArray) Then
    
        myArray = Array(ipArray)
        
    Else
    
        myArray = ipArray
        
    End If
    
    Dim myNextKey As Long
    myNextKey = s.Host.keys(s.Host.Count - 1)
    Do While s.Host.Exists(myNextKey)
    
        myNextKey = myNextKey + 1
        
    Loop
    
    
    Dim myItem As Variant
    For Each myItem In myArray
    
        s.Host.Add myNextKey, myItem
        myNextKey = myNextKey + 1
        
    Next
        
End Sub

Public Function Items() As Variant
    Items = s.Host.Items
End Function

So you can now do

oclass.addbyindex Range("A1:A42").value

and

Dim myItem as Variant
For Each myItem in oClass.Items

Wrapping data into Scripting.Dictionary seems to be useful technique, but how it solve `Attribute [MethodName].VB_UserMemId = -4` bug? If I understand it correctly, its better to don't use `VB_UserMemId` attribute and address the collection directly `oClass.oCollection`. — Lluser, Dec 14 '20 at 09:19
You haven't understood my post. Using a scripting.dictionary you don't get the bug because in the example I provided you use the .Items method of the Scripting.Dictionary to return an variant containing an array of items (values in the scripting.dictionary) and the for loop iterates over the provided items array. — freeflow, Dec 14 '20 at 09:35

VBA: Is possible to create a class module which behave like a Collection?

1 Answers1