Argument limit on user Function?

Yup but I do not remember what it is. Something like 40. You can pass arrays
and user defined types though if you get into trouble.

"Peter M" wrote:

> Is there a limit on the number of arguments you can have in a user defined
> Function in Excel?
> --
> Thanks for any help

Is there a limit on the number of arguments you can have in a user defined
Function in Excel?
--
Thanks for any help

Thanks. It's 30, because that's what I'm hitting. The error message isn't
very gooog though; it just says you are trying to pass too many arguments for
the function, instead of you've hit the limit. Anyway, I know about arrays,
but what are user defined types?

"Jim Thomlinson" wrote:

> Yup but I do not remember what it is. Something like 40. You can pass arrays
> and user defined types though if you get into trouble.
>
> "Peter M" wrote:
>
> > Is there a limit on the number of arguments you can have in a user defined
> > Function in Excel?
> > --
> > Thanks for any help

>it just says you are trying to pass too many arguments for
>the function, instead of you've hit the limit.

There's a difference there?

Look up the TYPE statement in Help.

On Wed, 2 Feb 2005 17:55:07 -0800, "Peter M"
<PeterM@discussions.microsoft.com> wrote:

>Thanks. It's 30, because that's what I'm hitting. The error message isn't
>very gooog though; it just says you are trying to pass too many arguments for
>the function, instead of you've hit the limit. Anyway, I know about arrays,
>but what are user defined types?
>
>"Jim Thomlinson" wrote:
>
>> Yup but I do not remember what it is. Something like 40. You can pass
arrays
>> and user defined types though if you get into trouble.
>>
>> "Peter M" wrote:
>>
>> > Is there a limit on the number of arguments you can have in a user
defined
>> > Function in Excel?
>> > --
>> > Thanks for any help

Depends on how you look at it. It's true that the current error message,
stictly speaking, does cover hitting the argument number limit. But if you
are not aware of the limit to begin with, as in my case, you waste a lot of
time counting arguments, etc, trying to figure out what's wrong, when a
simple message saying you've hit the limit would have been infinitely more
helpful. Luckily I have enough previous programming experience that it
occured to me that there may be a limit.

"Myrna Larson" wrote:

> >it just says you are trying to pass too many arguments for
> >the function, instead of you've hit the limit.
>
> There's a difference there?
>

Thanks. I'll look it up.


"Myrna Larson" wrote:

> Look up the TYPE statement in Help.
>
> On Wed, 2 Feb 2005 17:55:07 -0800, "Peter M"
> <PeterM@discussions.microsoft.com> wrote:
>
> >Thanks. It's 30, because that's what I'm hitting. The error message isn't
> >very gooog though; it just says you are trying to pass too many arguments for
> >the function, instead of you've hit the limit. Anyway, I know about arrays,
> >but what are user defined types?
> >
> >"Jim Thomlinson" wrote:
> >
> >> Yup but I do not remember what it is. Something like 40. You can pass
> arrays
> >> and user defined types though if you get into trouble.
> >>
> >> "Peter M" wrote:
> >>
> >> > Is there a limit on the number of arguments you can have in a user
> defined
> >> > Function in Excel?
> >> > --
> >> > Thanks for any help
>
>

Peter,
IMO if you are passing 30 arguments, you should look at your design again.
If it really require these 30 different pieces of information to return a
single answer, you may be trying to do too much in one function.
If you are passing 30 cell values to calculate say an average, just pass the
Range concerned.

NickHK

"Peter M" <PeterM@discussions.microsoft.com> wrote in message
news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> Thanks. It's 30, because that's what I'm hitting. The error message
isn't
> very gooog though; it just says you are trying to pass too many arguments
for
> the function, instead of you've hit the limit. Anyway, I know about
arrays,
> but what are user defined types?
>
> "Jim Thomlinson" wrote:
>
> > Yup but I do not remember what it is. Something like 40. You can pass
arrays
> > and user defined types though if you get into trouble.
> >
> > "Peter M" wrote:
> >
> > > Is there a limit on the number of arguments you can have in a user
defined
> > > Function in Excel?
> > > --
> > > Thanks for any help

