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:
You have 6 parts
We will now be removing Window
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 PostsLoops – For Each
Working With Form Objects