CODEWITHSUNDEEP
excelvba
FancyDolphin
FancyDolphin

Reputation: 457

Creating a Dictionary with VBA

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.

Upvotes: 1

Views: 2803

Answers (3)

Jzz
Jzz

Reputation: 739

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.

Upvotes: 1

PatricK
PatricK

Reputation: 6433

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

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

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.
Stage1

Auto Fill the UDF to get the result you want:
Stage2

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

Upvotes: 2

nbayly
nbayly

Reputation: 2167

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.

Upvotes: -1

Related Questions