Collection Function
 

The Function below fills a Collection with my Products, but when I call the function a second time, I don't want it to have to run. I think I've had a line like this working befo If Not colProducts Is Nothing Then Exit Function
....but when I step through, my collection actually IS nothing when I enter it the second time. Any ideas why?

Thank
Ro

sub testi
msgbox colproducts(1
msgbox colproducts(2
end su

Public Function colProducts() As Collectio
'Don't bother populating if it's already ful
If Not colProducts Is Nothing Then Exit Functio

'Populate the collectio
For i = 1 To 3
colProducts.Add Cells(i, 1).Valu
Nex
End Functio

Collection Function
 

ColProducts is not the object, it is just a function that populates a
collection object.

What you need is to trap the caller, something like

Dim colProd As Collection

If colProd Is Nothing Then
Set colProd = New Collection
colProd = ColProducts
End If


Public Function colProducts() As Collection
Dim i
Dim xx As New Collection
'Populate the collection
For i = 1 To 30
xx.Add Cells(i, 1).Value, CStr(Cells(i, 1).Value)
Next
Set colProducts = xx
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob" wrote in message
...
The Function below fills a Collection with my Products, but when I call
the function a second time, I don't want it to have to run. I think I've
had a line like this working befo If Not colProducts Is Nothing Then
Exit Function
...but when I step through, my collection actually IS nothing when I enter
it the second time. Any ideas why??

Thanks
Rob

sub testit
msgbox colproducts(1)
msgbox colproducts(2)
end sub

Public Function colProducts() As Collection
'Don't bother populating if it's already full
If Not colProducts Is Nothing Then Exit Function

'Populate the collection
For i = 1 To 30
colProducts.Add Cells(i, 1).Value
Next
End Function

Collection Function
 

Rob,

Try something like

Public Function colProducts() As Collection
Dim i As Long
Static C As Collection
If C Is Nothing Then
Set C = New Collection
End If
If C.Count = 0 Then
For i = 1 To 30
C.Add Cells(i, 1).Value
Next
End If
Set colProducts = C
End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Rob" wrote in message
...
The Function below fills a Collection with my Products, but
when I call the function a second time, I don't want it to have
to run. I think I've had a line like this working befo If
Not colProducts Is Nothing Then Exit Function
...but when I step through, my collection actually IS nothing
when I enter it the second time. Any ideas why??

Thanks
Rob

sub testit
msgbox colproducts(1)
msgbox colproducts(2)
end sub

Public Function colProducts() As Collection
'Don't bother populating if it's already full
If Not colProducts Is Nothing Then Exit Function

'Populate the collection
For i = 1 To 30
colProducts.Add Cells(i, 1).Value
Next
End Function