VBA Collection or Dictionary with an array and key-CodePudding

I have the following data: ID | Company | RDate

I want to create a dictionary or collection with the ID as key and (Company, RDate) as the value.

The error I am receiving is that I have a type-mismatch on the line where I try to add the key and tuple to the collection.

Ideal structure: ID, (Company, RDate)

Key, (Value A, Value B)

Below is my attempt:

Demo code

Dim ID              As String
Dim Company         As String
Dim RDate           As Date

Dim myCol           As Collection

Set myCol = New Collection
myCol.Add Array(Company, RDate), ID

Dim myVar           As Variant

For Each myVar In myCol.Keys        'this is just code to test that I successfully added the data to the collection
    
    Debug.Print myCol(myVar)(LBound(myCol(myVar)))
    
Next myVar

CodePudding user response：

You tried mixing a collection with a dictionary... Please, test the next adapted code which uses a dictionary and test it as in your code:

Sub testCollDictionary()
 Dim ID As String, Company As String, RDate As Date, myCol As Object

 RDate = Date: ID = "1234": Company = "MyConpany"

 Set myCol = CreateObject("Scripting.dictionary")
 myCol.Add ID, Array(Company, RDate)

 Dim myVar  As Variant

 For Each myVar In myCol.Keys 'this is just code to test that I successfully added the data to the DICTIONARY..
    
    Debug.Print myCol(myVar)(LBound(myCol(myVar)))
    
 Next myVar
End Sub

CodePudding user response：

Store an array as the value:

Dim Id              As String
Dim Company         As String
Dim RDate           As Date
Dim Index           As Integer

Dim myCol           As Collection

Set myCol = New Collection

Id = CStr(1)
Company = "Corp. A"
RDate = Date
myCol.Add Array(Company, RDate), Id

Id = CStr(2)
Company = "Corp. B"
RDate = Date
myCol.Add Array(Company, RDate), Id

For Index = 1 To myCol.Count
    Debug.Print Index, myCol(Index)(0), myCol(Index)(1)
Next

Set myCol = Nothing

Output:

 1            Corp. A       20-09-2022 
 2            Corp. B       20-09-2022

CodePudding user response：

Dictionary vs Collection

The Question

You could have used the following in your question to make things clearer.

Sub Question()
    
    Dim ID As Long: ID = 1
    Dim Company As String: Company = "Querty"
    Dim RDate As Date: RDate = Date
    
    Dim myCol As Collection: Set myCol = New Collection
    myCol.Add Array(Company, RDate), ID ' Run-time error '13': Type mismatch
    
    Dim myVar  As Variant
    For Each myVar In myCol.Keys
        Debug.Print myCol(myVar)(LBound(myCol(myVar)))
    Next myVar

End Sub

Collection

The following illustrates the key limitation of the collection (for this case).

Sub Answer()
    
    Dim ID As String: ID = "1"
    Dim Company As String: Company = "Querty"
    Dim RDate As Date: RDate = Date
    
    Dim myCol As Collection: Set myCol = New Collection
    myCol.Add VBA.Array(Company, RDate), ID ' ensure zero-based with 'VBA.'
    
    Dim myVar As Variant
    Dim i As Long
    For Each myVar In myCol
        i = i   1
        Debug.Print "In the Loop", myVar(0), myVar(1), i
    Next myVar

    ' You cannot get the key,
    
    ' ... but you can get the data using the key...
    Dim myKey As String: myKey = ID
    On Error Resume Next
        Debug.Print "Use the Key", myCol(myKey)(0), myCol(myKey)(1)
    On Error GoTo 0
    
    ' ... or you can use the index.
    Debug.Print "Use the Index", myCol(1)(0), myCol(1)(1), 1

    ' Conclusion
    ' Use the dictionary instead.

End Sub

Dictionary

The following illustrates how easy it is by using the dictionary.

Sub Dictionary()
    
    Dim ID As Long: ID = 1
    Dim Company As String: Company = "Querty"
    Dim RDate As Date: RDate = Date
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    dict(ID) = VBA.Array(Company, RDate) ' ensure zero-based with 'VBA.'
    
    Dim myKey As Variant
    For Each myKey In dict.Keys
        Debug.Print myKey, dict(myKey)(0), dict(myKey)(1)
    Next myKey

End Sub