Collections

In this blog post we will be discussing collections and how they can be used in VBA.

Sub carParts()
    'A collection is an object that has the ability
    'to store other objects.
    '
    'Collections have 4 methods:
    'Add: Used to add an item to the collection
    'Remove: Used to remove an item from the collection
    'Count: Used to get the number of items in the Collection
    'Item: Used to reference an item in the collection
    '
    'We will be using all of the above methods in our code
    
    Dim parts As New Collection
    'First we create a collection
    Dim part As Variant
    
    parts.Add "Volvo"
    parts.Add 5
    parts.Add "V70R"
    parts.Add "Window"
    parts.Add "Drive"
    parts.Add #12/24/2012#
    'We use the Add method to add
    '6 items to the collection
    
  
    For Each part In parts
        Debug.Print part, TypeName(part)
        'Here we print each item in the collection
        'to the immediate window
        'we are also printing the type (String, integer)
        'of the item
    Next
    
    Debug.Print "You have " & parts.Count & " parts"
    'We now use the count method to return the number
    'of items in the collection
    Debug.Print
    'Prints a blank line
    
    Debug.Print "We will now be removing " & parts.Item(4)
    'We use the Item Method to print the 4th item in the
    'collection
    
    parts.Remove (4)
    'We use the Remove method to remove the 4th item
    'from the collection
    
    For Each part In parts
        Debug.Print part, TypeName(part)
        'Same again
    Next
    
    Debug.Print "You now have " & parts.Count & " parts"
    'Same again again
End Sub

The output of the code above will be:

Iterating Over The AllForms Collection

Access has a lot of built in collections. One of them is the AllForms collection that contains the names of all the forms in your database.

Public Sub allForms()
Dim form As Variant
For Each form In CurrentProject.allForms
'Here we reference the AllForms collection which
'lists all forms in the database
    Debug.Print form.Name
    'Prints the form name to the immediate window
Next
End Sub

Try it in your own database.

Collections are quite a handy way of storing multiple objects and have 4 useful methods that can be invoked.

Related Posts