ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   UDF Function Not working (https://www.excelbanter.com/excel-discussion-misc-queries/221191-udf-function-not-working.html)

jlclyde
UDF Function Not working
 
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

Gary''s Student
UDF Function Not working
 
Without VBA:

=LEFT(A1,FIND("X",A1,1)-1)*MID(A1,FIND("X",A1,1)+2,256)

--
Gary''s Student - gsnu2007L


"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

Sheeloo[_3_]
UDF Function Not working
 
There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.


"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

jlclyde
UDF Function Not working
 
On Feb 16, 2:18*pm, Sheeloo <Click on my name above to get
instructions for getting my email id wrote:
There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.



"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- Hide quoted text -

- Show quoted text -

Sheelo,
I am glad that it working for you. I wish it was working for me. Any
thoughts?
Thanks,
Jay

Sheeloo[_3_]
UDF Function Not working
 
I also wish it works foy you.

I have sent my testfile to your email id.

How are you testing? Make sure there are no extra characters in the cell you
are testing it.

Where do you have the code? In a module?

"jlclyde" wrote:

On Feb 16, 2:18 pm, Sheeloo <Click on my name above to get
instructions for getting my email id wrote:
There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.



"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- Hide quoted text -

- Show quoted text -

Sheelo,
I am glad that it working for you. I wish it was working for me. Any
thoughts?
Thanks,
Jay

jlclyde
UDF Function Not working
 
On Feb 16, 3:31*pm, Sheeloo <Click on my name above to get
instructions for getting my email id wrote:
I also wish it works foy you.

I have sent my testfile to your email id.

How are you testing? Make sure there are no extra characters in the cell you
are testing it.

Where do you have the code? In a module?



"jlclyde" wrote:
On Feb 16, 2:18 pm, Sheeloo <Click on my name above to get
instructions for getting my email id wrote:
There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.

"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- Hide quoted text -

- Show quoted text -

Sheelo,
I am glad that it working for you. *I wish it was working for me. *Any
thoughts?
Thanks,
Jay- Hide quoted text -

- Show quoted text -

I got your file and sent you mine. For somereason all of my other
UDFs work fine, but this one is throwing a value. I have it in a
module with other functions. Your file opens and the UDF works fine.
I ahve no idea why it is no longer working on my file.

Thanks,
Jay

Chip Pearson
UDF Function Not working
 
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

Sheeloo[_3_]
UDF Function Not working
 
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


All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com