Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
I have a problem passing arguments to a UDF. I believe it is a specific
problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: ... F G R S 14 [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF - Clarification
What the workbook looks like got tangled up in word-wrap. Try this:
[F14][G14 <Range R14][S14] "Bob Myers" wrote in message ... I have a problem passing arguments to a UDF. I believe it is a specific problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: ... F G R S 14 [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF - Clarification
Hi Bob,
It works every which way I can test it. Suggestion. Are you using X somewhere else like in another UDF? Try changing it to something like intX or something. -- Regards, OssieMac "Bob Myers" wrote: What the workbook looks like got tangled up in word-wrap. Try this: [F14][G14 <Range R14][S14] "Bob Myers" wrote in message ... I have a problem passing arguments to a UDF. I believe it is a specific problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: ... F G R S 14 [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
On Fri, 21 Nov 2008 23:42:45 -0500, "Bob Myers" wrote:
I have a problem passing arguments to a UDF. I believe it is a specific problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: ... F G R S 14 [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers Need more information. What is the formula in F14, and the precedents to that formula? How are you determining that the "value you get out of X) is zero? The only way I have been able to get the following to return a zero is if X<1. Even if X is a text string, (i.e. "1") I still return a one. ====================== Function Action(X As Integer, Rrrr As Range) As Integer MsgBox (X) End Function ================== --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
Ron, this is going to get messy -- but you asked, and thanks.
My previous rendition was simplified. The following accurately reflects the actual. F14 holds: '=IF(OR(nBank=0,A14="S"),"",COUNTIF(G14:OFFSET(F14 ,0,iWk3),"=x"))' Where nBank is set on another tab and is 12, and iWk3 is set on this tab and is 18. It returns the number of x's in the first 18 Cells of Range G14:AO14, which is 2. G14:AO14 holds: '|*|22|20|x|22|20|*|19|20|*|18|17|x|19|20|*|23|24| *|25|21|*|20|21|x|18|17|*|*|*|*|*|*|' Where * = Null (empty, blank) AQ14 holds: '=FBank(F14, G14:AO14)' UDF header: 'Function FBank(ub As Integer, rRange As Range)' With F14 in the function call, ub is always 0, which includes 'ByVal ub As Integer.' I've also tried 'ub As Variant,' and still ub = 0. If I set D14 to '=F14' and change FBank's call to FBank(D14, G14:AO14), ub = 0; but if I set D14 to 2, ub will be 2. If I change FBank's call to FBank(F14:AO14) and read the first cell, it will be 0. If I change FBank's call to FBank(ROW(), G14:AO14), the row comes thru as 14; but 'ub = Range("F14")' still comes out as 0; as does Range("Sheet!F14"). If I change the FBank call to FBank(2, G14:AO14), ub will be 2. I've tried as many work-arounds as I can think of to get the value of F14 into my UDF. Although it would be nice to know why I can't, I'd be be semi-happy just to get it there. R/Bob "Ron Rosenfeld" wrote in message ... On Fri, 21 Nov 2008 23:42:45 -0500, "Bob Myers" wrote: I have a problem passing arguments to a UDF. I believe it is a specific problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers Need more information. What is the formula in F14, and the precedents to that formula? How are you determining that the "value you get out of X) is zero? The only way I have been able to get the following to return a zero is if X<1. Even if X is a text string, (i.e. "1") I still return a one. ====================== Function Action(X As Integer, Rrrr As Range) As Integer MsgBox (X) End Function ================== --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF -- Work-around
The work-around:
Change the function call to FBank(INT(F14), G14:AO14) gets the correct value passed. Thanks for the help. I still don't know why it didn't work. R/Bob "Bob Myers" wrote in message ... Ron, this is going to get messy -- but you asked, and thanks. My previous rendition was simplified. The following accurately reflects the actual. F14 holds: '=IF(OR(nBank=0,A14="S"),"",COUNTIF(G14:OFFSET(F14 ,0,iWk3),"=x"))' Where nBank is set on another tab and is 12, and iWk3 is set on this tab and is 18. It returns the number of x's in the first 18 Cells of Range G14:AO14, which is 2. G14:AO14 holds: '|*|22|20|x|22|20|*|19|20|*|18|17|x|19|20|*|23|24| *|25|21|*|20|21|x|18|17|*|*|*|*|*|*|' Where * = Null (empty, blank) AQ14 holds: '=FBank(F14, G14:AO14)' UDF header: 'Function FBank(ub As Integer, rRange As Range)' With F14 in the function call, ub is always 0, which includes 'ByVal ub As Integer.' I've also tried 'ub As Variant,' and still ub = 0. If I set D14 to '=F14' and change FBank's call to FBank(D14, G14:AO14), ub = 0; but if I set D14 to 2, ub will be 2. If I change FBank's call to FBank(F14:AO14) and read the first cell, it will be 0. If I change FBank's call to FBank(ROW(), G14:AO14), the row comes thru as 14; but 'ub = Range("F14")' still comes out as 0; as does Range("Sheet!F14"). If I change the FBank call to FBank(2, G14:AO14), ub will be 2. I've tried as many work-arounds as I can think of to get the value of F14 into my UDF. Although it would be nice to know why I can't, I'd be be semi-happy just to get it there. R/Bob "Ron Rosenfeld" wrote in message ... On Fri, 21 Nov 2008 23:42:45 -0500, "Bob Myers" wrote: I have a problem passing arguments to a UDF. I believe it is a specific problem since I've passed a lot of arguments to a lot of other UDF's in the past. I'm working with Excel 2003 on Vista. The Workbook looks like: [F14] [G14 <Range R14] [S14] F14 has a formula that produces an Integer as a function of what's in the Range. G14:R14 is mostly integers, but with a few alphas and nulls. S14 has a formula containing the UDF Action(F14, G14:R14). The UDF takes the form: Function Action(X As Integer, Rrrr As Range) As Integer ... Stuff ... End Function My problem is the only value I can get out of X is 0 (zero), regardless of what's in F14. I've tried ByVal X and the default, ByRef X. Still 0. I've tried putting an Integer in B3 and passing B3 instead of F14. Still 0. Since I'm able to pick every cell out of Range Rrrr in the UDF, I've tried extending Rrrr to F14:R14 (changed the passed arguments accordingly). Regardless of what's in the first cell of the Rrrr, all I can still get is a 0. If I change the Call to Action(3, G14:R14), the 3 comes through for the value of X. Any ideas, suggestions? Respectfully submitted, Bob Myers Need more information. What is the formula in F14, and the precedents to that formula? How are you determining that the "value you get out of X) is zero? The only way I have been able to get the following to return a zero is if X<1. Even if X is a text string, (i.e. "1") I still return a one. ====================== Function Action(X As Integer, Rrrr As Range) As Integer MsgBox (X) End Function ================== --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
On Sat, 22 Nov 2008 14:38:56 -0500, "Bob Myers" wrote:
Ron, this is going to get messy -- but you asked, and thanks. My previous rendition was simplified. The following accurately reflects the actual. F14 holds: '=IF(OR(nBank=0,A14="S"),"",COUNTIF(G14:OFFSET(F1 4,0,iWk3),"=x"))' Where nBank is set on another tab and is 12, and iWk3 is set on this tab and is 18. It returns the number of x's in the first 18 Cells of Range G14:AO14, which is 2. G14:AO14 holds: '|*|22|20|x|22|20|*|19|20|*|18|17|x|19|20|*|23|24 |*|25|21|*|20|21|x|18|17|*|*|*|*|*|*|' Where * = Null (empty, blank) AQ14 holds: '=FBank(F14, G14:AO14)' UDF header: 'Function FBank(ub As Integer, rRange As Range)' With F14 in the function call, ub is always 0, which includes 'ByVal ub As Integer.' I've also tried 'ub As Variant,' and still ub = 0. If I set D14 to '=F14' and change FBank's call to FBank(D14, G14:AO14), ub = 0; but if I set D14 to 2, ub will be 2. If I change FBank's call to FBank(F14:AO14) and read the first cell, it will be 0. If I change FBank's call to FBank(ROW(), G14:AO14), the row comes thru as 14; but 'ub = Range("F14")' still comes out as 0; as does Range("Sheet!F14"). If I change the FBank call to FBank(2, G14:AO14), ub will be 2. I've tried as many work-arounds as I can think of to get the value of F14 into my UDF. Although it would be nice to know why I can't, I'd be be semi-happy just to get it there. Using your information to reproduce what you have done, I have no problem with the values you have used. Again, how are you determining that ub is not being properly passed? Since you did not indicate that, I used a very simple UDF: Function FBank(ub As Integer, rRange As Range) FBank = ub End Function As expected, the function returned a value of 2. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
Ron, using your function, I get your results -- which led to further
investigation. Function FBank(ub As Integer, rRange As Range) FBank = ub End Function What I found was that when I put a break point inside the function is when the ub value doesn't get passed. Was having a lot of trouble with the function's algorithm and every time I stopped the function in the debugger, the value of ub was 0. This led me to believe ub was why my algorithm wasn't working (wasted a lot of time). For some reason, the argument in the form of INT(F14) allowed me to see the value of ub in the debugger. After the algorithm was correct, and with your help, I removed the INT() from the call, and still get the correct results. I would guess it is an anomaly in the debugger. Thanks again. R/Bob "Ron Rosenfeld" wrote in message ... On Sat, 22 Nov 2008 14:38:56 -0500, "Bob Myers" wrote: Ron, this is going to get messy -- but you asked, and thanks. My previous rendition was simplified. The following accurately reflects the actual. F14 holds: '=IF(OR(nBank=0,A14="S"),"",COUNTIF(G14:OFFSET(F 14,0,iWk3),"=x"))' Where nBank is set on another tab and is 12, and iWk3 is set on this tab and is 18. It returns the number of x's in the first 18 Cells of Range G14:AO14, which is 2. G14:AO14 holds: '|*|22|20|x|22|20|*|19|20|*|18|17|x|19|20|*|23|2 4|*|25|21|*|20|21|x|18|17|*|*|*|*|*|*|' Where * = Null (empty, blank) AQ14 holds: '=FBank(F14, G14:AO14)' UDF header: 'Function FBank(ub As Integer, rRange As Range)' With F14 in the function call, ub is always 0, which includes 'ByVal ub As Integer.' I've also tried 'ub As Variant,' and still ub = 0. If I set D14 to '=F14' and change FBank's call to FBank(D14, G14:AO14), ub = 0; but if I set D14 to 2, ub will be 2. If I change FBank's call to FBank(F14:AO14) and read the first cell, it will be 0. If I change FBank's call to FBank(ROW(), G14:AO14), the row comes thru as 14; but 'ub = Range("F14")' still comes out as 0; as does Range("Sheet!F14"). If I change the FBank call to FBank(2, G14:AO14), ub will be 2. I've tried as many work-arounds as I can think of to get the value of F14 into my UDF. Although it would be nice to know why I can't, I'd be be semi-happy just to get it there. Using your information to reproduce what you have done, I have no problem with the values you have used. Again, how are you determining that ub is not being properly passed? Since you did not indicate that, I used a very simple UDF: Function FBank(ub As Integer, rRange As Range) FBank = ub End Function As expected, the function returned a value of 2. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
On Sat, 22 Nov 2008 19:03:55 -0500, "Bob Myers" wrote:
Ron, using your function, I get your results -- which led to further investigation. Function FBank(ub As Integer, rRange As Range) FBank = ub End Function What I found was that when I put a break point inside the function is when the ub value doesn't get passed. Was having a lot of trouble with the function's algorithm and every time I stopped the function in the debugger, the value of ub was 0. This led me to believe ub was why my algorithm wasn't working (wasted a lot of time). For some reason, the argument in the form of INT(F14) allowed me to see the value of ub in the debugger. After the algorithm was correct, and with your help, I removed the INT() from the call, and still get the correct results. I would guess it is an anomaly in the debugger. Thanks again. R/Bob Well, glad you got your function working properly. If there is a problem in the debugger, though, it would be good to document it so that it can be properly reported. For example, using my code, even if I put a break point at the first line (the Function statement), ub remains with a value of 2. That leads me to wonder if there is truly a problem with the debugger, or if there is a problem with either your code, or the way you are using the debugger. You do realize that if iwk3 is empty, ub will initially have a value of zero, and Excel will show a circular reference error (unless you have that suppressed) after a few iterations. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Passed Arguments to a UDF
I don't think this is a bug.
Sounds to me like F14 has not yet been calculated and is therefore Empty when the function FBank is first called. Converting Empty to Integer gives zero. The function will eventually be called again when F14 is recalculated. This can easily happen if the initial calculation chain is out-of-sequence: I recommend checking all UDF input parameters for this condition. see http://www.DecisionModels.com/Calcsecretsj.htm for more details. Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com I recommend "Bob Myers" wrote in message ... Ron, using your function, I get your results -- which led to further investigation. Function FBank(ub As Integer, rRange As Range) FBank = ub End Function What I found was that when I put a break point inside the function is when the ub value doesn't get passed. Was having a lot of trouble with the function's algorithm and every time I stopped the function in the debugger, the value of ub was 0. This led me to believe ub was why my algorithm wasn't working (wasted a lot of time). For some reason, the argument in the form of INT(F14) allowed me to see the value of ub in the debugger. After the algorithm was correct, and with your help, I removed the INT() from the call, and still get the correct results. I would guess it is an anomaly in the debugger. Thanks again. R/Bob "Ron Rosenfeld" wrote in message ... On Sat, 22 Nov 2008 14:38:56 -0500, "Bob Myers" wrote: Ron, this is going to get messy -- but you asked, and thanks. My previous rendition was simplified. The following accurately reflects the actual. F14 holds: '=IF(OR(nBank=0,A14="S"),"",COUNTIF(G14:OFFSET( F14,0,iWk3),"=x"))' Where nBank is set on another tab and is 12, and iWk3 is set on this tab and is 18. It returns the number of x's in the first 18 Cells of Range G14:AO14, which is 2. G14:AO14 holds: '|*|22|20|x|22|20|*|19|20|*|18|17|x|19|20|*|23| 24|*|25|21|*|20|21|x|18|17|*|*|*|*|*|*|' Where * = Null (empty, blank) AQ14 holds: '=FBank(F14, G14:AO14)' UDF header: 'Function FBank(ub As Integer, rRange As Range)' With F14 in the function call, ub is always 0, which includes 'ByVal ub As Integer.' I've also tried 'ub As Variant,' and still ub = 0. If I set D14 to '=F14' and change FBank's call to FBank(D14, G14:AO14), ub = 0; but if I set D14 to 2, ub will be 2. If I change FBank's call to FBank(F14:AO14) and read the first cell, it will be 0. If I change FBank's call to FBank(ROW(), G14:AO14), the row comes thru as 14; but 'ub = Range("F14")' still comes out as 0; as does Range("Sheet!F14"). If I change the FBank call to FBank(2, G14:AO14), ub will be 2. I've tried as many work-arounds as I can think of to get the value of F14 into my UDF. Although it would be nice to know why I can't, I'd be be semi-happy just to get it there. Using your information to reproduce what you have done, I have no problem with the values you have used. Again, how are you determining that ub is not being properly passed? Since you did not indicate that, I used a very simple UDF: Function FBank(ub As Integer, rRange As Range) FBank = ub End Function As expected, the function returned a value of 2. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mark date that has passed | Excel Worksheet Functions | |||
how do I fix a forecast date that has passed for a zero value? | Excel Discussion (Misc queries) | |||
IF Formula to calculate whether date has passed | Excel Discussion (Misc queries) | |||
Date Passed Notification | Excel Discussion (Misc queries) | |||
how do i work out how many hrs have passed in a 24 hr clock, in ex | Excel Worksheet Functions |