Items - Updating

To refer to a member of a collection, you can refer to it by its position in the collection as an index or by its name, as a text string.

Update by position

Public Sub UpdateCollectionByIndex() 
Dim myCol As New Collection 
Dim indexToUpdate As Long 
Dim newValue As String 
    
    myCol.Add "Apple"   ' Index 1  
    myCol.Add "Banana"  ' Index 2  
    myCol.Add "Cherry"  ' Index 3  
    
    indexToUpdate = 2 
    newValue = "Blueberry" 
    
    myCol.Remove indexToUpdate 
    
    If indexToUpdate <= myCol.Count Then 
        myCol.Add newValue, Before:=indexToUpdate 
    Else 
        myCol.Add newValue 
    End If 
End Sub 

Update by key - make sure the key exists

Public Sub UpdateCollectionByKey() 
Dim myCol As New Collection 
Dim targetKey As String 
Dim newValue As String 
    
    myCol.Add "Apple", "ID101" 
    myCol.Add "Banana", "ID102" 
    
    targetKey = "ID101" 
    newValue = "Apple 2" 
    
    myCol.Remove targetKey 
    myCol.Add newValue, targetKey 
End Sub