| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
| IF Function not working | Excel Worksheet Functions | |||
| IF function not working | Excel Worksheet Functions | |||
| RIGHT function not working | Excel Discussion (Misc queries) | |||
| Function F3 key not working | Excel Worksheet Functions | |||
Linear Mode
