VBA Collection with case specific keys-CodePudding

I need to store class items in vba such that the collection can be string indexed and iterated like a normal collection. But the keys need to be case specific. To clarify I need this behavior:

classWaz:

...
Private mName As String
...
Public Property Get Name() As String
    Name=mName
End Property

Public Property Let Name(RHS As String)
    mName=RHS
End Property
...

Sub DoIt()
    Dim d As Desideratum, foo As classWaz, bar As classWaz, iter As classWaz
    Set d = New Desideratum '<- The thing I need - a collection with case specific keys

    Set foo = New classWaz
    foo.Name = "foo"

    Set bar = New classWaz
    bar.Name = "bar"

    d.Add Item:=foo, Key:="baz"
    d.Add Item:=bar, Key:="BAZ"

    For Each iter In d
        Debug.Print iter.Name
    Next
'Should print
'    foo
'    bar

    Set iter = d("baz")
    Debug.Print iter.Name
'Should print
'    foo

End Sub

The setup is that I have code using Collection that extensively uses these idioms. But I realized as I was testing that my use case requires case specific indexing and Collection doesn't support this.

I've tried Dictionary, but this doesn't appear to support class items. .Items() also returns an array, so a different iteration idiom would be needed. And I'm not aware of any way to force Collection to use vbCompareBinary. Even using Option Compare Binay, which is the default anyway.

I can think of a few workarounds; like having classes that had Collection typed properties, instead have methods GetWaz(wazName As String) As classWaz and an un-keyed GetWazes() As Collection. But this would be a lot of work I'd like to avoid if I can.

Thanks

CodePudding user response：

I don't see the issue with using a Dictionary:

Sub DoIt()
    ' Requires reference to Microsoft Scripting Runtime
    Dim d As Scripting.Dictionary
    Set d = New Scripting.Dictionary
    
    Dim foo As classWaz, bar As classWaz, iter As classWaz
    Set foo = New classWaz
    foo.Name = "foo"
    
    Set bar = New classWaz
    bar.Name = "bar"
    
    d.Add Key:="baz", Item:=foo
    d.Add Key:="BAZ", Item:=bar
    
    Dim i As Long
    For i = LBound(d.Items) To UBound(d.Items)
        Debug.Print d.Items(i).Name
    Next
    
    Set iter = d("baz")
    Debug.Print iter.Name
End Sub

Caveat: the Scripting.Dictionary is not available on macOS, though you might consider this drop-in replacement.