VBA/Excel/Access/Word/Data Type/Collection

Accessing an Item in a Custom Collection

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "D", "D"
    colNames.Add "A2", "A2"
    colNames.Add "B", "B"
    colNames.Add "S", "S"
    colNames.Add "S3", "S2"
    Debug.Print colNames.Item(1)
 
End Sub

Adding Items to a Custom Collection

 
Sub NewCollection()
   Dim colSports As New Collection
   colSports.Add "Basketball"
   colSports.Add "Skiing"
   colSports.Add "Skating", Before:=1
   colSports.Add "Hockey", After:=2
 End Sub

Create collection

 
Sub NewEmployees()
    Dim colEmployees As New Collection
    Dim emp As Variant
    With colEmployees
        .Add Item:="John Collins", Key:="128634456"
        .Add Item:="Mary Poppins", Key:="223998765"
        .Add Item:="Karen Loza", Key:="120228876", Before:=2
    End With
    For Each emp In colEmployees
        Debug.Print emp
    Next
    MsgBox "There are " & colEmployees.Count & " employees."
End Sub

Creating and Working with Custom Collections

 
Sub AddToCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "B"
    colNames.Add "C", "D"
    colNames.Add "E", "F"
    colNames.Add "G", "H"
    colNames.Add "I", "J"
    colNames.Add "K", "ZL"
End Sub

Item method is the default method of the Collection object

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "D", "Dan"
    colNames.Add "Al", "Alexis"
    colNames.Add "B", "Brendan"
    colNames.Add "S", "Sonia"
    colNames.Add "Su", "Sue"
    Debug.Print colNames(1)
    Debug.Print colNames.Item(1)
 
End Sub

Iterating Through the Elements of a Custom Collection: use the For...Each loop to iterate through the items in a collection

 
Sub IterateCollection()
    Dim colNames As Collection
    Dim varItem As Variant
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "Dan", "Dan"
    colNames.Add "Al", "Ale"
    colNames.Add "B", "Bren"
    colNames.Add "S", "Son"
    colNames.Add "Sue", "Su"
    For Each varItem In colNames
        Debug.Print varItem
    Next varItem
End Sub

Looping Through the Elements of a Custom Collection

 
Sub LoopThroughCollection()
   Dim colSports As New Collection
   Dim varSport As Variant
   colSports.Add "Basketball"
   colSports.Add "Skiing"
   colSports.Add "Skating", Before:=1
   colSports.Add "Hockey", After:=2
   For Each varSport In colSports
      Debug.Print varSport
   Next varSport
End Sub

Referencing Items in a Custom Collection

 
Sub CustomKey()
   Dim colSports As New Collection
   colSports.Add "Basketball", "B"
   colSports.Add "Skiing", "S1"
   colSports.Add "Skating", "S2"
   colSports.Add "Hockey", "H"
   Debug.Print colSports.Item("S1")
End Sub

Refer to an item in a collection using its unique key

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "D", "Da"
    colNames.Add "Al", "Al"
    colNames.Add "Br", "Br"
    colNames.Add "S", "So"
    colNames.Add "Su", "Su"
    Debug.Print colNames(1)
    Debug.Print colNames.Item(1)
    Debug.Print colNames("Alexis")
End Sub

Remove all the elements of a collection in two ways:

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "Dan", "Dan"
    colNames.Add "Al", "Ale"
    colNames.Add "B", "Bre"
    colNames.Add "S", "Son"
    colNames.Add "Sue", "Su"
    Set colNames = New Collection
    "Or Set colNames = Nothing
End Sub

Remove element with the key

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "Dan", "Dan"
    colNames.Add "Al", "Ale"
    colNames.Add "B", "Bre"
    colNames.Add "S", "So"
    colNames.Add "Sue", "Su"
    colNames.Remove "Sonia"
    Debug.Print colNames(1)
    Debug.Print colNames.Item(1)
    Debug.Print colNames("Alexis")
End Sub

Removing Items from a Custom Collection

 
Sub AccessCollection()
    Dim colNames As Collection
    Set colNames = New Collection
    colNames.Add "A", "A"
    colNames.Add "Dan", "Dan"
    colNames.Add "Al", "Al"
    colNames.Add "B", "Br"
    colNames.Add "S", "So"
    colNames.Add "Sue", "Su"
    colNames.Remove 2
    Debug.Print colNames(1)
    Debug.Print colNames.Item(1)
    Debug.Print colNames("Alexis")
End Sub

Removing Objects from a Collection

 
Sub deleteEmployees()
    " declare the employees collection
    Dim colEmployees As New Collection
    " declare a variable to hold each element of a collection
    Dim emp As Variant
    " Add 3 new employees to the collection
    With colEmployees
        .Add Item:="John Collins", Key:="128634456"
        .Add Item:="Mary Poppins", Key:="223998765"
        .Add Item:="Karen Loza", Key:="120228876", Before:=2
    End With
    " list the members of the collection
    For Each emp In colEmployees
        Debug.Print emp
    Next
    MsgBox "There are " & colEmployees.Count & " employees."
    
    colEmployees.Remove (3)
    MsgBox colEmployees.Count & " employees remain."
End Sub

Using a Collection to Manipulate Multiple Instances of the FileInformation Class

 
Sub FileInfoCollection()
    Dim colFiles As Collection
    Dim objFileInfo As FileInformation
    Dim strFile As String
    Dim vntFile As Variant
    Set colFiles = New Collection
    strFile = Dir("c:\")
    Do Until Len(strFile) = 0
        Set objFileInfo = New FileInformation
        objFileInfo.FullFileName = strDirName & strFile
        colFiles.Add objFileInfo
        strFile = Dir()
    Loop
    For Each vntFile In colFiles
        Debug.Print vntFile.Drive, vntFile.Path, vntFile.Name
    Next vntFile
End Sub