VBA: How to return the key of a collection object?

Question

Is there a smart way to return the key of a collection-object? I am interested in way like

Dim c as Collection
Set c = New Collection
    c.Add "value1", "key1"
    c.Add "value2", "key2"

Debug.Print c.Items(1)       'returns "value1"
Debug.Print c.Items("key1")  'returns "value1"
Debug.Print c.Keys(1)        'this does not work, but "key1" is wanted

The only way i know so far is

Dim keys as Collection
Set keys = New Collection
    keys.Add "key1"
    keys.Add "key2"

Dim c as Collection
Set c = New Collection
For i=1 to keys.Count
    c.Add "value" & i,keys(i)
Next i

Debug.Print c(1)    'returns "value1"
Debug.Print keys(1) 'returns "key1"

But this way isn't very smart.

Do it either your way or with a dictionary. See [this question](https://stackoverflow.com/q/5702362/5162073). — Brian M Stafford, May 17 '20 at 16:06
@BrianMStafford and freeflow: Thanks for the hint to dictionary....much better than the collection! And the link provides all needed information. — Aroc, May 18 '20 at 06:50

score 0 · Answer 1 · answered May 17 '20 at 16:30

A more sensible way of getting a key back from a collection or dictionary is to build your own VBA Library that wraps a dictionary object using C#.

I did this for dictionary object I call Kvp because I was frustrated by the limitations of VBA for getting information into a scripting.dictionary. You can download the library and source code from here Kvp

This would then allow the following

Dim myKyp as Kvp
Set myKvp = new kvp 
' Assuming regular keys e.g. Key1,key2,key3
mykvp.SetFirstIndexAsString "Key1"
mykvp.AddByIndexFromArray Split("Value1,Value2,Value3,Value4,Value5,Value6")
Debug.print myKvp.GetKey("Value3")
Key3
Debug.Print myKvp.GetKeysAsString
Key1,Key2,Key3,Key4,Key5,Key6

Amongst many other useful transformations of dictionary information.

VBA: How to return the key of a collection object?

1 Answers1