Chip,

I got the file from jlclyde. It had links to another file which, I suspect,
also had the same UDF...

When I typed the formula into any cell it was looking for that file. It
worked fine when I changed the function name.

Can you tell us why it was looking in the other file? I mean how UDF names
are resolved if their are formulas with the same name in the source file and
the linked file.

While typing the name of the formula I was getting two matches - fcarea and
FCarea...

Regards,
Sheeloo

"Chip Pearson" wrote:

What version of Excel are you using? Code in a UDF cannot change any
part of the Excel environment, and VBA considers the Find method to be
one of these things (probably because it is linked to the Replace
method). In any case, it is probably the Find method that is causing
the UDF to throw a #VALUE exception. This was fixed in, I think,
2003, but I'm not sure.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 16 Feb 2009 11:51:08 -0800 (PST), jlclyde
wrote:

I have two functions. One to split out each side of a measurement ....
36 1/8 X 40 17/32. This is in one cell. The functions are supposed
to take and find the square inches of the measurements. So in this
case FCArea would be 36.125 * 40.53125. It is finding the right cell
and all of the right information seems to be going to the right
places, then it returns a #value. Any help will be greatly
appreciated.

Thanks,
Jay

Function FCarea() As Double
Application.Volatile
Dim Fnd As String
Dim L As String, R As String, X As Integer
Fnd = Range("B:B").Find(what:="Final Size:").Offset(2, 0).Value
X = InStr(1, Fnd, "X", 1)
L = Left(Fnd, X - 1)
R = Right(Fnd, Len(Fnd) - X)
FCarea = Frac2Num(L) * Frac2Num(R)
End Function

Function Frac2Num(ByVal X As String) As Double
Dim P As Integer, N As Double, Num As Double, Den As Double
X = Trim$(X)
P = InStr(X, "/")

If P = 0 Then
N = Val(X)
Else
Den = Val(Mid$(X, P + 1))

If Den = 0 Then Error 11 ' Divide by zero
X = Trim$(Left$(X, P - 1))
P = InStr(X, " ")
If P = 0 Then
Num = Val(X)
Else
Num = Val(Mid$(X, P + 1))
N = Val(Left$(X, P - 1))
End If
End If
If Den < 0 Then
N = N + (Num / Den)
End If
Frac2Num = N
End Function