VBA Collections Retrieving Items From a Collection
Example
Items can be retrieved from a Collection by calling the .Item function.
Syntax:
.Item(index)
| Parameter | Description |
|---|---|
| index | The 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:
.Itemis the default member ofCollection. 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
Stringor visa-versa. It is entirely possible that.Item(1)and.Item("1")refer to different items of theCollection.
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.
