VBA: How to build up nested dictionaries in a loop (ByVal?)

Question

I am exporting several INI-files from an Excel Spreadsheet, where one line means one INI-file, the INI-sections and keys are the columns. This works quite well so far, except it seems VBA is passing dictionaries ByRef (?), but let me explain:

The Excel Spreadsheet looks like this:

       |       section 1       |   section 2   |  section 3  | ...
       | key1  | key2  | key3  | key4  | key3  |  ...
name 1 | value | value | value | value | value |  ...
name 2 | value | value | value | value | value |  ...
...

I get all INI-sections, keys and values dynamically from spreadsheet, that's why I decided not to build a class module but to set up a dictionary structure with 3 nested dictionaries like this:

innerdict: holds key/value pairs of one section
middledict: holds section name and innerdict with key/value pairs
outerdict: holds line name with middledict

The following simplified code should do to explain my situation:

Set outerDict = New Scripting.Dictionary
Set middleDict = New Scripting.Dictionary
Set innerDict = New Scripting.Dictionary

For row = 3 to 10
For col = 1 to 5
  key = Cells(a,b).Value
  value = Cells(x,y).Value
  innerdict.Add key, value

  If nextSectionGroup Then
    middledict.Add sectionName, innerdict
    innerdict.RemoveAll
  End If
Next col

outerdict.Add linename, middledict
middledict.RemoveAll

Next row

In words:
Starting in the first value row, I loop through columns, fill key/value pairs in the innerdict until I hit the end of a section.

Then I (want to) copy the innerdict into the middledict and after that delete the content of innerdict so it can be used for the next section. If I hit the end of the entire row, I copy the middledict into the outerdict and delete the content of middledict so it can be used for next row.

But after running the above code, the middledict and innerdict are not accessible via the outerdict, it seems they are just empty. If I delete those .RemoveAll statements, the code works fine but fills the dicts with wrong content.

It seems to me, that dict.Add does not copy the content to the parent dict, but makes a reference. That would explain why content is empty after the .RemoveAll.

But how to solve it, may someone have a hint please?

Thanks.

Instances of reference types are never copied on assignment or when passed as arguments, regardless of `ByVal` or `ByRef`. A class is a reference type. A dictionary is a class. — GSerg, Jul 31 '19 at 12:39
Possible duplicate of [Assignment of objects in VB6](https://stackoverflow.com/q/4805475/11683) — GSerg, Jul 31 '19 at 12:51
Because you want to clone a dictionary, which is a class that is not under your control, you will need to manually clone it (create a new dictionary and add all keys and values from the original dictionary). Note that this will create a shallow copy, that is, the dictionary will be different, but if the values contained in the dictionary are themselves reference types, the two dictionaries will refer to the same instances of the data, and you would need to clone them too. — GSerg, Jul 31 '19 at 12:55
Replace both `.RemoveAll` by `Set ... = New Scripting.Dictionary` to create new references. Though you should avoid nesting dictionaries. For instance, if your goal is to get a value by name,section,key then store all the values in a single dictionary: `dict.Add name & "|" & section & "|" & key, Value` — Florent B., Jul 31 '19 at 13:59
alright, in between I found that out on my own, replacing ```.RemoveAll``` with ```Set dict = Nothing``` and ```Set dict = New Scripting.Dictionary``` after that worked out. Do you know if it makes a difference for resources used if I destroy the object first and create a new one, or if I directly create a new one like you suggested? Why should I avoid nested dictionaries? I did not want to join a string together, as those values are numerous and can have several special characters so it would be hard to find a delimiter. — mrt, Jul 31 '19 at 14:14
Of course it makes a difference whether or not you are destroying objects. The thing is, you are [not destroying them](https://stackoverflow.com/a/19038890/11683) either way. `middledict` keeps the references to all your `innerdict`s, otherwise how would you be able to retrieve them later? — GSerg, Jul 31 '19 at 14:52

score 0 · Answer 1 · answered Aug 01 '19 at 01:22

Knowing what you want to do with your data would have quite a bearing on the solution. FlorentB's suggestion of a delimited key is appealing if you simply want to retrieve single data items. If you want to iterate data within those lists, then nested dictionaries are probably the way to go.

For nested dictionaries, as you've seen, a reference to the object is stored. So you'd need to create a new instance of a dictionary for each nested dictionary. Some skeleton code, then, would look like this:

Dim v As Variant
Dim r As Long, c As Long
Dim nameDic As Dictionary, secDic As Dictionary, valDic As Dictionary

'Find area of data.
With Sheet1
    r = .Cells(.Rows.Count, 1).End(xlUp).Row
    c = .Cells(2, .Columns.Count).End(xlToLeft).Column
    v = .Range(.Cells(1, 1), .Cells(r, c)).Value2
End With

'Populate dictionaries.
Set nameDic = New Dictionary
For r = 3 To UBound(v, 1)
    Set secDic = New Dictionary
    nameDic.Add v(r, 1), secDic
    For c = 2 To UBound(v, 2)
        If Not IsEmpty(v(1, c)) Then
            'It's a new section.
            Set valDic = New Dictionary
            secDic.Add v(1, c), valDic
        End If
        valDic.Add v(2, c), v(r, c)
    Next
Next

'Single data items can be retrieved like so:
Debug.Print nameDic("name 2")("section 2")("key 4")

VBA: How to build up nested dictionaries in a loop (ByVal?)

1 Answers1