Returning collections?

Hey folks,

A pretty simple question, all in the title: is it possible to return a collection in a function?

eg.

Public Function myFunc () As Collection
     Dim boo As New Collection
     Dim foo as New MyObjectType

     set foo = <some code to make it an object>
     set boo  = New collection
     boo.add(foo, myIdentString)
End Function

And then use it with:

Sub MySub
    Dim myColl As Collection

    Set myColl = New Collection

    myColl = myFunc()
End Sub

I'm asking the question because I'm getting an error ("Argument not optional"). My code to create an object is coded with the correct arguments so I assume that it's the collection creation that is in error. Since the call to myFunc() doesn't use the Add() procedure I'm suspicious that you can't return collections.

I'm fairly new to VBA programming and still getting the hang of things. Google (or my use of it) didn't seem to be of much use.

Thanks!

Hi clintonf

Too many New keywords in your code.

An Example:

Public Function myFunc() As Collection
     Dim boo As New Collection
     Dim foo As Range
     
     Set foo = Range("B3")
     boo.Add foo, "myIdentString"
     
     Set myFunc = boo
End Function

Use it with:

Sub MySub()
    Dim myColl As Collection

    Set myColl = myFunc()
    
    MsgBox myColl(1).Address
    MsgBox myColl("myIdentString").Address
End Sub

Hope it helps
lecxe

Hey there,

Thanks for your help

Sorry for the late reply, I didn't have subscriptions enabled