VBA Collection with case specific keys

Question

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

A lot of reworking existing code. But if that's the only option, I can do it. I'm really just looking for options. — h0x0, Jan 11 '23 at 16:59
If you hash the names before using them as keys they would then be "case sensitive" (ie. hashes for "AAA" and "aaa" are not the same. Eg: https://stackoverflow.com/a/28362053/478884 — Tim Williams, Jan 11 '23 at 18:25
@TimWilliams - interesting idea, but not really on option (I think) because I need to be able to recover objects using something like `objA.Thing = objB.Things(objA.ThingName)`. I guess `objA.Thing = objB.Things(hash(objA.ThingName))` is possible but it would be at least as clumsy as redoing the iteration - and there's an edge case of unlike keys having the same hash. Thanks for the idea. — h0x0, Jan 11 '23 at 18:44

BigBen · Accepted Answer · 2023-01-11T20:17:30.530

2

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.

edited Jan 11 '23 at 20:17

answered Jan 11 '23 at 16:58

BigBen

46,229
7
24
40

One potential issue could be that the `Scripting` library isn't available on macOS, so if the code is intended to be cross-platform, then this doesn't work. – GWD Jan 11 '23 at 17:05
Yes that is a caveat for sure. – BigBen Jan 11 '23 at 17:06
The better practice would have been for me to use `Dictionary` from the start, rather than trying to coble dictionary behavior out of `Collection`. Just to possibly save someone a search, the reference `Microsoft Scripting Runtime` is required to use `Scripting.Dictionary` rather than `CreateObject("Scripting.Dictionay")` as on learn.microsoft.com. This was not a default reference on my Office install. – h0x0 Jan 11 '23 at 18:56

VBA Collection with case specific keys

1 Answers1