Redesign for hitting limit argument in a UDF

[Peter M]

I currently have the following, which of course breaks down once you hit the
limit of 29 arguments in a user defined function:

In the Worksheet:
=Newton(B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,
B39,B38)

In VB:
Function Newton(P, n, F, A1, m1, A2, m2, A3, m3, A4,m4, A5,m5,A6, m6,
A7, m7, A8, m8, A9, m9, A10,m10, A11, m11, A12, m12, A13, m13, iannual)

To redesign, I have:

In the Worksheet:
=Newton(B11:B39,B8)

In VB:
Function Newton(.......?)
??

Any help here to make this work is appreciated. I can't seem to ge this to
work.

 

[sebastienm]

Hi Peter,
try something as follow:
Pass and return Variants: it enables you to pass an Excel range, an excel
array, a single value. It also enables you to return a worksheet error if
necessary. Manage the different scenario of parameter type within the
function. Also this way, the function should be re-evaluated once a dependant
range is the sheet is modified.

'-------------------------------------
Function Newton(Var1 As Variant, Var2 As Variant) as Variant
Dim v2 As Double

On Error GoTo Error_Handler
v2 = CDbl(Var2) '<-- if fails then not number --> error_handler

If TypeOf Var1 Is Excel.Range Then 'case var2 is excel range
' eg: =newton(A1:A10,10)
'code here
' newton=
ElseIf TypeName(Var1) = "Variant()" Then 'case var2 is array
' eg: =newton({1,2,3},3)
'code here
'newton=
Else 'try to convert into double
'eg =newton(2,4)
'code here
'newton=
End If

Exit Function

Error_Handler:
Newton = CVErr(xlErrValue)
End Function
'---------------------------------------

Regards,
Sebastien

 

[Peter M]

Hi Sebestian-
Thanks for your help! Is there an easier way to do this though? I am not
very versed in VB. I do know Fortran programming, and the way I had the
function set up originally (until I hit the limit of 29 arguments) worked
fine and I understood the logic.
For example, all of the variables I had defined in the Function statement
oroginally (i.e. P, n, F, A1, m1, etc) are used in the Function itself. How
do I know define these variables in the new Function?

Thanks,
Peter

 

[sebastienm]

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters are: P, n, F, iAnnual and a list of pairs A1, m1,... Ax,
mx. Am i right? In such a case, assuming it is NOT a function to be used
through a worksheet:, and assuming parameters are 'reals' values.
Function Newton(P as double, n as double, F as double, iAnnual as
Double, _
ParamArray AM( ) ) as Double

End Function

<from online help>"
ParamArray: Used only as the last argument in arglist to indicate that the
final argument is an Optional array of Variant elements. The ParamArray
keyword allows you to provide an arbitrary number of arguments. It may not be
used with ByVal, ByRef, or Optional.

I hope this helps.
Sebastienm

 

[sebastienm]

Sorry, i should read more carefully as you specifically said you were using
the function in worksheets. I get back to you shortly.
Sebastienm

 

[Peter M]

Hi Sebastian-

Just to clarify:
#1 used only in a worksheet
#2 yess. these are all the parameters and they are all real (not integer)and
they are used in equations in the function, so I need to somehow associate
them with the array being passed into the function

Thanks

 

[sebastienm]

1. For worksheet functions, it is better to use Variant type for parameters
(the user could enter 1 directly, but could also enter A1 which contains 1).
Also if a reference to a range of cell is used, if one of these cells change,
the function is therefore rec omputed recomputed.

2. Use ParamArray as last parameter. It enables you to have any number of
parameters. Again, <from online help>": ParamArray: Used only as the last
argument in arglist to indicate that the final argument is an Optional array
of Variant elements. The ParamArray keyword allows you to provide an
arbitrary number of arguments. It may not be used with ByVal, ByRef, or
Optional.
To check if the user uses the paramArray use the function
IsMissing(my_Param_array)

3. When converting parameters to Double data type, be careful that, if
parameter is A1, the value in A1 is not an error eg #VALUE which would be
converted without a problem to the error value (in this case 2015) and would
not generate an error in further computations.

Below is an example. Ii first check that p,n,f,iAnnual
are/can_be_converted_to Double data type. If the user enters A1 for p, the
conversion is implicitely made on the value of range A1.
Then, the most unconvient is the ParamArray (type array of variant).
Depending if the user entered a list of numbers (paramarray is multi-elements
array) or a range (paramarray a single element array of type Range), ... you
have to check and process the ParamArray differently. It could also be
entered as an array of values ( {1,2,3,4} ) but i would personnaly just take
care of the 2 above cases: range of cells or list of numbers.

Function Newton(P As Variant, n As Variant, F As Variant, _
iAnnual As Variant, ParamArray AM()) As Variant

