Collection Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 before: 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
 
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 said:
The Function below fills a Collection with my Products, but when I call
Click to expand...
the function a second time, I don't want it to have to run. I think I've
had a line like this working before: If Not colProducts Is Nothing Then
Exit Function
...but when I step through, my collection actually IS nothing when I enter
Click to expand...
it the second time. Any ideas why??
 
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 said:
The Function below fills a Collection with my Products, but
Click to expand...
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 before: If
Not colProducts Is Nothing Then Exit Function
...but when I step through, my collection actually IS nothing
Click to expand...
when I enter it the second time. Any ideas why??
 
Back
Top