Setting the Item property of a Collection in VBA

I'm surprised at how hard this has been to do but I imagine it's a quick fix so I will ask here (searched google and documentation but neither helped). I have some code that adds items to a collection using keys. When I come across a key that already exists in the collection, I simply want to set it by adding a number to the current value.

Here is the code:

If CollectionItemExists(aKey, aColl) Then 'If key already has a value
    'add value to existing item
  开发者_如何学Go  aColl(aKey).Item = aColl(aKey) + someValue
Else
    'add a new item to the collection (aka a new key/value pair)
    mwTable_ISO_DA.Add someValue, aKey
End If

The first time I add the key/value pair into the collection, I am adding an integer as the value. When I come across the key again, I try to add another integer to the value, but this doesn't work. I don't think the problem lies in any kind of object mis-match or something similar. The error message I currently get is

Runtime Error 424: Object Required

You can't edit values once they've been added to a collection. So this is not possible:

aColl.Item(aKey) = aColl.Item(aKey) + someValue

Instead, you can take the object out of the collection, edit its value, and add it back.

temp = aColl.Item(aKey)
aColl.Remove aKey
aColl.Add temp + someValue, aKey

This is a bit tedious, but place these three lines in a Sub and you're all set.

Collections are more friendly when they are used as containers for objects (as opposed to containers for "primitive" variables like integer, double, etc.). You can't change the object reference contained in the collection, but you can manipulate the object attached to that reference.

On a side note, I think you've misunderstood the syntax related to Item. You can't say: aColl(aKey).Item. The right syntax is aColl.Item(aKey), or, for short, aColl(aKey) since Item is the default method of the Collection object. However, I prefer to use the full, explicit form...

Dictionaries are more versatile and more time efficient than Collections. If you went this route you could run an simple Exists test on the Dictionary directly below, and then update the key value

Patrick Matthews has written an excellent article on dictionaries v collections

Sub Test()
    Dim MyDict
    Set MyDict = CreateObject("scripting.dictionary")
    MyDict.Add "apples", 10
    If MyDict.exists("apples") Then MyDict.Item("apples") = MyDict.Item("apples") + 20
    MsgBox MyDict.Item("apples")
End Sub

I think you need to remove the existing key-value pair and then add the key to the collection again but with the new value