Understanding how to use dictionaries

Question

I have a worksheet that has a sheet of customer invoice data:

Name | Date       | OrderID | Item  | Price | Email
Anna | 2015-03-10 | ABC123  | Shirt | 5.00  | anna@gmail.com
Bob  | 2015-03-11 | ABC124  | Pants | 10.00 | bob@gmail.com
Anna | 2015-03-11 | ABC125  | Pants | 10.00 | anna@gmail.com

Prior, I used a collection by utilizing a for-loop from row 2 to end of the data region, to add to a collection. I created two class modules. One is 'Transaction' and the other 'Invoice'. Within 'Transaction' class module I declared public variables of the components for each transaction, which is Name, Date, OrderID, Item, Price, and Email. The 'Invoice' class module contained procedures which would add each transaction into a collection.

I am starting to look into dictionaries as a way to handle data due to its ability to work with keys. I want the keys to be the Name of the person on the transaction. Because Anna has made two transactions, if I look up the 'Anna' key in the dictionary, I would be able to retrieve the transactions for Anna.

How would I go about doing this? I have been running into errors because the code examples I have been looking at deals with one dimensional data sets.

Sub ProcessData()

Dim dict As Dictionary
Dim i As Integer

Dim TargetRow As Integer

Dim Name As String
Dim Date As Date
Dim OrderID As Double
Dim Item As String
Dim Price As Double
Dim Email As string
Dim More As Boolean

Set dict = New Dictionary

More = True
i = 2

While more

    Name = Worksheets("Data").Cells(i, 1).Value
    Date = Worksheets("Data").Cells(i, 2).Value
    OrderID = Worksheets("Data").Cells(i, 3).Value
    Item = Worksheets("Data").Cells(i, 4).Value
    Price = Worksheets("Data").Cells(i, 5).Value
    Email = Worksheets("Data").Cells(i, 6).Value

    dict.Item(Name) = ...

Wend

End Sub

You may want to start [here](http://www.snb-vba.eu/VBA_Dictionary_en.html). Try something out and if you got stuck post your code in your question and I or someone else will assist you. — L42, Apr 06 '15 at 06:47
@L42 I added what I started. I do not know how to add the Date, OrderID, Item, Price, and Email to the Name. — AG10, Apr 06 '15 at 09:08

L42 · Answer 1 · 2015-04-06T10:00:07.733

To do that you need to pass an array as your Dictionary item.
Something like this:

Dim c As Range, sh As Worksheet, lr As Long, key
Set sh = Worksheets("Data")
With sh
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
End With

With CreateObject("Scripting.Dictionary")
    ' Pass data from worksheet to Dictionary
    For Each c In sh.Range("A2:A" & lr)
        .Item(c.Offset(0, 2).Value2) = c.Resize(, 6) ' Passed as array
    Next

    ' Retrieve data from Dictionary to where
    For Each key In .Keys
        ' Access Items as array
        Debug.Print .Item(key)(1, 1) 'Name
        Debug.Print .Item(key)(1, 2) 'Date
        Debug.Print .Item(key)(1, 3) 'OrderID
        Debug.Print .Item(key)(1, 4) 'Item
        Debug.Print .Item(key)(1, 5) 'Price
        Debug.Print .Item(key)(1, 6) 'Email
    Next
End With

Take note that you need to use a Key that is always unique.
In you example data, you cannot use Name as Key since Ana has duplicate.
The first data will be overwritten once you pass it to dictionary.
In my example above, I used OrderID as Key since it is unique.
Also, I pass all the info as a 2D array Item and can be retrieved as shown above.
This is ok, but I think using a Class Module is much better in your case.

Can you explain further when you think using a `Class Module` will be better? Do you mean using custom class collections housed in Class Modules rather than dictionaries? When I have a data such as the one shown above, is it more efficient to use one or the other? What about the case when I want to only look at the transactions for Anna, where there are multiple entries for a specific person? Thank you - Val — AG10, Apr 06 '15 at 11:03

score 0 · Answer 2 · answered Apr 06 '15 at 07:42

You need disconnected ADODB.Recordsets. They are a grid. Dictionaries are needed only to test for unique keys.

This is a two field recordset.

Sub Randomise
    Randomize 
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        .Fields.Append "RandomNumber", 4 
        .Fields.Append "Txt", 201, 5000 
        .Open
        Do Until Inp.AtEndOfStream
            .AddNew
            .Fields("RandomNumber").value = Rnd() * 10000
            .Fields("Txt").value = Inp.readline
            .UpDate
        Loop
        .Sort = "RandomNumber"
        Do While not .EOF
            Outp.writeline .Fields("Txt").Value
            .MoveNext
        Loop
    End With
End Sub

'=============================================
Sub Swap
    Dim LineCount
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        .Fields.Append "LineNumber", 4 
        .Fields.Append "Txt", 201, 5000 
        .Open
        LineCount = 0
        Do Until Inp.AtEndOfStream
            LineCount = LineCount + 1
            .AddNew
            .Fields("LineNumber").value = LineCount
            .Fields("Txt").value = Inp.readline
            .UpDate
        Loop
        .Sort = "LineNumber DESC"
        Do While not .EOF
            Outp.writeline .Fields("Txt").Value
            .MoveNext
        Loop
    End With
End Sub

Understanding how to use dictionaries

2 Answers2