VBA collection: list of keys

If you intend to use the default VB6 Collection, then the easiest you can do is:

col1.add array("first key", "first string"), "first key"col1.add array("second key", "second string"), "second key"col1.add array("third key", "third string"), "third key"

Then you can list all values:

Dim i As VariantFor Each i In col1  Debug.Print i(1)Next

Or all keys:

Dim i As VariantFor Each i In col1  Debug.Print i(0)Next

I don't thinks that possible with a vanilla collection without storing the key values in an independent array.

The easiest alternative to do this is to add a reference to the Microsoft Scripting Runtime & use a more capable Dictionary instead:

Dim dict As DictionarySet dict = New Dictionarydict.Add "key1", "value1"dict.Add "key2", "value2"Dim key As VariantFor Each key In dict.Keys    Debug.Print "Key: " & key, "Value: " & dict.Item(key)Next

You can create a small class to hold the key and value, and then store objects of that class in the collection.

Class KeyValue:

Public key As StringPublic value As StringPublic Sub Init(k As String, v As String)    key = k    value = vEnd Sub

Then to use it:

Public Sub Test()    Dim col As Collection, kv As KeyValue    Set col = New Collection    Store col, "first key", "first string"    Store col, "second key", "second string"    Store col, "third key", "third string"    For Each kv In col        Debug.Print kv.key, kv.value    Next kvEnd SubPrivate Sub Store(col As Collection, k As String, v As String)    If (Contains(col, k)) Then        Set kv = col(k)        kv.value = v    Else        Set kv = New KeyValue        kv.Init k, v        col.Add kv, k    End IfEnd SubPrivate Function Contains(col As Collection, key As String) As Boolean    On Error GoTo NotFound    Dim itm As Object    Set itm = col(key)    Contains = TrueMyExit:    Exit FunctionNotFound:    Contains = False    Resume MyExitEnd Function

This is of course similar to the Dictionary suggestion, except without any external dependencies. The class can be made more complex as needed if you want to store more information.