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