Excel VBA Dictionary of Collections

Question

I want to have a dictionary where the key will be a string providerName and the value will be a collection of categoryId's. However, this is the result I am getting:

It looks like the key is just item1, item2, item3 etc and the value is just the providerName, and the collection of categoryIds is not there at all.

My code:

For i = 2 To Selection.Rows.Count
providerName = SingleLine(i, 1)
categoryId = SingleLine(i, 3)
Dim categoryIdCollection As New Collection
If Not providerNamesDictionary.exists(providerName) Then
    categoryIdCollection.add (categoryId)
    providerNamesDictionary.add key:=providerName, Item:=categoryIdCollection
Else
    Dim tempCategoryIdCollection As Collection
    Set tempCategoryIdCollection = providerNamesDictionary(providerName)
    tempCategoryIdCollection.add (categoryId)
    Set providerNamesDictionary(providerName) = tempCategoryIdCollection
End If

How do I get the key to be the providerName and the value to be a collection of categoryIds?

chris neilsen · Accepted Answer · 2016-10-20 01:47:21Z

3

Two issues here:

Dim inside a loop does not work as you expect. It creates a variable the first time it's encountered, thereafter it's ignored. Net affect, you only ever create one Collection and add all catagoryID's to it
To fix item 1, put Dim categoryIdCollection As Collection outside the loop and Set categoryIdCollection = New Collection inside the loop (instead of Dim ...)
The Watch window on a dictionary shows the keys, not items. To see the Items (ie the Collections) , watch providerNamesDictionary.items

edited Oct 20, 2016 at 1:47

answered Oct 20, 2016 at 1:05

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Dictionary of Collections

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related