I am writting some function in Excel using VBA. Here is my code :
Function PresentValue2()
Dim i As Double
i = 1
Dim coll As Collection
coll.add i
PresentValue2 = coll.Item(1)
End Function
I made a break point and the intepreter just stops at the instruction coll.add i and the function return the value "#VALUE!"
Why is that?
I add the same problem with a Dynamic array
A collection is an object. You need to instantiate it before using it. E.g. Dim col1 as Collection Set col1 = New Collection col1.add "item"
Considering that your function seems to want to "persist" the value and check it this will still cause issues because you are instantiating a new collection each time this method is called. You'll need to declare and instantiate the collection outside of your function and use it like this
Dim col1 as New Collection
Function PresentValue2 as Double
Dim i as Double
i = 1
col1.add i
'rest of your code here and return value
'....
End Function
Sub blah():Static foo:If foo is nothing then set foo = New Bar():...rest of sub...)You are missing a Set:
Function PresentValue2()
Dim i As Double
i = 1
Dim coll As Collection
Set coll = New Collection
coll.Add i
PresentValue2 = coll.Item(1)
End Function
