Modify value by key

Question

Dim dFeat As Collection
Set dFeat = New Collection

Dim cObj As Collection
Set cObj = New Collection
cObj.Add 3, "PASSED"
cObj.Add 4, "TOTAL"
dFeat.Add cObj, "M1"

Set cObj = New Collection
cObj.Add 5, "PASSED"
cObj.Add 6, "TOTAL"
dFeat.Add cObj, "M2"

dFeat("M1")("TOTAL") = 88 ' Error here
Debug.Print dFeat("M1")("TOTAL")

How do I modify the value of inner collection using the key?

Answer 1

Alex K.'s advice about using a Dictionary is correct, but I think the issue here is more general than his answer lets on. A Collection key (or index position for that matter) is only good for reading, not writing.

So in this line:

dFeat("M1")("TOTAL") = 88 ' Error here

dFeat("M1") is fine. It returns the Collection you added with key "M1". The error is happening because you try to directly assign to an element of that collection. In general, if c is a Collection, c("TOTAL") (or c(2)) can't be an lvalue.

As Alek K. says, the best way around this is to use a Dictionary for the inner "collections", or for both the inner and outer. Here is how using one for the inner would look:

Dim d As Dictionary
Set d = New Dictionary

d("PASSED") = 3
d("TOTAL") = 4

dFeat.Add d, "M1"

Then the line:

dFeat("M1")("TOTAL") = 88 

will work because dFeat("M1")("TOTAL") is a valid lvalue.

If for some reason you can't or don't want to include the MS Scripting Runtime, you'll have to replace the failing line with something like:

Dim c As Collection
Set c = dFeat("M1")

Call c.Remove("TOTAL")
Call c.Add(88, "TOTAL")

or more concisely:

Call dFeat("M1").Remove("TOTAL")
Call dFeat("M1").Add(88, "TOTAL")

Then, you can read the value of dFeat("M1")("TOTAL"), but you still can't assign to it.