That seems unnecessarily trite. If you know you have too many, rather than
try to get to the number one less than the max, you look at a re-design.
Pushing to the boundaries is rarely a good idea.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter M" <PeterM@discussions.microsoft.com> wrote in message
news:EAEA360A-370B-4259-B8DF-C50FCBCE233C@microsoft.com...
> Depends on how you look at it. It's true that the current error message,
> stictly speaking, does cover hitting the argument number limit. But if you
> are not aware of the limit to begin with, as in my case, you waste a lot
of
> time counting arguments, etc, trying to figure out what's wrong, when a
> simple message saying you've hit the limit would have been infinitely more
> helpful. Luckily I have enough previous programming experience that it
> occured to me that there may be a limit.
>
> "Myrna Larson" wrote:
>
> > >it just says you are trying to pass too many arguments for
> > >the function, instead of you've hit the limit.
> >
> > There's a difference there?
> >

Tom,

the limit of the paramarray itself 29

But for discussion's sake..
(i cant imagine writing a usable function with so many arguments)

I can get following to compile in xl97,xlXP & xl2003.
in fact xl2003 allows 1 extra argument before the paramarray

Note the numbering is 0 based <g>


Function ArgListig1( _
arg00$, arg01$, arg02$, arg03$, arg04$, _
arg05$, arg06$, arg07$, arg08$, arg09$, _
arg10$, arg11$, arg12$, arg13$, arg14$, _
arg15$, arg16$, arg17$, arg18$, arg19$, _
arg20$, arg21$, arg22$, arg23$, arg24$, _
arg25$, arg26$, arg27$, arg28$, arg29$, _
arg30$, arg31$, arg32$, arg33$, arg34$, _
arg35$, arg36$, arg37$, arg38$, arg39$, _
arg40$, arg41$, arg42$, arg43$, arg44$, _
arg45$, arg46$, arg47$, arg48$, arg49$, _
arg50$, arg51$, arg52$, arg53$, arg54$, _
arg55$, arg56$, arg57$, ParamArray arg58())

End Function







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

> Technically, the limit is 29 - you are getting the error on 30, Yes?

Thanks for sharing that - useful information.

However, the topic was UDF. If I put your function in xl2002 (I can't get
the extra argument in the declaration in xl2002 - just for info) and put in
a formula

=ArgListig1(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24
,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,
50,51,52,53,54,55,56,57,58,59,60)

in the worksheet, it tells me there is an error in the formula and
highlights 29 (the 30th argument, note it is zero based<g>). So for a UDF,
with just straight arguments, I believe it is 29.

There actually was someone complaining about this limitation about 6 months
ago and of course that is the concern of the OP.

--
Regards,
Tom Ogilvy


"keepITcool" <xrrcvgpbby@puryyb.ay> wrote in message
news:xn0dy2g2woocuac00gkeepitcoolnl@msnews.microsoft.com...
>
> Tom,
>
> the limit of the paramarray itself 29
>
> But for discussion's sake..
> (i cant imagine writing a usable function with so many arguments)
>
> I can get following to compile in xl97,xlXP & xl2003.
> in fact xl2003 allows 1 extra argument before the paramarray
>
> Note the numbering is 0 based <g>
>
>
> Function ArgListig1( _
> arg00$, arg01$, arg02$, arg03$, arg04$, _
> arg05$, arg06$, arg07$, arg08$, arg09$, _
> arg10$, arg11$, arg12$, arg13$, arg14$, _
> arg15$, arg16$, arg17$, arg18$, arg19$, _
> arg20$, arg21$, arg22$, arg23$, arg24$, _
> arg25$, arg26$, arg27$, arg28$, arg29$, _
> arg30$, arg31$, arg32$, arg33$, arg34$, _
> arg35$, arg36$, arg37$, arg38$, arg39$, _
> arg40$, arg41$, arg42$, arg43$, arg44$, _
> arg45$, arg46$, arg47$, arg48$, arg49$, _
> arg50$, arg51$, arg52$, arg53$, arg54$, _
> arg55$, arg56$, arg57$, ParamArray arg58())
>
> End Function
>
>
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Tom Ogilvy wrote :
>
> > Technically, the limit is 29 - you are getting the error on 30, Yes?

Technically, the limit is 29 - you are getting the error on 30, Yes?

--
Regards,
Tom Ogilvy

"Peter M" <PeterM@discussions.microsoft.com> wrote in message
news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> Thanks. It's 30, because that's what I'm hitting. The error message
isn't
> very gooog though; it just says you are trying to pass too many arguments
for
> the function, instead of you've hit the limit. Anyway, I know about
arrays,
> but what are user defined types?
>
> "Jim Thomlinson" wrote:
>
> > Yup but I do not remember what it is. Something like 40. You can pass
arrays
> > and user defined types though if you get into trouble.
> >
> > "Peter M" wrote:
> >
> > > Is there a limit on the number of arguments you can have in a user
defined
> > > Function in Excel?
> > > --
> > > Thanks for any help

