ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Redesign for hitting limit argument in a UDF (https://www.excelbanter.com/excel-programming/322474-redesign-hitting-limit-argument-udf.html)

Peter M
Redesign for hitting limit argument in a UDF
 
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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
Sorry, i should read more carefully as you specifically said you were using
the function in worksheets. I get back to you shortly.
Sebastienm

"sebastienm" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
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" wrote:

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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
Sorry. UBound(AM)=0, not 1.


"Peter M" wrote:

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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
Sorry, I still do not understand :o(

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


"sebastienm" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

sebastienm
Redesign for hitting limit argument in a UDF
 
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
Redesign for hitting limit argument in a UDF
 
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"
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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from
other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Tom Ogilvy
Redesign for hitting limit argument in a UDF
 
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

--
Regards,
Tom Ogilvy


"Peter M" wrote in message
...
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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Tom Ogilvy
Redesign for hitting limit argument in a UDF
 
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

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
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"
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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from
other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter T
Redesign for hitting limit argument in a UDF
 
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
Redesign for hitting limit argument in a UDF
 
No, it was meant for Peter M - I must have had the wrong message selected.

my apologies.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
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
Redesign for hitting limit argument in a UDF
 
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

--
Regards,
Tom Ogilvy




"Peter M" wrote in message
...
Sorry, I still do not understand :o(

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


"sebastienm" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only
from other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,

B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help

Peter M
Redesign for hitting limit argument in a UDF
 
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

"Peter T" wrote:

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"
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" wrote:

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" wrote:

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" wrote:

two questions:
- is this function to be used in a worksheet directly or only from
other vba
code?
- your paramters a 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

"Peter M" wrote:

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" wrote:

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" wrote:

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,B2 1,B22,B23,
B24,B25,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B3 6,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.

--
Thanks for any help


All times are GMT +1. The time now is 03:49 AM.

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