Returning a collection from a function.

[Joe in Australia via OfficeKB.com]

How do I create a function which returns a collection? I've tried this:

Function ListOfDates(ThisDate As Date, NumberofDays) As Collection
Dim MyLoop As Integer
For MyLoop = 1 To NumberofDays
ListOfDates.Add MyLoop, ThisDate + MyLoop - 1
Next MyLoop
End Function

But that gets me "Object variable or With block variable not set". On the
other hand, if I insert the line

ListOfDates = New Collection

it tells me that the arguments (to ListOfDates I assume) are not optional.

 

[Jay Freedman]

How do I create a function which returns a collection? I've tried this:

Function ListOfDates(ThisDate As Date, NumberofDays) As Collection
Dim MyLoop As Integer
For MyLoop = 1 To NumberofDays
ListOfDates.Add MyLoop, ThisDate + MyLoop - 1
Next MyLoop
End Function

But that gets me "Object variable or With block variable not set". On the
other hand, if I insert the line

ListOfDates = New Collection

it tells me that the arguments (to ListOfDates I assume) are not optional.

Hi Joe,

You can't work directly on the result of the function. You have to
define a temporary collection, add to it, and finally assign the
temporary collection as the result.

Once you get past that, there are a couple of other issues with your
code. One is that, if you want to put in the parameters of the .Add
method by position, you have the Item and Key parameters backwards
(see the .Add help topic). It's best to avoid this by putting in the
parameter names. The other issue is that for some reason .Add won't
automatically convert the integer MyLoop to the String type expected
by the Key parameter, so you have to use the CStr function.

This code works:

Function ListOfDates(ThisDate As Date, NumberofDays) As Collection
Dim MyLoop As Integer
Dim MyDates As New Collection

For MyLoop = 1 To NumberofDays
MyDates.Add Item:=ThisDate + MyLoop - 1, Key:=CStr(MyLoop)
Next MyLoop

Set ListOfDates = MyDates
End Function

Sub test()
Dim myList As Collection
Dim msg As String, ind As Integer

Set myList = ListOfDates(CDate("1/29/2006"), 5)

For ind = 1 To 5
msg = msg & Format(myList(ind), "MMMM d, yyyy") & vbCr
Next
MsgBox msg
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.