CODEWITHSUNDEEP
vbaexcel
BeniaminoBaggins
BeniaminoBaggins

Reputation: 12433

Excel VBA Dictionary of Collections

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:

enter image description here

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?

Upvotes: 0

Views: 1285

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

Two issues here:

  1. 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 ...)
  2. The Watch window on a dictionary shows the keys, not items. To see the Items (ie the Collections) , watch providerNamesDictionary.items

Upvotes: 3

Related Questions