Need help on Array VBA

Question

I don't understand some parts in the following code:

    For i = 2 To UBound(a, 1)
        txt = Join$(Array(a(i, 3), a(i, 4)), Chr(2))
        If Not dic.exists(txt) Then
            Set dic(txt) = CreateObject("Scripting.Dictionary")
        End If
        dic(txt)(a(i, 1)) = VBA.Array(a(i, 5), a(i, 6), a(i, 2))
        
    Next

• Q1: Why set dic(txt) in the loop code
• Q2: dic(txt)(a(i,1)) => Why they use (a(i,1))

Thanks alot

Answer 1

Dictionary of Dictionaries

• Loop through the elements of the 1st dimension (rows) of a multi-dimensional array (a):

  For i = 2 To UBound(a, 1)
  

• Revealing that a is a 2D array, joining the elements of columns 3 and 4 into a string (txt).

  txt = Join$(Array(a(i, 3), a(i, 4)), Chr(2))
  

  or to simplify:

  txt = a(i, 3) & Chr(2) & a(i, 4)
  

• Checking if txt exists as a key in the existing dictionary (dic).

  If Not dic.exists(txt) Then
  End If
  

• If it doesn't, add a new dictionary as an item associated with the current key:

  Set dic(txt) = CreateObject("Scripting.Dictionary") 
  

  1. Key is txt,
  2. Item is a new (inner) dictionary.

• Use the value in the first column of the array (a) as the key in the new dictionary and add a zero-based (VBA. ensures zero-based) array with the values from columns 5, 6, and 2 of array a as the associated item to the new dictionary (of course only if the 1st column value does not already exist in the 'new' (inner) dictionary):

  dic(txt)(a(i, 1)) = VBA.Array(a(i, 5), a(i, 6), a(i, 2))
  

• dic:

  • Key is txt
  • Item is new (inner) dic:
    • Key is a(i, 1)
    • Item is VBA.Array(...)