Saturday, November 22, 2008

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

10 comments:

  1. Anonymous6:00 AM

    where is oReqVen declared?

    ReplyDelete
  2. Anonymous5:27 AM

    Good question, which is oReqVen type?

    Very instructive BTW, thanks very much for sharing your knowledge!

    ReplyDelete
  3. Anonymous2:37 PM

    i tried to run the code as is, and it would not work until i changed your vendor class definition to

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

    VendorID = sVendorID
    VendorName = sVendorName
    If VendorAddress <> "" Then VendorAddress = sVendorAddress

    End Function

    the optional value, if not sent to the function was giving me type mismatch errors.

    thanks so much for the tutorial though!!!

    ReplyDelete
  4. Anonymous2:26 PM

    May I buy a vowel? Please give me the statment that defines oReqVen

    I assume Dim oReqVen as ClsVendor??

    ReplyDelete
  5. Anonymous4:53 PM

    Thnka again!
    One adjustment:
    Public Function AddVendor(ByVal sVendorID As String, ByVal sVendorName As String, Optional ByVal sVendorAddress)

    VendorID = sVendorID
    VendorName = sVendorName
    If Not IsMissing(sVendorAddress) Then VendorAddress = sVendorAddress

    End Function

    ReplyDelete
  6. Anonymous9:40 AM

    Another method would be:

    Public Function AddVendor(ByVal sVendorID As String, ByVal sVendorName As String, Optional ByVal sVendorAddress = "Default Address")

    ReplyDelete
  7. Anonymous9:12 PM

    Why do I get the type mismatch error while adding each object to the collection??

    Can anybody help me out?
    Thanks so much

    ReplyDelete
    Replies
    1. Anonymous6:41 AM

      Add this in Sub Add_Vendor_Info():

      Set oColl = New Collection

      before:

      oColl.Add oVendor, "MCMASTER"

      Delete
  8. Anonymous1:37 AM

    what are you talking about???

    ReplyDelete
  9. Hello,

    Thanks for the awesome info on collections!

    I was wondering if you could help regarding a collection class that I have created (2 really).

    I Dim'd 2 collection classes in a form, and now I want to use a click event to cycle through all the entries in the collection. however, I am unable to as it is not 'aware' that the collection exists! Do you know how to reference a collection that was not populated within the code module calling it, and without passing the collection into the subroutine as an argument?

    Dave

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.