Creating a Dictionary with VBA

Question

I'm trying to create a dictionary of lists. I'm used to this in python but in vba I'm having difficulties. The data is in columns A and B (I'll be using a range with end(xldown) instead of hardcoding A1:A4 this is just illustrative) and they look something like this:

A   Apple  
A   Banana  
B   Carrot   
B   Juice

Essentially I'm wanting to create a dictionary that looks (in python format as: {A:[Apple,Bannana], B:[Carrot,Juice]}

The end result being that I'll want to paste the dictionary values in another column C that will look like this:

A Apple  Apple/Banana   
A Banana Apple/Banana   
B Carrot Carrot/Juice   
B Juice  Carrot/Juice

I've been trying a nested loop through varrays but it hasn't been recognizing the relative positions of Column A and B. I was wondering what the best way to do this in VBA is?
Thanks.

PatricK · Accepted Answer · 2015-12-08 01:23:52Z

2

You can do with VBA only or mix with a UDF.

Consider originally you have (to demonstrate one of the options):

Create a module and put this in:

Option Explicit

Private lRow As Long
Private oDict As Object

Private Sub CreateDict()
    Dim arrValues As Variant, oKey As Variant, oValue As Variant, i As Long

    If oDict Is Nothing Then
        lRow = Worksheets("Sheet2").UsedRange.Rows.Count
        Set oDict = CreateObject("Scripting.Dictionary")
    Else
        If lRow <> Worksheets("Sheet2").UsedRange.Rows.Count Then oDict.RemoveAll ' Remove old items
    End If
    ' Add items to the dictionary
    ' Load values of used range to memory
    arrValues = Worksheets("Sheet2").UsedRange.Value
    ' Assuming the Key is on first column and Value is on next
    For i = 1 To UBound(arrValues)
        oKey = arrValues(i, 1)
        oValue = arrValues(i, 2)
        If Len(oKey) > 0 Then
            If oDict.Exists(oKey) Then
                ' Append Value to existing key
                oDict.Item(oKey) = oDict.Item(oKey) & "/" & oValue
            Else
                ' Add Key and value
                oDict.Add oKey, oValue
            End If
        End If
    Next
End Sub

Function GetList(ByVal oRange As Range) As String
    If lRow <> Worksheets("Sheet2").UsedRange.Rows.Count Then Set oDict = Nothing
    If oDict Is Nothing Then CreateDict
    GetList = oDict.Item(oRange.Value)
End Function

The macro above is to create the dictionary list. Then on next column, use formula to call the UDF GetList, e.g.

Auto Fill the UDF to get the result you want:

There are pros and cons with using UDF, but I believe you can modify it to VBA only (new Sub and loop throught the first column).

answered Dec 8, 2015 at 1:23

PatricK

6,4231 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FancyDolphin Over a year ago

That's pretty much spot on. So then my issue was that you have to explicitly ensure that the references are in the array by using the arrValues. Thank you very much for that learning point. And yes it was easy to modify it to VBA only, but nice to see the option.

PatricK Over a year ago

You can load any Range values into memory and process them, the hard part is determining number of columns other then rows.

Community · Accepted Answer · 2018-05-08 21:49:49Z

1

As PatricK already showed, there are Dictionaries in VBA, but only through Scripting, and not as nicely intergrated into the language as in Python (God, I miss those lists and dictionaries). That method usually works, but does require the referencing of Scripting or the use of late binding, as PatricK shows.

As an alternative, you could use a Collection of classes. Define a class module and give it some variables, one of which a Collection. That way, you could loop through your collection of classes to find the index letter (A and B in this example) and extract your list of fruits from the stored class.

I find this method more flexible and scalable (you have complete control over the class module). And it is a 'native' solution, which will work on any machine, also on Mac (where referencing can be a pain sometimes).

If you want, I can elaborate with an example, but I can never be as complete as Chip Pearson on his site.

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Dec 8, 2015 at 1:42

Jzz

7394 silver badges8 bronze badges

2 Comments

PatricK Over a year ago

Very good pointer for advanced use of VBA and about Mac-unfriendly code... Did you find existing codes using Collection to implement Scripting.Dictionary? There are many methods to implement which may be too much hassle to make it Mac-friendly.

Jzz Over a year ago

True, sometimes it is not worth the hassle. But 9 out of 10 times I do use a Dictionary I end up rewriting it afterwards because I want to do something a Dictionary can't do. And a collection of classes is not that much of a hassle. Did you know you can assign a key to a collection variable in vba? Not very usefull most of the time, but still, for some cases, it has its uses.

Community · Accepted Answer · 2018-05-08 21:49:10Z

-1

Using a combination of formulas and VBA you can achieve your expected results. Starting in cell C1 and then dragging down to the end of your table place the following formula:

=stringconcat("/",IF($A:$A=A1,$B:$B,""))

This is inserted as an array formula using CTRL + SHIFT + ENTER. Explaining the logic from the IF, for each row in column A:A that is equal to the value of that row ("A" in the example provided) it will extract it into an array (return if true option in the IF). The best part of this is that you don't have to hardcode the range as we consider the whole column.

This array is then concatenated using Chip Pearsons handy function that you can find here:

http://www.cpearson.com/excel/stringconcatenation.aspx

Include the code he kindly offers into a module and you're off to the races.

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Dec 7, 2015 at 23:05

nbayly

2,1672 gold badges16 silver badges24 bronze badges

1 Comment

FancyDolphin Over a year ago

While the awnser is much appreciated, if I was to go the spreadsheet route It could be done more simply. I could've created a unique index, used some length counter and concatenated with an if command. However, that is messy and I'd rather it be solely done by VBA on an effective data range not an entire column that'll take up a fair bit of resources.

Collectives™ on Stack Overflow

Creating a Dictionary with VBA

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related