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

[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