Errors adding items to a VBA/VB6 Collection

Question

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

Is it possible that you post the class "columnMap" also, please? — hol, Jul 30 '10 at 08:07

score 11 · Accepted Answer · answered Jul 30 '10 at 08:09

11

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.

answered Jul 30 '10 at 08:09

Vincent

421
2
4

2

One word: Bizzare! Thanks a million mate. I'm still getting errors but now it's something else. – holografix Jul 30 '10 at 08:27
This behavior was also discussed in the Question `Hidden features of VBA` on StackOverflow. http://stackoverflow.com/questions/1070863/hidden-features-of-vba#1070942 – marg Jul 30 '10 at 09:51

score 3 · Answer 2 · answered Jul 30 '10 at 08:08

3

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

Set getRevColumns = bag

answered Jul 30 '10 at 08:08

renick

3,873
2
31
40

Yep you were definitely right there! Weird weird VBA... Where's the return keyword?! – holografix Jul 30 '10 at 08:28
1

There is no explicit return statement. The return value(or object as in your case) has to match the name of the function. – renick Jul 30 '10 at 09:03
Yep I got that after 20 minutes of scratching my head and an additional 10 of googling! – holografix Aug 09 '10 at 06:58

score 0 · Answer 3 · answered Jul 30 '10 at 08:58

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

It definitely does mate! Can't understand why I'm not supposed to use () around that add method... but anyway as long as it works. — holografix, Aug 09 '10 at 06:59
Yes is crazy but as Vincent mentioned before there seems to be some logic behind it how the parameter is passed (by value or by reference). I googled it for a while but couldn't find anything. VBA is somehow old VB6 and wasn't perfect. — hol, Aug 09 '10 at 08:44

score -1 · Answer 4 · edited Mar 12 '14 at 00:29

I had a similar problem with a collection.

I Dim'd it but hadn't set it with New or initialized it.

Basically i had

Dim collection1 As Collection
...
collection1.Add item     'no compile error just empty

I added the following before the add

Set collection1 = New Collection
Call collection1.init

then it worked like a charm...I had also moved the Dim statement from the Sub to the top of the Module to make it a class variable

Errors adding items to a VBA/VB6 Collection

4 Answers4