Hi Peter T-

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

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

Peter M

"Peter T" wrote:

Hi Peter,

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

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

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

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

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

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

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

Dim vArr
vArr = Arg.value

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

(don't need x & y)

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

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

Regards,
Peter T


"Peter M"
Hi Sebastian-

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

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

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

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

Thanks,
Peter


"sebastienm" wrote:

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

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

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

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

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

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

On Error GoTo Error_Handler

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

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

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

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

Does it answer your question?
Regards,
Sebastien

"Peter M" wrote:

Hi Sebastian-

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

Thanks

"sebastienm" wrote:

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

End Function

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

I hope this helps.
Sebastienm

"Peter M" wrote:

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

Thanks,
Peter


"sebastienm" wrote:

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

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

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

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

Exit Function

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

Regards,
Sebastien

"Peter M" wrote:

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

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

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

To redesign, I have:

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

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

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

--
Thanks for any help