How to retain collection in function?

[durex]

Im looking to create a function which I can call that adds certain
values from an excel spreadsheet to a New Collection and retain those
values throughout the script, but every time I call the function the
collection is being re-declared so the collection gets cleared out
everytime I call it...

Heres what Im looking (hoping) to do...

Code:
--------------------
... code ....

call Blah(col, row)

Private Function Blah(whatever)
Dim colSomething As New Collection
colSomething.Add Range(col,row)

... do some other stuff and return something back to the routine that called this ...
End Function

--------------------


... and I need the collection to retain the value that it grabs so that
I can continue adding additional values to it whenever that function is
called.

This it taken way out of context of what Im actually doing, but the
functionality is basically the same. Im still a little rusty when it
comes to this advanced stuff... Ive tried using Static instead of Dim,
but that didnt seem to work either.

Any ideas??

Thanks a bunch everyone!

 

[Dave Peterson]

How about something like:

Option Explicit
Dim ColSomething As Collection

Function Blah(whatever as variant)
Dim i As Long

If ColSomething Is Nothing Then
'initialize colsomething
Set ColSomething = New Collection
For i = 1 To 3
ColSomething.Add i, CStr(i)
Next i
End If
On Error Resume Next 'just it case it's there
ColSomething.Add whatever, CStr(whatever)
On Error GoTo 0

End Function


Sub testme()
Dim x As Long
x = 100
Call Blah(x)
End Sub

 

[durex]

Thanks a ton Dave - this worked perfectly!