Home > How-To Library > Arrays, Collections, Lists

How to replace an item in a collection

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

'Collections don't allow you to modify the value of an item. If you want to 
'change the value of an item, you must first delete it and then add a new item.
'Here's generic routine that uses this technique:

' INDEX can be either a numeric or a string value.
Sub ReplaceItem(col As Collection, index As Variant, newValue As Variant)
    ' First remove that item (exits with error if it doesn't exist).
    col.Remove index
    ' Then add it again.
    If VarType(index) = vbString Then
        ' Add a new item with the same string key.
        col.Add newValue, index
    Else
        ' Add a new item in the same position (without any key).
        col.Add newValue, , index
    End If
End Sub

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.