Dim vP As Double, vn As Double, vF As Double, viAnnual As Double
Dim upperB As Long

On Error GoTo Error_Handler

'converts values to Doubles.
'This makes sure no wrong type is passed from worksheet
vP = CDbl(P): vn = CDbl(n): vF = CDbl(F): viAnnual = CDbl(iAnnual)
'could also check if these values are worksheet errors
'in which case the above conversion to double would have worked
If VarType(P) = vbError Then GoTo Error_Handler
If VarType(n) = vbError Then GoTo Error_Handler
If VarType(F) = vbError Then GoTo Error_Handler
If VarType(iAnnual) = vbError Then GoTo Error_Handler

'check ParamArray AM
If IsMissing(AM) Then GoTo Error_Handler 'Case no entry for AM
upperB = UBound(AM) + 1 'array start at index 0

If upperB = 1 Then '1 single element in ParamArray
If TypeOf AM(0) Is Excel.Range Then 'case the element is an Excel range
'eg: =newton(p,n,f,annual,A1:B10)
ElseIf <condition> Then 'other conditions
'could also be: =newton(p,n,f,annual,{1,2,3,4})
Else ' you get the idea
End If
Else 'a series of elements
'each element could be one of the above single elements
' or =newton(p,n,f,annual, 1,2,3,4)
' or a mix =newton(p,n,f,annual, 1, A1:B1, 2, 3 , A5)
'...
End If

Exit Function
Error_Handler:
Newton = CVErr(xlErrValue)
End Function

Does it answer your question?
Regards,
Sebastien

 

[Peter M]

Hi Sebastian-

This is what I tried, and still have a slight problem.

In Worksheet:
=Newton(B11,B12,B13,B8,B14:B39)

In VB:
Function Newton(P, n, F, iannual, ParamArray AM())

In the Function, I have verified P, n F, iannual and AM(0).
That is, in the Function, I have checked that P=B11, n=B12, F=B13,
iannual=B8 and AM(0)=B14. However, Ubound(AM)=1, so AM is not picking up B15
thru B39.

Thanks,
Peter

 

[sebastienm]

When entering a range like B14:B39 as parameter, B14:B39 goes entirely into
AM(0) and not 1st cell into AM(0), 2nd into AM(1). Don't forget that still
parameters are separated by comas and it still applies for each component of
a ParamArray... Try:
Function Newton2(P, n, F, iannual, ParamArray AM())
MsgBox UBound(AM) & ": " & AM(0).Address
End Function
with =Newton2(1,2,3,4,B14:B39) you'll see that AM(0) contains the whole
range B14:B39.
This is what is was trying to explain by separating each case in my previous
post. However, today i have hard time writing in English, i can barely
understand myself

Sebastienm

 

[Peter M]

Sorry. UBound(AM)=0, not 1.

Hi Sebastian-

This is what I tried, and still have a slight problem.

In Worksheet:
=Newton(B11,B12,B13,B8,B14:B39)

In VB:
Function Newton(P, n, F, iannual, ParamArray AM())

In the Function, I have verified P, n F, iannual and AM(0).
That is, in the Function, I have checked that P=B11, n=B12, F=B13,
iannual=B8 and AM(0)=B14. However, Ubound(AM)=1, so AM is not picking up B15
thru B39.

Thanks,
Peter

 

[Peter M]

