VBA Collection or Dictionary with an array and key

qwertyman9938 0
Viewed 50

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
vba excel
3 Answers

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
FaneDuru0

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
VBasic20080

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