Option Base 1
Dim MyCollection As New Collection
Public Sub Main()

' **************************************************************************
' We have a new collection (MyCollection) so we should be able to add to it, list items in it, etc.  The collection doesn't care about duplicates, so if you keep adding while the macro active, the collection will simply keep getting larger.  It's the same as working with a dynamic array - except it gives you more flexibility (can establish special hash keys, remove items without redimensioning, etc.)  Can also add other collections! with no limit to the nesting of collections.  Type of object being added doesn't matter.
***************************************************************************

String1 = "This is my first string"
String2 = "This is my second string"
Number1 = 40
String4 = "This is my fourth string"
String5 = "This is my fifth string"

' *************Adding simple values to the collection ***************************
' When adding members to a collection - you also have the ability to designate a KEY, Before, After (remember that keys must be unique).  Can also add objects such as Userform buttons, etc.
' ***********************************************************************

MyCollection.Add String1, "Name"' A key can also be added - though not needed for all
MyCollection.Add Number1
MyCollection.Add String4
MyCollection.Add String5

' Show how many items in collection
n = MyCollection.Count

' List each item in the collection
For i = 1 To MyCollection.Count
    Temp = MyCollection.Item(i)
    Temp = MyCollection(i)' Alternate addressing method
Next

' Remove the 1st or 2nd item from the collection
' As soon as an item is removed - the remaining items are renumbered (this is the advantage of using keys.

MyCollection.Remove "Name"
MyCollection.Remove 2

' Using function to test for existence in collection
' The first parameter is MyCollection (name of collection), then Number1 (lookup term), then No (not case sensitive)

If ExistsInCollection(MyCollection, Number1, "No") = True Then
    MsgBox "The item exists in the collection."
Else
    MsgBox "The item doesn't exist in the collection."
End If

' To clear the collection (while macro executing)
Set MyCollection = Nothing

' Collections will remain active as long as the macro is executing - retained in memory, similar to a public variable.  However, when the collection is redimensioned i.e., the macro begins) then the old values are erased.
BYE:
End Sub

Private Function ExistsInCollection(NameOfCollection, LookupTerm, CaseYesNo)
Dim Result As Boolean
For i = 1 To NameOfCollection.Count
    If CaseYesNo = "Yes" Then
        If UCase(LookupTerm) = UCase(NameOfCollection(i)) Then
            Result = True
            GoTo BYE
        End If
    Else
        If LookupTerm = NameOfCollection(i) Then
            Result = True
            GoTo BYE
        End If
    End If
Next
Result = False
BYE:
ExistsInCollection = Result
End Function
End Sub

' VB's Collection object provides a great way to store code objects in one spot.   Naturally, at some point, your code will want to determine if a Collection already contains a specific item, based on either an index number or a key value.  Unfortunately, the Collection object doesn't come with a built-in method to do so.  As a result, we've come up with our own function to make this test, as seen here:

Private Function ContainsItem(col As Collection, val As Variant) As Boolean
Dim itm As Variant
On Error Resume Next
itm = col.Item(val)
ContainsItem = Not (Err.Number = 5 Or Err.Number = 9)
On Error GoTo 0
End Function

' As you can see, this function relies on the fact that VB generates an error if the code tries to refer to an item that doesn't exist.  If VB doesn't generate an error, then the item exists.  The function tests for either of the potential errors and then returns the Boolean results.

' The following code illustrates how to use this function:

Private Sub Form_Load()
Dim col As Collection
Dim x As Integer
Set col = New Collection
With col
    For x = 1 To 10
        .Add x, "k" & x
    Next x
End With
Debug.Print "Contains index 11: " & ContainsItem(col, 11)
Debug.Print "Contains key k3: " & ContainsItem(col, "k3")
Debug.Print "Contains key k11: " & ContainsItem(col, "k11")
End Sub