VBA Collections Adding Items to a Collection
Example
Items are added to a Collection by calling its .Add method:
Syntax:
.Add(item, [key], [before, after])
| Parameter | Description |
|---|---|
| item | The item to store in the Collection. This can be essentially any value that a variable can be assigned to, including primitive types, arrays, objects, and Nothing. |
| key | Optional. A String that serves as a unique identifier for retrieving items from the Collection. If the specified key already exists in the Collection, it will result in a Run-time error 457: "This key is already associated with an element of this collection". |
| before | Optional. An existing key (String value) or index (numeric value) to insert the item before in the Collection. If a value is given, the after parameter must be empty or a Run-time error 5: "Invalid procedure call or argument" will result. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result. |
| after | Optional. An existing key (String value) or index (numeric value) to insert the item after in the Collection. If a value is given, the before parameter must be empty. Errors raised are identical to the before parameter. |
Notes:
-
Keys are not case-sensitive.
.Add "Bar", "Foo"and.Add "Baz", "foo"will result in a key collision. -
If neither of the optional before or after parameters are given, the item will be added after the last item in the
Collection. -
Insertions made by specifying a before or after parameter will alter the numeric indexes of existing members to match thier new position. This means that care should be taken when making insertions in loops using numeric indexes.
Sample Usage:
Public Sub Example()
Dim foo As New Collection
With foo
.Add "One" 'No key. This item can only be retrieved by index.
.Add "Two", "Second" 'Key given. Can be retrieved by key or index.
.Add "Three", , 1 'Inserted at the start of the collection.
.Add "Four", , , 1 'Inserted at index 2.
End With
Dim member As Variant
For Each member In foo
Debug.Print member 'Prints "Three, Four, One, Two"
Next
End Sub
