VBA storing an ArrayList in Dictionary

Question

I need a dictionary that contain a key with a corresponding pair of dates. I created below code to illustrate what I'm doing now. So basically, I store the pair of dates in an ArrayList which I then add to my dictionary. Then I remove the items from the ArrayList so it can be used again to store the next pair of dates. When I want to use myDict on other parts of the code, the dictionary contains the key but it doesn't contain the corresponding pair with dates. I assume that has to do with passing by reference of ArrayList (?) and the Clear function that I apply.

How can I resolve this? I don't specifically need to use an ArrayList to store the pair of dates.

Function myDict() As Object

    Dim dict As Object, keyList As Object, arrListToDict As Object
    Dim keyListCount As Long, iLoop As Long
    Dim dateToday As Date, date1 As Date, date2 As Date
    Dim x As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set keyList = CreateObject("System.Collections.ArrayList")
    Set arrListToDict = CreateObject("System.Collections.ArrayList")
    
    keyList.Add ("Hey")
    keyList.Add ("Hello")
    keyListCount = keyList.Count
    dateToday = Date

    
    For iLoop = 0 To keyListCount - 1
        date1 = DateAdd("d", Int(20000 * Rnd), dateToday)
        date2 = DateAdd("d", Int(20000 * Rnd), dateToday)
        arrListToDict.Add (date1)
        arrListToDict.Add (date2)
        dict.Add key:=keyList(iLoop), Item:=arrListToDict
        
        arrListToDict.Clear
    Next iLoop
    
    Set myDict = dict

End Function

I googled but couldn't find a solution.

Answer 1

Nest Arraylists in Dictionary

• For each dictionary key, create a new arraylist.

Function myDict() As Object
    
    Dim keyList As Object:
    Set keyList = CreateObject("System.Collections.ArrayList")
    keyList.Add ("Hey")
    keyList.Add ("Hello")
    
    Dim dateToday As Date: dateToday = Date
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    Dim arrList As Object, i As Long, date1 As Date, date2 As Date
    
    For i = 0 To keyList.Count - 1
        date1 = DateAdd("d", Int(20000 * Rnd), dateToday)
        date2 = DateAdd("d", Int(20000 * Rnd), dateToday)
        Set arrList = CreateObject("System.Collections.ArrayList")
        arrList.Add (date1)
        arrList.Add (date2)
        dict.Add Key:=keyList(i), Item:=arrList
        ' or:
        'Set dict(keyList(i)) = arrList
    Next i
    
    Set myDict = dict

End Function

Sub Test()
    Dim dict As Object: Set dict = myDict
    Dim Key As Variant, Item As Variant
    For Each Key In dict.Keys
        Debug.Print Key
        For Each Item In dict(Key)
            Debug.Print vbTab & Item
        Next Item
    Next Key
End Sub