VBA includes a collection object that you can use to represent a group of items.
Members of a collection don't have to share the same data type.
A Collection object is an ordered set of items that can be referred to as a unit.
You can think of a collection as an array and refer to items using their index numbers or, items can be referred to by using their actual name.
A collection is an object that contains a group of related objects.

Dim colMyCollection As Collection 
Set colMyCollection = New Collection

Any Data Type

A collection can contain values of any data type.

oMyCollection.Add "string" 
oMyCollection.Add 100
oMyCollection.Add myObject

Collection Methods

Add - Adds an item to the collection allowing you to provide a key value to easy finding
Count - Returns the number of items in the collection
Item - Returns an item from the collection, either using its index number or its key value
Remove - Deletes an item from the collection, you can use either its index number or its key value

Built-in Collections

Excel Workbooks is an example of a collection.
Most of the collections in the Office model are 1-based which means they start at index position 1 rather than 0.
Collections in the office object model are generally always 1-based, meaning that they start with an index of 1 and not 0.
The majority of them are 1-based although not all of them.
The name of a collection object is typically the plural of the type of object it contains.
For example Workbooks is a collection of Workbook objects.
The Item method can generally always accept either a numeric value or the name of a specific object.


Returns the number of item in the collection.
When you are returning a collection object from a function

Using with Classes

If you want to maintain a list of all the instances of a particular class then you could do this by using a Collection.
A collection is a group of objects of the same class.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext