Refer to Excel collections in VBA with a "variable" name?

[pplanch]

Hi there,

In the code I'm working on, I would like to (if possible) set up collections and refer to them with names that are not "hard-coded" but are generated in the code. Is this possible?

e.g. I have a collection with bank account data in objects. I have 3 bank accounts currently, so have created 3 objects (colKeyWordsBank1List, colKeyWordsBank2List, colKeyWordsBank3List). What I would like to do is have the objects created and referred to with something like "colKeyWordsBank" & i & "List" where i is variable.

I've tried to google a solution with no luck. Maybe someone in this forum can point me to a solution? (or confirm if it is at all possible).

I'm running Excel 365 on Win11 and Mac.

Thanks in advance for any feedback ...

[Bob Phillips]

Would a collection class not do what you want?

[pplanch]

Would a collection class not do what you want?

Quite possibly … I’m relatively new to classes and collections, haven’t figured out to combine then yet. I’ll ponder this, thanks for the tip …

If I understand your suggestion correct, what I need to do, is first have the classes for the individual objects, then set up a separate class for the collection of the objects, and then create items in the latter one ?

[Bob Phillips]

You basically understand the concept, but you have two class types, the object and the collection. You populate the object class, then add it to the collection class, which will have a property of the object class. You can then iterate through the collection, reference by index, or add a name property to reference by.

[Paul_Hossler]

I'd use a Dictionary as the main 'container' with a class containing a collection as it's elements

I just think it's a little more flebible than a Collection

This needs error checking and the class could probably have more functionality included, but might give you some ideas


Standard module -------------------

Option Explicit


Sub Example()
    Dim KeyWords As Object
    Dim KeyWordsList As clsKeyWords
    Dim i As Long
    
    Set KeyWords = CreateObject("Scripting.Dictionary")
    Set KeyWordsList = New clsKeyWords
    
    With KeyWords
        Set KeyWordsList = New clsKeyWords
        .Add "KeyWordsBank1List", KeyWordsList
        Set KeyWordsList = New clsKeyWords
        .Add "KeyWordsBank2List", KeyWordsList
        Set KeyWordsList = New clsKeyWords
        .Add "KeyWordsBank3List", KeyWordsList
    End With
    
    i = 1
    With KeyWords("KeyWordsBank" & i & "List")
        .Entry("Key001") = "Data001"
        .Entry("Key002") = "Data002"
        .Entry("Key003") = "Data003"
    End With
            
    i = 3
    With KeyWords("KeyWordsBank" & i & "List")
        .Entry("Key001") = "Data004"
        .Entry("Key002") = "Data005"
        .Entry("Key003") = "Data006"
    End With


    
    MsgBox KeyWords("KeyWordsBank1List").KeyWords("Key001")
    MsgBox KeyWords("KeyWordsBank3List").Entry("Key001")


End Sub

clsKeyWords module ------------------------------------------

Option Explicit

Private m_KeyWords As Collection


Private Sub Class_Initialize()
    Set m_KeyWords = New Collection
End Sub


Private Sub Class_Terminate()
    Set m_KeyWords = Nothing
End Sub


Property Get KeyWords() As Collection
    Set KeyWords = m_KeyWords
End Property


Property Let Entry(K As String, V As Variant)
    m_KeyWords.Add V, K
End Property


Property Get Entry(K As String) As Variant
    Entry = m_KeyWords(K)
End Property

[pplanch]

Sounds interesting, Paul. From what I found after reading your suggestion it would appear however (haven’t tested yet) that this would not work on Mac, so I’d have to use a replacement for dictionaery …

[Paul_Hossler]

This reports to be a 'drop in' replacement for the scripting dictionary that will work on a Mac also


https://github.com/VBA-tools/VBA-Dictionary

[pplanch]

Thanks, Paul, I meanwhile got out of bed and found the link you posted, as well as some similar links for the drop-in replacement ... I do appreciate your following through :-)

[Bob Phillips]

I just think it's a little more flebible than a Collection

And then you used a collection!

[Paul_Hossler]

well .... I did use a Dictionary as the outer container

I suppose it might have been more consistent to also use a Dictionary as the inner containers

Using a dictionary gave me the ability to index the outer container by using a constructed string

[Bob Phillips]

Paul, one of my reasons for using a collection class was because I did some work many years ago, prompted by an idea from Rob Van Gelder, to extend the class to provide subsets of the collection.

Thinking of this led me to recall building an example with a disconnected recordset some time ago, which provides the same functionality as the collection class, builtin, and more, such as sorting. It certainly provides that ability to recall an item by using a constructed string.

[snb]

See: https://www.snb-vba.eu/inhoud.html