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,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.
>
> --
> Thanks for any help

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.

--
Thanks for any help

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,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.
> >
> > --
> > Thanks for any help

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

"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,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.
> > >
> > > --
> > > Thanks for any help

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 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
>
> "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,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.
> > > >
> > > > --
> > > > Thanks for any help

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 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
>
> "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,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.
> > > >
> > > > --
> > > > Thanks for any help

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 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
> >
> > "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,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.
> > > > >
> > > > > --
> > > > > Thanks for any help

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 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
> > >
> > > "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,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.
> > > > > >
> > > > > > --
> > > > > > Thanks for any help

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 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
> > > >
> > > > "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,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.
> > > > > > >
> > > > > > > --
> > > > > > > Thanks for any help

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 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
> > > > >
> > > > > "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,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.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Thanks for any help

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 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
> > > >
> > > > "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,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.
> > > > > > >
> > > > > > > --
> > > > > > > Thanks for any help

>>> 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

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 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
> > > >
> > > > "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,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.
> > > > > > >
> > > > > > > --
> > > > > > > Thanks for any help

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> --
> Thanks for any help

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
news:[email protected]...
> 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 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
> > > > >
> > > > > "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,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.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Thanks for any help
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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
> > > > > >
> > > > > > "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,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.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Thanks for any help

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
news:[email protected]...
> 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
>
>

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 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
> > > > >
> > > > > "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,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.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Thanks for any help
>
>
>