Hello Guys,

My previous post wasn't clear enough. So i'll detail this one a bit mo

Currently i have this function I USE in a worksheet:

Function Toef(sDate As Date, uitVM, inVM, uitNM, inNM, NaR, CAD As Single)
Dim hCol As New CollClass
Set hCollection = New Collection
Set hColRange = Worksheets("CODE").range("G5:G18")
For hCItemCounter = 1 To 15
hCollection.Add Item:=hColRange.Cells(hCItemCounter).Value
Next hCItemCounter
For Each hCItem In hCollection
If hCItem = sDate Then
'do a calculation
Exit Function
Else
Toef = ""
End If
Next hCItem
End Function

Because this function is used in a worksheet, this is kinda heavy in the
sense that the collection should only be created once, not each time for
every cell this function is used. So i've tried to put a part of this
function in a class named CollClass:

Public hCollection As Collection
Public hCItemCounter As Long
Public hCItem As Variant

Public Sub CreateRange()
Public hColRange As range
Set hCollection = New Collection
Set hColRange = Worksheets("CODE").range("G5:G18")
For hCItemCounter = 1 To 15
hCollection.Add Item:=hColRange.Cells(hCItemCounter).Value
Next hCItemCounter
End Sub

Next i would change my Function into:
(in the second line i'm creating an instance of the class, i thought this
would be enough). However: If I use this function, i get this error:

The cell is filled with the value: #VALUE!
"A value in the formula has a incorrect datatype"

Any suggestions guys?

Function Toef(sDate As Date, uitVM, inVM, uitNM, inNM, NaR, CAD As Single)
Dim hCol As New CollClass
For Each hCItem In hCollection
If hCItem = sDate Then
'do calculation
Exit Function
Else
Toef = ""
End If
Next hCItem
End Function

With regards everyone,