UDF returns #value in XL97
 

It seems to be a problem with conversion:

Works in XL 97
Function GetStockQuote(Stock As String, _
Optional sWhatType As String, Optional sWhatDate As String) As Variant
sValue = rs.Fields(5).Value
End function


Returns #Value in XL 97
Function GetStockQuote(Stock As String, _
Optional sWhatType As String, Optional sWhatDate As String) As Variant
sValue = CDec(rs.Fields(5).Value)
End function

Why is that?

Clark



"Clark B" wrote in message
...
Hi there! I have problems with my UDF in XL97. In XL XP it works just fine
I have values stored in a recordset (not shown in code since it has low
relevance).
The name of the recordset is "rs". I filter the recordset and can match
the
correct
value to a given cell using application.caller.

To the problem: When I step through code I can se that the sValue has a
value on the second last row of the code.
But in cell I get just #Value. What could be wrong here? Corrupt metadata?

Best Regards

Clarkie

Function GetStockQuote(Stock As String, _
Optional sWhatType As String, Optional sWhatDate As String) As Variant

Dim sFilter as String
Dim sValue as Variant

sFilter = "Cell='" & Application.Caller.Address & "' AND Sheet='" &
Application.Caller.Parent.Name & _
"' AND WorkBook='" & Application.Caller.Parent.Parent.Name &
"'"

rs.Filter = sFilter
If rs.EOF = True Then
sValue = "Value not updated/found"
Else
If rs.Fields(5).Value < "Missing" Then
sValue = CDec(rs.Fields(5).Value)
Else
sValue = rs.Fields(5).Value
End If
End If

Msgbox sValue ' I do get a messagebox with a correct value here!
GetStockQuote = sValue

End Function