How to use collections in Excel VBA

Collection of objects using Excel VBA

Collection provides a convenient way to refer to a group of objects and collections as a single object. The objects and collections in the collection don't have to be of the same data type.

In the following example we will use collections for a class (that is a collection of objects of type Vendor)

First define a class ClsVendor (Insert a Class Module to the project) and add the following item


Public VendorID As String
Public VendorName As String
Public VendorAddress As String


Public Function AddVendor(ByVal sVendorID As String, ByVal sVendorName As String, Optional ByVal sVendorAddress)

VendorID = sVendorID
VendorName = sVendorName
VendorAddress = sVendorAddress

End Function

The class has three public variables that can be initialized by AddVendor method.

Let us define a collection by

Private oColl As New Collection ' Collection to store Vendor

Now let us define a sub to create a collection of Vendors

Sub Add_Vendor_Info()

Dim oVendor As ClsVendor



Set oVendor = New ClsVendor

oVendor.AddVendor "EXV0023", "MCMASTER", "P.O. Box 94930, Cleveland, OH 44101-4930"
oColl.Add oVendor, "MCMASTER"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0024", "SHANTI GEARS"
oColl.Add oVendor, "SHANTI GEARS"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0025", "SKF"
oColl.Add oVendor, "SKF"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0026", "SUNDARAM FASTNERS"
oColl.Add oVendor, "SUNDARAM FASTNERS"

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & " " & oReqVen.VendorName & " " & oReqVen.VendorAddress

End Sub

Nornally, you would be reading the above information from a database or a flat file. You can also use the above example to get the information from an userform and add it to the collection

Here the Vendor objects are added to the collection using Add method. The syntax is as follows

object.Add item, key, before, after

The Add method syntax has the following object qualifier and named arguments:
object - Required. An object expression that evaluates to an object in the Applies To list.

item - Required. An expression of any type that specifies the member to add to the collection.

key - Optional. A unique string expression that specifies a key string that can be used,
instead of a positional index, to access a member of the collection.

before - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection before the member identified by the before argument. If a numeric expression, before must be a number from 1 to the value of the collection's Count property. If a string expression, before must correspond to the key specified when the member being referred to was added to the collection. You can specify a before position or an after position, but not both.

after - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection after the member identified by the after argument. If numeric, after must be a number from 1 to the value of the collection's Count property. If a string, after must correspond to the key specified when the member referred to was added to the collection. You can specify a before position or an after position, but not both.

Here we have added the item and the key

oColl.Add oVendor, "SUNDARAM FASTNERS"

The above adds "SUNDARAM FASTNERS" as a key to the corresponding Vendor. This Key will be useful to retrieve the information from the collection (as shown later)

Once a collection is created, its members can be retrieved using the Item method. The entire collection can be iterated using the For Each...Next statement

The Item method can be used with numeric or string arguments. If you are specifying a string argument, it should match the ‘Key’ of the member

Retrieving a specific member of a Collection object by position

Set oReqVen = oColl(1)
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress

Collection data retrieved using position
Retrieving a specific member of a Collection object by key

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress

Collection data retrieved using Key (EXcel VBA)

To delete a member from a collection using VBA use the remove method

oColl.Remove ("SKF")

or

oColl.Remove (3)

Collection after removing a member