That's the whole point though. I didn't know I had too many specifically
because I had hit the limit. Therefore, when the error message said I had
too many, I was looking elsewhere for the problem (eg, do the number of
arguments being passed match the number of arguments in the function, etc).
Eventually when I checked everything out, I came to the conclusion I had
probably hit a limit, but in a roundabout way. I appreciate your comments,
but it's really neither here nor there at this point. I was simply stating
an opinion that a more definitive error message when you hit the limit would
be useful.

"Bob Phillips" wrote:

> That seems unnecessarily trite. If you know you have too many, rather than
> try to get to the number one less than the max, you look at a re-design.
> Pushing to the boundaries is rarely a good idea.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> news:EAEA360A-370B-4259-B8DF-C50FCBCE233C@microsoft.com...
> > Depends on how you look at it. It's true that the current error message,
> > stictly speaking, does cover hitting the argument number limit. But if you
> > are not aware of the limit to begin with, as in my case, you waste a lot
> of
> > time counting arguments, etc, trying to figure out what's wrong, when a
> > simple message saying you've hit the limit would have been infinitely more
> > helpful. Luckily I have enough previous programming experience that it
> > occured to me that there may be a limit.
> >
> > "Myrna Larson" wrote:
> >
> > > >it just says you are trying to pass too many arguments for
> > > >the function, instead of you've hit the limit.
> > >
> > > There's a difference there?
> > >
>
>
>

Nick-

Thanks for your reply. Perhaps so. My programming experience is Fortran
(which obviously dates me), and in Fortran it is not uncommon to have a long
argument list. I would be happy to redesign if I knew how. What I am doing
is passing values in cells, say B11:B39 & B8 into a function I wrote in VB
which performs Newton-Raphson iteration and returns a single value, which is
the result of the iteration. Up to now the number of arguments I passed was
under the limit of 29. I need to be able to pass more than 29 though. How
can I pass these arguments, using the example I give above, pass B11:B39 &
B8. Also, what do I need to do to the function statement (if anything) to
receive arguments passed in an array form.

Thanks,

Peter

"NickHK" wrote:

> Peter,
> IMO if you are passing 30 arguments, you should look at your design again.
> If it really require these 30 different pieces of information to return a
> single answer, you may be trying to do too much in one function.
> If you are passing 30 cell values to calculate say an average, just pass the
> Range concerned.
>
> NickHK
>
> "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > Thanks. It's 30, because that's what I'm hitting. The error message
> isn't
> > very gooog though; it just says you are trying to pass too many arguments
> for
> > the function, instead of you've hit the limit. Anyway, I know about
> arrays,
> > but what are user defined types?
> >
> > "Jim Thomlinson" wrote:
> >
> > > Yup but I do not remember what it is. Something like 40. You can pass
> arrays
> > > and user defined types though if you get into trouble.
> > >
> > > "Peter M" wrote:
> > >
> > > > Is there a limit on the number of arguments you can have in a user
> defined
> > > > Function in Excel?
> > > > --
> > > > Thanks for any help
>
>
>

yes, that is correct. i am getting the error on 30

"Tom Ogilvy" wrote:

> Technically, the limit is 29 - you are getting the error on 30, Yes?
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > Thanks. It's 30, because that's what I'm hitting. The error message
> isn't
> > very gooog though; it just says you are trying to pass too many arguments
> for
> > the function, instead of you've hit the limit. Anyway, I know about
> arrays,
> > but what are user defined types?
> >
> > "Jim Thomlinson" wrote:
> >
> > > Yup but I do not remember what it is. Something like 40. You can pass
> arrays
> > > and user defined types though if you get into trouble.
> > >
> > > "Peter M" wrote:
> > >
> > > > Is there a limit on the number of arguments you can have in a user
> defined
> > > > Function in Excel?
> > > > --
> > > > Thanks for any help
>
>
>

=myFunction(B11:B39,B8)

is two arguments. You have 27 left.

You have to write your function to work with multiple cells in a single
argument.

--
Regards,
Tom Ogilvy

