Setting the Item property of a Collection in VBA

Question

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
    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

This answer to an earlier, related question should also answer your question: http://stackoverflow.com/questions/5709444/modify-value-by-key/5710063#5710063 . The short version is that Collections don't work like arrays; you can't reassign an "element". The answers others have already given you are good alternate ways to get what you want. — jtolle, Sep 27 '11 at 01:05

score 15 · Accepted Answer · answered Sep 27 '11 at 10:52

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...

brettdj · Answer 2 · 2013-01-28T09:30:34.453

9

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

edited Jan 28 '13 at 09:30

answered Sep 27 '11 at 00:17

brettdj

54,857
16
114
177

1

Dictionary is better than Collection for everything except one thing: Dictionary does not preserve order. In most of my applications, order *is* important; I got screwed over once or twice by Dictionary because of this! – Jean-François Corbett Sep 27 '11 at 18:20

score 0 · Answer 3 · answered Sep 26 '11 at 23:55

0

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

answered Sep 26 '11 at 23:55

barrowc

10,444
1
40
53

Setting the Item property of a Collection in VBA

3 Answers3

Linked