CODEWITHSUNDEEP
arraysexcelvbadictionary
K jack
K jack

Reputation: 9

Need help on Array VBA

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

Thanks alot

Upvotes: 0

Views: 66

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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(...)

Upvotes: 1

Related Questions