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