"Peter M" <PeterM@discussions.microsoft.com> wrote in message
news:91BA56E5-4B82-41B5-BEEC-93D7BBCA18BC@microsoft.com...
> Nick-
>
> Thanks for your reply. Perhaps so. My programming experience is Fortran
> (which obviously dates me), and in Fortran it is not uncommon to have a
long
> argument list. I would be happy to redesign if I knew how. What I am
doing
> is passing values in cells, say B11:B39 & B8 into a function I wrote in VB
> which performs Newton-Raphson iteration and returns a single value, which
is
> the result of the iteration. Up to now the number of arguments I passed
was
> under the limit of 29. I need to be able to pass more than 29 though.
How
> can I pass these arguments, using the example I give above, pass B11:B39 &
> B8. Also, what do I need to do to the function statement (if anything) to
> receive arguments passed in an array form.
>
> Thanks,
>
> Peter
>
> "NickHK" wrote:
>
> > Peter,
> > IMO if you are passing 30 arguments, you should look at your design
again.
> > If it really require these 30 different pieces of information to return
a
> > single answer, you may be trying to do too much in one function.
> > If you are passing 30 cell values to calculate say an average, just pass
the
> > Range concerned.
> >
> > NickHK
> >
> > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > > Thanks. It's 30, because that's what I'm hitting. The error message
> > isn't
> > > very gooog though; it just says you are trying to pass too many
arguments
> > for
> > > the function, instead of you've hit the limit. Anyway, I know about
> > arrays,
> > > but what are user defined types?
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Yup but I do not remember what it is. Something like 40. You can
pass
> > arrays
> > > > and user defined types though if you get into trouble.
> > > >
> > > > "Peter M" wrote:
> > > >
> > > > > Is there a limit on the number of arguments you can have in a user
> > defined
> > > > > Function in Excel?
> > > > > --
> > > > > Thanks for any help
> >
> >
> >

While we're finding the limits for an UDF..
I max out at 57 arguments...

at 28 normal args + 29 element paramarray. = 57 arguments

call like:
with European seps..
=ArgListig1(1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;
24;25;26;27;28;{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22
\23\24\25\26\27\28\29})

with Anglo seps..
=ArgListig1(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26,27,28,{1\2\3\4\5\6\7\8\9\10\11\12\13\14\15\16\17\18\19\20\21\22
\23\24\25\26\27\28\29})


define like:

Function ArgListig1( _
arg01$, arg02$, arg03$, arg04$, _
arg05$, arg06$, arg07$, arg08$, arg09$, _
arg10$, arg11$, arg12$, arg13$, arg14$, _
arg15$, arg16$, arg17$, arg18$, arg19$, _
arg20$, arg21$, arg22$, arg23$, arg24$, _
arg25$, arg26$, arg27$, arg28$, ParamArray argP())
ArgListig1 = "oops"
Stop 'view locals...

End Function






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

> Thanks for sharing that - useful information.
>
> However, the topic was UDF. If I put your function in xl2002 (I
> can't get the extra argument in the declaration in xl2002 - just for
> info) and put in a formula
>
> =ArgListig1(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
> ,23,24
> ,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,
> 48,49, 50,51,52,53,54,55,56,57,58,59,60)
>
> in the worksheet, it tells me there is an error in the formula and
> highlights 29 (the 30th argument, note it is zero based<g>). So for
> a UDF, with just straight arguments, I believe it is 29.
>
> There actually was someone complaining about this limitation about 6
> months ago and of course that is the concern of the OP.

Thanks Tom. But what about the statement in the function itself?

Function myFuntion(?,x)

"Tom Ogilvy" wrote:

