VBA collection: list of keys

Asked 2011-04-18T11:48:25.537

After I add some values to the VBA collection, is there any way to retrieve the list of all keys?

For example

Dim coll as new  Collection
Dim str1, str2, str3

str1="first string"
str2="second string"
str3="third string"

coll.add str1, "first key"
coll.add str2, "second key"
coll.add str3, "third key"

I know how to retrieve the list of strings:

Dim x As Variant
For Each x In coll
    Debug.Print x
Next x

will produce the following...

first string
second string
third string

Is there a similarly concise way to retrieve just the keys?

first key
second key
third key

Note: I'm using VBA through AutoCAD 2007

vba collections

Edit Report

2 Answers

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 String
Public value As String
Public Sub Init(k As String, v As String)
    key = k
    value = v
End 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 kv
End Sub

Private 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 If
End Sub

Private Function Contains(col As Collection, key As String) As Boolean
    On Error GoTo NotFound
    Dim itm As Object
    Set itm = col(key)
    Contains = True
MyExit:
    Exit Function
NotFound:
    Contains = False
    Resume MyExit
End 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.

answered 2012-03-29T23:33:46.390

An alternative solution is to store the keys in a separate Collection:

'Initialise these somewhere.
Dim Keys As Collection, Values As Collection

'Add types for K and V as necessary.
Sub Add(K, V) 
Keys.Add K
Values.Add V, K
End Sub

You can maintain a separate sort order for the keys and the values, which can be useful sometimes.

answered 2011-09-23T07:56:14.850

VBA collection: list of keys

2 Answers

Your Answer