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

Accessing an Item in a Custom Collection

   <source lang="vb">

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

</source>
   
  

Adding Items to a Custom Collection

   <source lang="vb">

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
</source>
   
  

Create collection

   <source lang="vb">

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

</source>
   
  

Creating and Working with Custom Collections

   <source lang="vb">

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

</source>
   
  

Item method is the default method of the Collection object

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Looping Through the Elements of a Custom Collection

   <source lang="vb">

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

</source>
   
  

Referencing Items in a Custom Collection

   <source lang="vb">

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

</source>
   
  

Refer to an item in a collection using its unique key

   <source lang="vb">

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

</source>
   
  

Remove all the elements of a collection in two ways:

   <source lang="vb">

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

</source>
   
  

Remove element with the key

   <source lang="vb">

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

</source>
   
  

Removing Items from a Custom Collection

   <source lang="vb">

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

</source>
   
  

Removing Objects from a Collection

   <source lang="vb">

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

</source>
   
  

Using a Collection to Manipulate Multiple Instances of the FileInformation Class

   <source lang="vb">

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

</source>