Create dictionary of lists in vba

Arrays in VBA are more or less like everywhere else with various peculiarities:

• Redimensioning an array is possible (although not required).
• Most of the array properties (e.g., Sheets array in a Workbook) are 1-based. Although, as rightly pointed out by @TimWilliams, the user-defined arrays are actually 0-based. The array below defines a string array with a length of 11 (10 indicates the upper position).

Other than that and the peculiarities regarding notations, you shouldn't find any problem to deal with VBA arrays.

Dim stringArray(10) As StringstringArray(1) = "first val"stringArray(2) = "second val"'etc.

Regarding what you are requesting, you can create a dictionary in VBA and include a list on it (or the VBA equivalent: Collection), here you have a sample code:

Set dict = CreateObject("Scripting.Dictionary")Set coll = New Collectioncoll.Add ("coll1")coll.Add ("coll2")coll.Add ("coll3")If Not dict.Exists("dict1") Then    dict.Add "dict1", collEnd IfDim curVal As String: curVal = dict("dict1")(3) '-> "coll3"Set dict = Nothing 

You can have dictionaries within dictionaries. No need to use arrays or collections unless you have a specific need to.

Sub FillNestedDictionairies()    Dim dcParent As Scripting.Dictionary    Dim dcChild As Scripting.Dictionary    Dim rCell As Range    Dim vaSplit As Variant    Dim vParentKey As Variant, vChildKey As Variant    Set dcParent = New Scripting.Dictionary    'Don't use currentregion if you have adjacent data    For Each rCell In Sheet2.Range("A1").CurrentRegion.Cells        'assume the text is separated by a space        vaSplit = Split(rCell.Value, Space(1))        'If it's already there, set the child to what's there        If dcParent.Exists(vaSplit(0)) Then            Set dcChild = dcParent.Item(vaSplit(0))        Else 'create a new child            Set dcChild = New Scripting.Dictionary            dcParent.Add vaSplit(0), dcChild        End If        'Assumes unique post-space data - text for Exists if that's not the case        dcChild.Add CStr(vaSplit(1)), vaSplit(1)    Next rCell    'Output to prove it works    For Each vParentKey In dcParent.Keys        For Each vChildKey In dcParent.Item(vParentKey).Keys            Debug.Print vParentKey, vChildKey        Next vChildKey    Next vParentKeyEnd Sub

I am not that familiar with C++ and Python (been a long time) so I can't really speak to the differences with VBA, but I can say that working with Arrays in VBA is not especially complicated.

In my own humble opinion, the best way to work with dynamic arrays in VBA is to Dimension it to a large number, and shrink it when you are done adding elements to it. Indeed, Redim Preserve, where you redimension the array while saving the values, has a HUGE performance cost. You should NEVER use Redim Preserve inside a loop, the execution would be painfully slow

Adapt the following piece of code, given as an example:

Sub CreateArrays()Dim wS As WorksheetSet wS = ActiveSheetDim Flanged_connections()ReDim Flanged_connections(WorksheetFunction.CountIf(wS.Columns(1), _    "Flanged_connections"))For i = 1 To wS.Cells(1, 1).CurrentRegion.Rows.Count Step 1    If UCase(wS.Cells(i, 1).Value) = "FLANGED_CONNECTIONS" Then   ' UCASE = Capitalize everything        Flanged_connections(c1) = wS.Cells(i, 2).Value    End IfNext iEnd Sub