Sorry, I still do not understand (

What do I do if I want to have B14 go into AM(0), B15 into AM(1), etc?

 

[sebastienm]

B14 go into AM(0), B15 into AM(1)...
You would have to enter it this way :
=Newton( 1 , 2 , 3 , 4 , B14 , B15 , B16 ,... B39 )
More painful for the user though. You could however implement both into the
code of your macro... so the user could choose how to enter it.
But in the end, working on 1 single range B14:B39 is very similar to working
with an array... they have the same shape ... you loop through cells instead
of looping through elements (and use the same Statement FOR NEXT)

One thing to consider, if youo really want to work on a array, not a range:
in the case ubound(am)=0 and Am(0) is Range
Dim v( )
v=Am(0)
---> v is now an array , each element contains the value of the range
am(0)

I hope this helps... if not, i could maybe write a very small function that
would show what i mean. I have such a hard time structuring my explanations
today...

Sebastienm

 

[Peter T]

Hi Peter,

If you enter a single (even multicell range) as the argument for AM(),
Ubound(AM) will be 0, ie one element (base 0 array). UM(0) is a holder for
the cell range.

Try AM(0)(3,1) to get cell(3,1) of the range.

But I don't think you need to be concerned with parameter arrays. Just enter
as a normal range.

If you enter more than one cell as a reference it returns a two dimensional
array of values, even if it's only in one column.

Say Arg is a single column of cells, in your function get individual values
like this

x = Arg(1,1), y = Arg(2,1) etc

As you are entering a large number of cells as a single argument, your
function may process faster if you pass to a variant array:

Dim vArr
vArr = Arg.value

x = vArr(1,1), y = vArr(2,1) etc

(don't need x & y)

I understand the workaround you are trying to accomplish to overcome the
arguments limit. One problem in usage you may encounter is if the integrity
of the block of cells gets changed, eg inserted rows, cells moved "out" of
the block etc.

Two approaches:
- A larger number of arguments with blocks of cells you know are not going
to get disturbed (moved as a block is OK).
- Named cells, most likely Worksheet level names (prefixed with
Sheetname! ). If many are always going to be the same cells (even moved) you
can hardcode the named ranges into your function and forget about them. Or
could ReferTo all the single named cells with one name ("BigName") and
simply enter BigName as an argument.

Regards,
Peter T


"Peter M"

 

[Tom Ogilvy]

Your wasing you time with paramarrays. If you want to check values, you can
add code to do that, but this is the basic approach you need.


Public function Newton(rng as Range,rng1 as Range)
if rng.count <> 29 or rng1.count <> 1 then
Newton = cvErr(xlErrRef)
exit function
End if
P = rng(1)
n = rng(2)
F = rng(3)
A1 = rng(4)
m1 = rng(5)
A2 = rng(6)
m2 = rng(7)
A3 = rng(8)
m3 = rng(9)
A4 = rng(10)
m4 = rng(11)
A5 = rng(12)
m5 = rng(13)
A6 = rng(14)
m6 = rng(15)
A7 = rng(16)
m7 = rng(17)
A8 = rng(18)
m8 = rng(19)
A9 = rng(20)
m9 = rng(21)
A10 = rng(22)
m10 = rng(23)
A11 = rng(24)
m11 = rng(25)
A12 = rng(26)
m12 = rng(27)
A13 = rng(28)
m13 = rng(29)
iannual = rng1
' existing code

End function

 

[Tom Ogilvy]

Your wasing you time with paramarrays. If you want to check values, you can
add code to do that, but this is the basic approach you need.


Public function Newton(rng as Range,rng1 as Range)
if rng.count <> 29 or rng1.count <> 1 then
Newton = cvErr(xlErrRef)
exit function
End if
P = rng(1)
n = rng(2)
F = rng(3)
A1 = rng(4)
m1 = rng(5)
A2 = rng(6)
m2 = rng(7)
A3 = rng(8)
m3 = rng(9)
A4 = rng(10)
m4 = rng(11)
A5 = rng(12)
m5 = rng(13)
A6 = rng(14)
m6 = rng(15)
A7 = rng(16)
m7 = rng(17)
A8 = rng(18)
m8 = rng(19)
A9 = rng(20)
m9 = rng(21)
A10 = rng(22)
m10 = rng(23)
A11 = rng(24)
m11 = rng(25)
A12 = rng(26)
m12 = rng(27)
A13 = rng(28)
m13 = rng(29)
iannual = rng1
' existing code

End function

 

[Peter T]

Hi Tom,

Your wasing you time with paramarrays.

If that's in reply to me that's more or less what I said to Peter M,

But I [Peter T] don't think you [Peter M] need to be
concerned with parameter arrays.

Regards,
Peter T

 

[Tom Ogilvy]

No, it was meant for Peter M - I must have had the wrong message selected.

my apologies.

--
Regards,
Tom Ogilvy

Hi Tom,

Your wasing you time with paramarrays.

If that's in reply to me that's more or less what I said to Peter M,

But I [Peter T] don't think you [Peter M] need to be
concerned with parameter arrays.

Regards,
Peter T

 

[Tom Ogilvy]

Your wasing you time with paramarrays. If you want to check values, you can
add code to do that, but this is the basic approach you need.


Public function Newton(rng as Range,rng1 as Range)
if rng.count <> 29 or rng1.count <> 1 then
Newton = cvErr(xlErrRef)
exit function
End if
P = rng(1)
n = rng(2)
F = rng(3)
A1 = rng(4)
m1 = rng(5)
A2 = rng(6)
m2 = rng(7)
A3 = rng(8)
m3 = rng(9)
A4 = rng(10)
m4 = rng(11)
A5 = rng(12)
m5 = rng(13)
A6 = rng(14)
m6 = rng(15)
A7 = rng(16)
m7 = rng(17)
A8 = rng(18)
m8 = rng(19)
A9 = rng(20)
m9 = rng(21)
A10 = rng(22)
m10 = rng(23)
A11 = rng(24)
m11 = rng(25)
A12 = rng(26)
m12 = rng(27)
A13 = rng(28)
m13 = rng(29)
iannual = rng1
' existing code

End function

 

[Peter M]

Hi Peter T-

Thanks. I used your aprroach below, and it accomplished what I wanted. I.e.,

Arg(1,1)=x
Arg(2,1)=y
etc

Peter M