Results 1 to 12 of 12

Dictionaries and arrays explanation

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145

    Dictionaries and arrays explanation

    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
    Last edited by Xrull; 05-29-2019 at 08:06 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dictionaries within dictionaries to describe 3 different tables and theirs atributes
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-03-2018, 07:21 AM
  2. Replies: 5
    Last Post: 11-12-2018, 10:24 PM
  3. [SOLVED] Dictionaries vs Collections: Please help me understand when Dictionaries are better
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-14-2016, 12:36 PM
  4. Arrays and Collections and Dictionaries oh my
    By JYTS in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-24-2015, 12:06 AM
  5. [SOLVED] Find a specific sum for two different arrays..(more explanation in the post)
    By bach1229 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-03-2015, 08:21 AM
  6. [SOLVED] Looking for online learning materials on arrays & scripting dictionaries
    By strud in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-11-2013, 08:16 AM
  7. [SOLVED] Explanation on Arrays
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2013, 06:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1