Items

All collections are 1 based.

Adding

Members can be added before or after an existing member based on both the index number or a key value
Collection.Add (item [,key] [,before] [,after])
item - the item you want to add to the collection
key - a unique string that can be used instead of using the positional index
before - the relative position index (or key string) of the element to have in front of it.
after - the relative position index (or key string) of the element to have behind it.

oMyCol.Add "Mon" 
oMyCol.Add ("Tue") 
oMyCol.Add "Mon", "M"     ' include a key  
oMyCol.Add ("Tue", "T")   ' include a key  

Adding Before

This adds "Wed" before "Mon"

oMyCol.Add "Mon" 
oMyCol.Add ("Tue") 
oMyCol.Add "Wed", Before:=1      ' wed, mon, tue  
oMyCol.Add "Thu", , 1            ' thu, wed, mon, tue  

Adding After

This adds "Wed" after "Mon"

oMyCol.Add "Mon" 
oMyCol.Add ("Tue") 
oMyCol.Add "Wed", After:=1      ' mon, wed, tue  
oMyCol.Add "Thu", , , 1         ' mon, tue, wed, thu  

Adding Using a Loop

Do not add items like this

For lrowno = 1 to 10 
   On Error Resume Next 
   colMyCollection.Add Cells(lrowno,1).value, Cells(lrowno,2).Value 
   On Error GoTo 0 
Next lrowno 

Adding Duplicates

The items in a collection do not have to be unique.

oMyCol.Add "Mon" 
oMyCol.Add "Mon" 
oMyCol.Add "Mon" 

When a key is not supplied the "key" field is left blank.
If a key has not been provided the only way to access the items is by their position.

Debug.Print oMyCol(1) 
Debug.Print oMyCol(1) 
Debug.Print oMyCol(1) 

You cannot identify or retrieve these items by key, because they don't have one.
If you do use keys, they must be unique.

Removing

Collection.Remove (index)
index - the relative position index (or key string) of the element you want to remove

Dim myCollection As VBA.Collection 
Set myCollection = New VBA.Collection 
myCollection.Add "Item 1" 
myCollection.Remove "Item 1" 

Remove All

A collection does not have a RemoveAll
Set myCollection = Nothing
Multiple deletion using the index number should be done in descending order because the collection is reindexed every time.

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 

Finding

Collection.Item (index)
References an item and returns a pointer to it
Returns a specific member of a Collection object either by position or by key.

If the value provided as index doesn't match any existing member of the collection, an error occurs.
The Item method is the default method for a collection.
The following lines of code are equivalent:

colMyCollection(1) 
colMyCollection.Item(1) 
colMyCollection("key") 
colMyCollection.Item("key") 

Advanced

Consider the following

for 
   skey = "unique key of the collection" 
   set objObject = colMyCollection(skey) 

Members can be retrieved using both the index number or a key value
when the key doesn't exist the above line does not reset the objObject to Nothing
therefore objObject keeps its previous value ??

How to determine if an item already exists

You have to loop through all the items to find the one you are looking for ??
Specific items can be returned using the Item method.
If you want to refer to a member of a collection, you can refer to it by its position in the collection, as an index number starting at 1 or by its name as quoted text.

You will obtain a "subscript out of range" error when you try to access an element in a collection that does not exist. Collections in some respects are similar to arrays.

Remember

Most of the collections in the Office model are 1-based which means they start at index position 1 rather than 0.