Create dictionary of lists in vba
dictionary, vba
Solution
Arrays in VBA are more or less like everywhere else with various peculiarities:
- Redimensioning an array is possible (although not required).
- Most of the array properties (e.g., `Sheets` array in a Workbook) are 1-based. Although, as rightly pointed out by @TimWilliams, the user-defined arrays are actually 0-based. The array below defines a string array with a length of 11 (10 indicates the upper position).
Other than that and the peculiarities regarding notations, you shouldn't find any problem to deal with VBA arrays.
Dim stringArray(10) As String
stringArray(1) = "first val"
stringArray(2) = "second val"
'etc.
Regarding what you are requesting, you can create a dictionary in VBA and include a list on it (or the VBA equivalent: `Collection`), here you have a sample code:
Set dict = CreateObject("Scripting.Dictionary")
Set coll = New Collection
coll.Add ("coll1")
coll.Add ("coll2")
coll.Add ("coll3")
If Not dict.Exists("dict1") Then
dict.Add "dict1", coll
End If
Dim curVal As String: curVal = dict("dict1")(3) '-> "coll3"
Set dict = Nothing
Problem
I have worked in Python earlier where it is really smooth to have a dictionary of lists (i.e. one key corresponds to a list of stuff). I am struggling to achieve the same in vba. Say I have the following data in an excel sheet: ``` Flanged_connections 6 Flanged_connections 8 Flanged_connections 10 Instrument Pressure Instrument Temperature Instrument Bridle Instrument Others Piping 1 Piping 2 Piping 3 ``` Now I want to read the data and store it in a dictionary where the keys are `Flanged_connections`, `Instrument` and `Piping` and the values are the corresponding ones in the second column. I want the data to look like this: ``` 'key' 'values': 'Flanged_connections' '[6 8 10]' 'Instrument' '["Pressure" "Temperature" "Bridle" "Others"]' 'Piping' '[1 2 3]' ``` and then being able to get the list by doing `dict.Item("Piping")` with the list `[1 2 3]` as the result. So I started thinking doing something like: ``` For Each row In inputRange.Rows If Not equipmentDictionary.Exists(row.Cells(equipmentCol).Text) Then equipmentDictionary.Add row.Cells(equipmentCol).Text, <INSERT NEW LIST> Else equipmentDictionary.Add row.Cells(equipmentCol).Text, <ADD TO EXISTING LIST> End If Next ``` This seems a bit tedious to do. Is there a better approach to this? I tried searching for using arrays in vba and it seems a bit different than java, c++ and python, with stuft like `redim preserve` and the likes. Is this the only way to work with arrays in vba? My solution: Based on @varocarbas' comment I have created a dictionary of collections. This is the easiest way for my mind to comprehend what's going on, though it might not be the most efficient. The other solutions would probably work as well (not tested by me). This is my suggested solution and it provides the correct output: ``` '/--------------------------------------\' '| Sets up the dictionary for equipment |' '\--------------------------------------/' inputRowMin = 1 inputRowMax = 173 inputColMin = 1 inputColMax = 2 equipmentCol = 1 dimensionCol = 2 Set equipmentDictionary = CreateObject("Scripting.Dictionary") Set inputSheet = Application.Sheets(inputSheetName) Set inputRange = Range(Cells(inputRowMin, inputColMin), Cells(inputRowMax, inputColMax)) Set equipmentCollection = New Collection For i = 1 To inputRange.Height thisEquipment = inputRange(i, equipmentCol).Text nextEquipment = inputRange(i + 1, equipmentCol).Text thisDimension = inputRange(i, dimensionCol).Text 'The Strings are equal - add thisEquipment to collection and continue If (StrComp(thisEquipment, nextEquipment, vbTextCompare) = 0) Then equipmentCollection.Add thisDimension 'The Strings are not equal - add thisEquipment to collection and the collection to the dictionary Else equipmentCollection.Add thisDimension equipmentDictionary.Add thisEquipment, equipmentCollection Set equipmentCollection = New Collection End If Next 'Check input Dim tmpCollection As Collection For Each key In equipmentDictionary.Keys Debug.Print "--------------" & key & "---------------" Set tmpCollection = equipmentDictionary.Item(key) For i = 1 To tmpCollection.Count Debug.Print tmpCollection.Item(i) Next Next ``` Note that this solution assumes that all the equipment are sorted!