I'm trying to improve my understanding of Dictionaries and Arrays (I'm between jobs so I have the time) and I was looking at a piece of code in a response to a thread "generate array from multiple rows" sometime in 2017.
The answer was provided by Jindon.
https://www.excelforum.com/excel-pro...le-rows-2.html
Can anyone explain how the code below works?
Is there a good tutorial on how to understand how to use Dictionaries and Arrays?
Sub test()
Dim a, i As Long, ii As Long, maxComma As Long, w, e, n As Long
a = Sheets("data").Cells(1).CurrentRegion.Value: n = 1
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
ReDim w(1 To 2): w(1) = a(i, 2)
Set w(2) = CreateObject("Scripting.Dictionary")
w(2).CompareMode = 1
.Item(a(i, 1)) = w
End If
'This part is particularly interesting:
.Item(a(i, 1))(2)(CStr(a(i, 3))) = Empty
maxComma = WorksheetFunction.Max(maxComma, .Item(a(i, 1))(2).Count)
Next
For Each e In .keys
n = n + 1
a(n, 1) = e: a(n, 2) = .Item(e)(1)
a(n, 3) = Chr(34) & Join(.Item(e)(2).keys, ",") & _
String(maxComma - .Item(e)(2).Count, ",") & Chr(34)
Next
Sheets("output").Cells(1).Resize(.Count + 1, 3).Value = a
End With
End Sub
Regards,
Xrull
Bookmarks