Collection object
2020-07-01 VBA programming 0 737
A collection is an object where you can store items of any datatype, and unlike arrays you don't have to set the size of how many items you want to store in the collection.
More information about array variables.
More information about the Dictionary object.
The collection object:
- Can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays)
- Each item can optionally be associated with a unique key that must be a string
- The associated key for each item can not be retrieved, e.g. when looping through the collection items
- Usually you would only add items of the same datatype to the collection
- It is possible to add items of mixed datatypes to the collection
- Items can be added before or after existing items in the collection
- Looping through a collection is as fast as a dictionary when using For Each - Next
Below is some code examples on how to create a collection object, populate it with items and then retrieving one or more items:
Sub Example_Collection() ' updated 2020-06-26 by OPE Dim coll As Collection, i As Long, lngValue As Long, varItem As Variant, varArray() As Variant, objEmployee As clsEmployee ' collection object: ' can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays) ' each item can optionally be associated with a unique key that must be a string ' the associated key for each item can not be retrieved, e.g. when looping through the collection items ' usually you would only add items of the same datatype to the collection ' it is possible to add items of mixed datatypes to the collection ' items can be added before or after existing items in the collection ' looping through a collection is as fast as a dictionary when using For Each - Next Set coll = New Collection ' create a new collection object ' add items to a collection For i = 1 To 10 coll.Add i * 100 ' add a new item without a key, items can only be retrieved by using their index numbers or a For Each - Next loop Next i ' add items to a collection For i = 1 To 10 coll.Add i * 100, CStr(i) ' add a new item with a key, the key must be a string, an error will occur if the key already exists Next i ' add items to a collection For i = 1 To 10 On Error Resume Next coll.Add i * 100, CStr(i) ' add a new item with a key, the key must be a string, ignore error if the key already exists On Error GoTo 0 Next i ' add items to the collection before or after any existing items coll.Add 111, CStr(111), Before:=2 ' add a new item with a key, the key must be a string, an error will occur if item already exists coll.Add 222, CStr(222), Before:="5" ' add a new item with a key, the key must be a string, an error will occur if item already exists coll.Add 333, "333", After:=2 ' add a new item with a key, the key must be a string, an error will occur if item already exists coll.Add 444, "444", After:="5" ' add a new item with a key, the key must be a string, an error will occur if item already exists ' remove items from the collection coll.Remove 10 ' remove item from collection using an index number between 1 to coll.Count coll.Remove "9" ' remove item from collection using a key identifier, the key must be a string coll.Remove coll.Count ' remove the last item from collection using an index number between 1 to coll.Count ' use items in the collection Debug.Print "Count of items in the collection: " & coll.Count Debug.Print "This is the value of the first item: " & coll(1) Debug.Print "This is the value of the last item: " & coll(coll.Count) Debug.Print "This is the value of the item for key ""333"": " & coll("333") ' update items in the collection 'coll(1) = coll(1) + 1 ' this will not work, you can't change the collection item ' if the collection item is an object you can change properties of the object (see example below) ' you can update an item by removing it and adding it again with an updated value lngValue = coll("itemkey") + 1 ' calculate the new value coll.Remove "itemkey" ' remove an existing item from the collection coll.Add lngValue, "itemkey", Before:=1 ' add the updated value to the collection, optionally at the desired position ' loop through the collection Debug.Print "Collection: " & coll.Count ' using a For Each - Next loop is the preferred and significantly fastest method for looping through all items in a collection if the collection has many items i = 0 For Each varItem In coll ' varItem must be a Variant, Object or specific object type (e.g. Worksheet or clsEmployee) i = i + 1 Debug.Print i, varItem ' the associated key for each item is not available Next varItem ' using a For Next loop can be extremely slow to loop through all items in a collection with many items (10000 or more) For i = 1 To coll.Count Debug.Print i, coll(i) ' the associated key for each item is not available Next i ' there is no easy way to write the collection content to a worksheet ' you would normally transfer the collection items to an array and write the array to the worksheet ' assumes that the items in the collection are normal datatypes, no objects or arrays If coll.Count > 0 Then ReDim varArray(1 To coll.Count, 1 To 1) i = 0 For Each varItem In coll i = i + 1 varArray(i, 1) = varItem ' populate the array with items from the collection Next varItem With Sheet1 On Error Resume Next .Range("A3:A" & .Rows.Count).Clear .Range("A3").Formula = "Items" .Range("A4").Resize(UBound(varArray, 1), 1).Formula = varArray With .Range("A3").CurrentRegion .Style = "Borders" .Rows(1).Style = "Tableheader" .EntireColumn.AutoFit End With On Error GoTo 0 End With End If ' remove all items in a collection (usually not necessary) ' this is slow and should be avoided: 'Do While coll.Count > 0 ' coll.Remove 1 ' remove item from collection 'Loop ' better alternatives: Set coll = Nothing ' delete the collection Set coll = New Collection ' create a new collection ' adding custom objects to a collection, properties for objects in the collection can be edited unless they are read-only Set objEmployee = New clsEmployee ' create a new object and add values to it With objEmployee .ID = 1 .Name = "Peder Aas" .Position = "Manager" .Salary = 100000 .StartDate = DateSerial(2000, 1, 1) End With coll.Add objEmployee, CStr(objEmployee.ID) ' add a new item with a key, an error will occur if the key already exists Set objEmployee = New clsEmployee ' create a new object and add values to it With objEmployee .ID = 2 .Name = "Lars Holm" .Position = "Supervisor" .Salary = 125000 .StartDate = DateSerial(2001, 1, 1) End With coll.Add objEmployee, CStr(objEmployee.ID) ' add a new item with a key, an error will occur if the key already exists ' loop through the collection Debug.Print "Collection: " & coll.Count For Each objEmployee In coll objEmployee.DebugPrint ' assumes that the object has a DebugPrint method With objEmployee ' update object in collection .Salary = .Salary * 1.05 .EndDate = DateSerial(Year(Date), 12, 31) End With Next objEmployee Debug.Print "After update:" For Each objEmployee In coll objEmployee.DebugPrint ' assumes that the object has a DebugPrint method Next objEmployee ' add some items of another datatype to the collection For i = 1 To 3 coll.Add i * 100 Next i ' loop through a collection with mixed data types Debug.Print "Collection with mixed datatypes: " & coll.Count i = 0 For Each varItem In coll i = i + 1 If TypeName(varItem) = "clsEmployee" Then varItem.DebugPrint Else Debug.Print "Not an employee: " & i, varItem, "TypeName: " & TypeName(varItem) End If Next varItem End Sub
If you need functionality to check if an item with a key has been added to a collection you can use the function below:
Function ExistsInColl(coll As Collection, strKey As String) As Boolean ' updated 2020-06-26 by OPE ' returns True if the collection contains an item associated with strKey (not case sensitive) ' example: If ExistsInColl(collEmployees, strEmployeeID) Then ' employee was found ExistsInColl = True On Error GoTo ErrorHandler TypeName coll(strKey) On Error GoTo 0 Exit Function ErrorHandler: ExistsInColl = False Resume Next End Function