How to change value of an item of a collection

Question

With this code (in excel-vba) I add to a collection a number of items depending on an array.
I use the value of the array as key and the string "NULL" as value for each item added.

Dim Coll As New collection
Dim myArr()

Set Coll = New collection
myArr() = Array("String1", "String2", "String3")

For i = LBound(myArr) To UBound(myArr)
    Coll.Add "NULL", myArr(i)
Next i

Now, if I want to change the value of an item, identifying it by the key, I must remove the item and then add an item with same key or is it possible to change the item value?

This below is the only way?

Coll.Remove "String1"
Coll.Add "myString", "String1"

Or is there something like: (I know that doesn't work)

Coll("String1") = "myString"

Solution 1

You can also write a (public) function to make updates to a collection.

public function updateCollectionWithStringValue(coll as Collection, key as string, value as string) as collection
    coll.remove key
    coll.add value, key
    set updateCollectionWithStringValue = coll
end function

You can invoke this function by:

set coll = updateCollectionWithStringValue(coll, "String1","myString")

Then you have a one liner to invoke.

Solution 2

Can't you use the Before argument to fulfill this requirement?

Example:

Option Explicit

Sub TestProject()
    Dim myStrings As New Collection

    myStrings.Add item:="Text 1"
    myStrings.Add item:="Text 2"
    myStrings.Add item:="Text 3"

    ' Print out the content of collection "myStrings"
    Debug.Print "--- Initial collection content ---"
    PrintCollectionContent myStrings
    ' Or with the "Call" keyword: Call PrintCollectionContent(myStrings)
    Debug.Print "--- End Initial collection content ---"

    ' Now we want to change "Text 2" into "New Text"
    myStrings.Add item:="New Text", Before:=2 ' myStrings will now contain 4 items
    Debug.Print "--- Collection content after adding the new content ---"
    ' Print out the 'in-between' status of collection "myStrings" where we have
    ' both the new string and the string to be replaced still in.
    PrintCollectionContent myStrings
    Debug.Print "--- End Collection content after adding the new content ---"

    myStrings.Remove 3
    ' Print out the final status of collection "myStrings" where the obsolete 
    ' item is removed
    Debug.Print "--- Collection content after removal of the old content ---"
    PrintCollectionContent myStrings
    Debug.Print "--- End Collection content after removal of the old content ---"

End Sub

Private Sub PrintCollectionContent(ByVal myColl As Variant)
    Dim i as Integer

    For i = 1 To myColl.Count()
        Debug.Print myColl.Item(i)
    Next i
End Sub

Shouldn't this do the job?

Solution 3

Here is a solution where Coll("String1") = "myString" does work.

When you .Add an object to a VBA collection, the object itself is added, not its value. This means you can change the object's properties while it is in the collection. I've created a class module which wraps a single variant in a class object, with .Value as its default property. Save this to a .cls file, then File > Import File in the VBA editor.

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "clsValue"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit
Private MyValue As Variant
Property Get Value() As Variant
    Attribute Value.VB_UserMemId = 0
    Value = MyValue
End Property
Property Let Value(v As Variant)
    Attribute Value.VB_UserMemId = 0
    MyValue = v
End Property

Now this version of your code works the way you had hoped:

Private Sub clsValue_test()
Dim Coll As New Collection
Dim myArr()
Dim v As Variant
myArr = Array("String1", "String2", "String3")
For Each v In myArr
    Coll.Add New clsValue, v
    Coll(v) = "NULL"
Next v
Coll("String1") = "myString"    ' it works!
For Each v In myArr
    Debug.Print v, ": "; Coll(v)
Next v
End Sub

Produces the result:

String1       : myString
String2       : NULL
String3       : NULL

Comments

[Mathieu Guindon]

Have you tried using a Dictionary (from the scripting library) instead?

[L42]

AFAIK and as what MSDN say, that is the only way. The Mug is correct, use a dictionary instead. If you decide to take that route, check this out.

[Amen Jlili]

You can't do that with collection.

[genespos]

Ok, only another question: is there any contraindication for using dictionary instead of collection?

[Amen Jlili]

Yes. This will help you. I found it the other day: youtube.com/watch?v=dND4coLI_B8

[Marcelo Scofano Diniz]

+1 because it fulfills the OP's request; but for that, you have to fix: 1) the first two lines in fact are one only, and you left a crazy leftover (Set myStringsRef = myStrings) that was nor declared nor necessary; 2) PrintCollectionContent (myStringsRef) - or (myStrings) your call - need that OR use Call before, OR remove parenthesis after.

[GeertVc]

@MarceloScofano: You're correct in that I've used the "crazy leftover" because at that time I didn't know exactly when to use Call or not (with only one param I know now you don't have to use Call, very confusing at first). Therefore, I used that "useless" variable myStringsRef. I also didn't use the Option Explicit in my example code, so there was no need to declare that one first. Being a VBA newbee at that time, I know now that I don't want to work anymore without the Option Explicit and I also know now better the way the Collections are working... I edited my answer.

[Marcelo Scofano Diniz]

Yeah, I said crazy because at first I swore that it fits in the code, so I declared myStringsRef and did a mess! Only after some minutes spent I decided to get rid of it and could see that your answer fits. Thanks for your answer, it convinced me that I do not have to use dictionary for what I need. And for reference for everyone that is passing here and do not understand why Call () vs no(), here is an explanation.

[Anton Rybalko]

Typo: coll ax Collection must be coll as Collection