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

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 ...

Would a collection class not do what you want?

Quote:

---

Originally Posted by Bob Phillips

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 ?

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.

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 -------------------

Code:

---

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 ------------------------------------------

Code:

---

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

---

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 …

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

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 :-)

Quote:

---

Originally Posted by Paul_Hossler

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

---

And then you used a collection! :dunno

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

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

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.

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