VBA Collections driving me nuts!

Question

Hi guys,

I'm still learning VBA and I can't figure out wth I'm having so many problems with a Collections object.

I have a function that adds custom objects (I created a very simple class to store some data) that does the typical "read data, create object representation, stick it into Collections" sort of stuff.

If I try to add a "key" to the bag.add call I get a "Compile error. Expected:=" message.

If I don't it appears to have worked then when I run the program it says "Compile Error. Argument not optional" and highlights the "getRevColumns = bag" line.

I can't for the life of me figure out wth is going on! I suspect something wrong with how I initialized my bag?! PS: columnMap is the name of my custom class.

Function getRevColumns() As Collection

Dim rng As Range
Dim i As Integer
Dim bag As Collection
Dim opManCol As Integer, siebelCol As Integer
Dim opManColName As String, siebelColName As String
Dim itm As columnMap

Set bag = New Collection
Set rng = shSiebelMap.UsedRange.Columns(5)

i = 1
For i = 1 To rng.Rows.count

    If StrComp(UCase(rng.Cells(i).value), "Y") = 0 Then

        opManCol = rng.Rows(i).OffSet(0, -2).value
        opManColName = rng.Rows(i).OffSet(0, -4)
        siebelCol = rng.Rows(i).OffSet(0, -1).value
        siebelColName = rng.Rows(i).OffSet(0, -3)

        Set itm = New columnMap
        itm.opManColName = opManColName
        itm.opManColNumber = opManCol
        itm.siebelColName = siebelColName
        itm.siebelColNumber = siebelCol

        'WHY DOESN'T IT WORK!''
        bag.Add (itm)

        'MsgBox "opMan Col: " & opManColName & " : " & opManCol & ". Siebel Col: " & siebelColName & " : " & siebelCol'

    End If

Next i

getRevColumns = bag

End Function

Answer 1

the bag is an object. Rule #1 for objects use Set

Set getRevColumns = bag

Answer 2

Try removing the parens around itm in the add:

bag.Add itm

or

bag.Add itm, key

It's been a while since I've had to work with VBA/VB6, but I believe including the parens causes itm to be passed by value instead of by reference. I could be wrong.

Answer 3

You need to say

set getRevColumns = bag

also I guess you have a problem on the add. I don't know why this is but it works on

bag.add itm

I tried the whole thing in a simple manner here is my working code

Sub myroutine()

     Dim bag As Collection
     Dim itm As clsSimple

     Set bag = getTheCollection()

     Set itm = bag.Item(1)
     MsgBox (itm.someObjectValue)

     Set itm = bag.Item(2)
     MsgBox (itm.someObjectValue)


End Sub

Function getTheCollection() As Collection

        Dim bag As Collection
        Dim itm As clsSimple

        Set bag = New Collection

        Set itm = New clsSimple
        itm.someObjectValue = "value 1"
        bag.Add itm

        Set itm = New clsSimple
        itm.someObjectValue = "value 2"
        bag.Add itm

        Set getTheCollection = bag

End Function

The class is really simple:

Public someObjectValue As String

Hope it helps