CODEWITHSUNDEEP
arraysexceldictionary
user2680039
user2680039

Reputation: 99

Excel dictionary with multiple values?

In Python I am used to having something like a defaultdict where I can just append many values to a given key. In Excel it seems like if I use the Dictionary object from the Scripting Runtime, I can only add one item per key.

Is there a way I can do something like this:

  1. If the dictionary lacks a key, add List(first item) under dict[key]
  2. If the dictionary has the key, append item to pre-existing list under dict[key]

I hope I am being clear enough. Maybe instead of a list I mean an array, not sure.

Attempt:

        Dim collect As New collection

        If Not dict.Exists(key) Then
            dict.Add key, collect
        End If

        collect = dict.Item(key)
        collect.Add (val)

        dict.Item(key) = collect

Upvotes: 1

Views: 2293

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56745

You are missing the Set keyword required in VBA to assign to objects. However you can get around them with a much more compact format anyway:

If Not dict.Exists(Key) Then dict.Add Key, New Collection

dict.Item(Key).Add Val

If you want to keep your old format, then this is how:

    Dim collect As New collection

    If Not dict.Exists(key) Then
        dict.Add key, collect
    End If

    Set collect = dict.Item(key)
    collect.Add (val)

    Set dict.Item(key) = collect

But the more compact format above works fine as well.

Upvotes: 1

Related Questions