> =myFunction(B11:B39,B8)
>
> is two arguments. You have 27 left.
>
> You have to write your function to work with multiple cells in a single
> argument.
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> news:91BA56E5-4B82-41B5-BEEC-93D7BBCA18BC@microsoft.com...
> > Nick-
> >
> > Thanks for your reply. Perhaps so. My programming experience is Fortran
> > (which obviously dates me), and in Fortran it is not uncommon to have a
> long
> > argument list. I would be happy to redesign if I knew how. What I am
> doing
> > is passing values in cells, say B11:B39 & B8 into a function I wrote in VB
> > which performs Newton-Raphson iteration and returns a single value, which
> is
> > the result of the iteration. Up to now the number of arguments I passed
> was
> > under the limit of 29. I need to be able to pass more than 29 though.
> How
> > can I pass these arguments, using the example I give above, pass B11:B39 &
> > B8. Also, what do I need to do to the function statement (if anything) to
> > receive arguments passed in an array form.
> >
> > Thanks,
> >
> > Peter
> >
> > "NickHK" wrote:
> >
> > > Peter,
> > > IMO if you are passing 30 arguments, you should look at your design
> again.
> > > If it really require these 30 different pieces of information to return
> a
> > > single answer, you may be trying to do too much in one function.
> > > If you are passing 30 cell values to calculate say an average, just pass
> the
> > > Range concerned.
> > >
> > > NickHK
> > >
> > > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > > news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > > > Thanks. It's 30, because that's what I'm hitting. The error message
> > > isn't
> > > > very gooog though; it just says you are trying to pass too many
> arguments
> > > for
> > > > the function, instead of you've hit the limit. Anyway, I know about
> > > arrays,
> > > > but what are user defined types?
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > Yup but I do not remember what it is. Something like 40. You can
> pass
> > > arrays
> > > > > and user defined types though if you get into trouble.
> > > > >
> > > > > "Peter M" wrote:
> > > > >
> > > > > > Is there a limit on the number of arguments you can have in a user
> > > defined
> > > > > > Function in Excel?
> > > > > > --
> > > > > > Thanks for any help
> > >
> > >
> > >
>
>
>

Not sure what you are asking. If you mean how to handle multiple cell
arguments

Public Function NonZeroAverage(rng1 as range, rng2 as range)
Dim tot as Double, cnt as Long
tot = 0
cnt = 0
for each cell in rng1
if isnumeric(cell) then
tot = tot + cell.Value
cnt = cnt + 1
end if
Next
for each cell in rng2
if isnumeric(cell) then
tot = tot + cell.Value
cnt = cnt + 1
end if
Next
if cnt <> 0 then
NonZeroAverage = tot/cnt
else
NonZeroAverage = 0
end if
End Function

This is just for illustration. You are probably using a Paramarray, but it
illustrates handling any rng argument as if it was multicell. A single cell
argument would not cause a problem.

--
Regards,
Tom Ogilvy


"Peter M" <PeterM@discussions.microsoft.com> wrote in message
news:02C8499D-696D-4458-B74A-2AA4F3AF0F6E@microsoft.com...
> Thanks Tom. But what about the statement in the function itself?
>
> Function myFuntion(?,x)
>
> "Tom Ogilvy" wrote:
>
> > =myFunction(B11:B39,B8)
> >
> > is two arguments. You have 27 left.
> >
> > You have to write your function to work with multiple cells in a single
> > argument.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > news:91BA56E5-4B82-41B5-BEEC-93D7BBCA18BC@microsoft.com...
> > > Nick-
> > >
> > > Thanks for your reply. Perhaps so. My programming experience is
Fortran
> > > (which obviously dates me), and in Fortran it is not uncommon to have
a
> > long
> > > argument list. I would be happy to redesign if I knew how. What I am
> > doing
> > > is passing values in cells, say B11:B39 & B8 into a function I wrote
in VB
> > > which performs Newton-Raphson iteration and returns a single value,
which
> > is
> > > the result of the iteration. Up to now the number of arguments I
passed
> > was
> > > under the limit of 29. I need to be able to pass more than 29 though.
> > How
> > > can I pass these arguments, using the example I give above, pass
B11:B39 &
> > > B8. Also, what do I need to do to the function statement (if
anything) to
> > > receive arguments passed in an array form.
> > >
> > > Thanks,
> > >
> > > Peter
> > >
> > > "NickHK" wrote:
> > >
> > > > Peter,
> > > > IMO if you are passing 30 arguments, you should look at your design
> > again.
> > > > If it really require these 30 different pieces of information to
return
> > a
> > > > single answer, you may be trying to do too much in one function.
> > > > If you are passing 30 cell values to calculate say an average, just
pass
> > the
> > > > Range concerned.
> > > >
> > > > NickHK
> > > >
> > > > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > > > news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > > > > Thanks. It's 30, because that's what I'm hitting. The error
message
> > > > isn't
> > > > > very gooog though; it just says you are trying to pass too many
> > arguments
> > > > for
> > > > > the function, instead of you've hit the limit. Anyway, I know
about
> > > > arrays,
> > > > > but what are user defined types?
> > > > >
> > > > > "Jim Thomlinson" wrote:
> > > > >
> > > > > > Yup but I do not remember what it is. Something like 40. You can
> > pass
> > > > arrays
> > > > > > and user defined types though if you get into trouble.
> > > > > >
> > > > > > "Peter M" wrote:
> > > > > >
> > > > > > > Is there a limit on the number of arguments you can have in a
user
> > > > defined
> > > > > > > Function in Excel?
> > > > > > > --
> > > > > > > Thanks for any help
> > > >
> > > >
> > > >
> >
> >
> >

