Passed Arguments to a UDF

[Bob Myers]

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

 

[Bob Myers]

What the workbook looks like got tangled up in word-wrap. Try this:

[F14][G14 <Range>
R14][S14]

 

[OssieMac]

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

What the workbook looks like got tangled up in word-wrap. Try this:

[F14][G14 <Range>
R14][S14]

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

 

[Ron Rosenfeld]

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

 

[Bob Myers]

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

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

 

[Bob Myers]

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

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

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

 

[Ron Rosenfeld]

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

 

[Bob Myers]

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]

Ron, using your function, I get your results -- which led to further
investigation.


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

 

[Charles Williams]

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