Access VBA: String to Object Question

Question

I have several collections (150-200) and I am trying to get a reference to one of the collections by the name of the collection. Is there a way to convert or reference the string back to an object? I can do a Case Select statement but that seems to be a rather ugly way of doing it.

For example,

Dim strCollectionName As String

Dim clnTmpCollection As Collection

Dim clnCollection_1_Points As Collection

Dim clnCollection_1_Comments As Collection

Dim clnCollection_2_Points As Collection

Dim clnCollection_3_Points As Collection

strCollectionName = "clnCollection_2_Points"

clnTmpCollection = (strCollectionName)

clnTmpCollection needs to reference the collection that is named in the strCollectionName variable. In this example, clnTmpCollection would reference the clnCollection_2_Points collection.

Answer 1

No, VBA doesn't support that. You might use an array of collections:

    Dim clnTmpCollection As Collection

    Dim clnCollections(1 To 5) As Collection

    Set clnCollections(1) = New Collection

    clnCollections(1).Add "Alpha"

    clnCollections(1).Add "Beta"

    clnCollections(1).Add "Gamma"

    Set clnCollections(2) = New Collection

    clnCollections(2).Add "Amsterdam"

    clnCollections(2).Add "Brussels"

    clnCollections(2).Add "Copenhagen"

    ' ...

    ' ...

    Set clnTmpCollection = clnCollections(2)

    MsgBox clnTmpCollection(3)

Note the keyword Set. This is required when assigning a collection to a variable.