Tom-
Thanks again for your help. For clarification, this is what I'm asking...

I currently have the following, which of course breaks down once you hit the
limit of 29 arguments:

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, this, then:

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

In VB:
Function Newton(.......?) This is where I don't know what to do. Can you
please specify the exact statements to accomplish what I need?

Thanks again,
Peter










"Tom Ogilvy" wrote:

> Not sure what you are asking. If you mean how to handle multiple cell
> arguments
>
> Public Function NonZeroAverage(rng1 as range, rng2 as range)
> Dim tot as Double, cnt as Long
> tot = 0
> cnt = 0
> for each cell in rng1
> if isnumeric(cell) then
> tot = tot + cell.Value
> cnt = cnt + 1
> end if
> Next
> for each cell in rng2
> if isnumeric(cell) then
> tot = tot + cell.Value
> cnt = cnt + 1
> end if
> Next
> if cnt <> 0 then
> NonZeroAverage = tot/cnt
> else
> NonZeroAverage = 0
> end if
> End Function
>
> This is just for illustration. You are probably using a Paramarray, but it
> illustrates handling any rng argument as if it was multicell. A single cell
> argument would not cause a problem.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> news:02C8499D-696D-4458-B74A-2AA4F3AF0F6E@microsoft.com...
> > Thanks Tom. But what about the statement in the function itself?
> >
> > Function myFuntion(?,x)
> >
> > "Tom Ogilvy" wrote:
> >
> > > =myFunction(B11:B39,B8)
> > >
> > > is two arguments. You have 27 left.
> > >
> > > You have to write your function to work with multiple cells in a single
> > > argument.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > > news:91BA56E5-4B82-41B5-BEEC-93D7BBCA18BC@microsoft.com...
> > > > Nick-
> > > >
> > > > Thanks for your reply. Perhaps so. My programming experience is
> Fortran
> > > > (which obviously dates me), and in Fortran it is not uncommon to have
> a
> > > long
> > > > argument list. I would be happy to redesign if I knew how. What I am
> > > doing
> > > > is passing values in cells, say B11:B39 & B8 into a function I wrote
> in VB
> > > > which performs Newton-Raphson iteration and returns a single value,
> which
> > > is
> > > > the result of the iteration. Up to now the number of arguments I
> passed
> > > was
> > > > under the limit of 29. I need to be able to pass more than 29 though.
> > > How
> > > > can I pass these arguments, using the example I give above, pass
> B11:B39 &
> > > > B8. Also, what do I need to do to the function statement (if
> anything) to
> > > > receive arguments passed in an array form.
> > > >
> > > > Thanks,
> > > >
> > > > Peter
> > > >
> > > > "NickHK" wrote:
> > > >
> > > > > Peter,
> > > > > IMO if you are passing 30 arguments, you should look at your design
> > > again.
> > > > > If it really require these 30 different pieces of information to
> return
> > > a
> > > > > single answer, you may be trying to do too much in one function.
> > > > > If you are passing 30 cell values to calculate say an average, just
> pass
> > > the
> > > > > Range concerned.
> > > > >
> > > > > NickHK
> > > > >
> > > > > "Peter M" <PeterM@discussions.microsoft.com> wrote in message
> > > > > news:18368078-A4D6-4776-93A7-018DAFA9105D@microsoft.com...
> > > > > > Thanks. It's 30, because that's what I'm hitting. The error
> message
> > > > > isn't
> > > > > > very gooog though; it just says you are trying to pass too many
> > > arguments
> > > > > for
> > > > > > the function, instead of you've hit the limit. Anyway, I know
> about
> > > > > arrays,
> > > > > > but what are user defined types?
> > > > > >
> > > > > > "Jim Thomlinson" wrote:
> > > > > >
> > > > > > > Yup but I do not remember what it is. Something like 40. You can
> > > pass
> > > > > arrays
> > > > > > > and user defined types though if you get into trouble.
> > > > > > >
> > > > > > > "Peter M" wrote:
> > > > > > >
> > > > > > > > Is there a limit on the number of arguments you can have in a
> user
> > > > > defined
> > > > > > > > Function in Excel?
> > > > > > > > --
> > > > > > > > Thanks for any help
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>