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