VBA Retrieving Items From a Collection


Example

Items can be retrieved from a Collection by calling the .Item function.

Syntax:

.Item(index)
ParameterDescription
indexThe item to retrieve from the Collection. If the value passed is a numeric type or Variant with a numeric sub-type, it will be interpreted as a numeric index. If the value passed is a String or Variant containing a string, it will be interpreted as the a key. 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.

Notes:

  • .Item is the default member of Collection. This allows flexibility in syntax as demonstrated in the sample usage below.
  • Numeric indexes are 1-based.
  • Keys are not case-sensitive. .Item("Foo") and .Item("foo") refer to the same key.
  • The index parameter is not implicitly cast to a number from a String or visa-versa. It is entirely possible that .Item(1) and .Item("1") refer to different items of the Collection.

Sample Usage (Indexes):

Public Sub Example()
    Dim foo As New Collection
    
    With foo
        .Add "One"
        .Add "Two"
        .Add "Three"
        .Add "Four"
    End With
     
    Dim index As Long
    For index = 1 To foo.Count
        Debug.Print foo.Item(index) 'Prints One, Two, Three, Four
    Next
End Sub

Sample Usage (Keys):

Public Sub Example()
    Dim keys() As String
    keys = Split("Foo,Bar,Baz", ",")
    Dim values() As String
    values = Split("One,Two,Three", ",")
        
    Dim foo As New Collection
    Dim index As Long
    For index = LBound(values) To UBound(values)
        foo.Add values(index), keys(index)
    Next
     
    Debug.Print foo.Item("Bar") 'Prints "Two"
End Sub

Sample Usage (Alternate Syntax):

Public Sub Example()
    Dim foo As New Collection
    
    With foo
        .Add "One", "Foo"
        .Add "Two", "Bar"
        .Add "Three", "Baz"
    End With
    
    'All lines below print "Two"
    Debug.Print foo.Item("Bar")     'Explicit call syntax.
    Debug.Print foo("Bar")          'Default member call syntax.
    Debug.Print foo!Bar             'Bang syntax.
End Sub

Note that bang (!) syntax is allowed because .Item is the default member and can take a single String argument. The utility of this syntax is questionable.