Max number of args to a function?

[Rick Charnes]

Is there a limit to the number of arguments a VBA function can take in
Word 2003? Someone mentioned two or three years ago that the limit was
59, but I am wondering if that has changed. I don't see anything in
Help about this. Thanks.

 

[Tony Jollans]

Close! 60.

 

[Rick Charnes]

Thanks. Where would I have found this figure? Don't see it anywhere in
Help.

 

[Tony Jollans]

To be honest, I don't know - a quick look at help turned up nothing.

I had a feeling it was 60 and it only took a moment to try and use 61 and
find it didn't work.

 

[Jezebel]

You can use a ParamArray to send an arbitrary number of arguments.

 

[Tony Jollans]

Technically it's one argument that the procedure receives but, yes, the
number of elements is, AFAIK, unlimited (or, I suppose, limited only by the
maximum length of the calling statement).

 

[Rick Charnes]

I'm actually calling this Word macro from another application
(Powerbuilder) which installs Word as an OLE server and therefore allows
me to send commands to Word such as 'execute this macro'. Currently, I
call the macro from Powerbuilder with about 12 arguments and it works
great. I'd now like to add about 80 more (I know it sounds crazy...)

Tony, you mention that the number of elements is unlimited but you got
an error at 61? Maybe as you suggest the limit is the length of the
calling statement rather than the number of elements?

 

[Jezebel]

Put your arguments into an array, and pass that as a single argument.

 

[Rick Charnes]

Thanks, how do I reference that in the VBA code? I looked up ParamArray
in Help and don't see anything.

 

[Tony Jollans]

Code your macro something like this

Sub mySub(ParamArray myArgs())
For n = LBound(myArgs) To UBound(myArgs)
' Do whatever you want with the n'th argument here
Next
End Sub

And call it with as many parameters as you like. ParamArray arrays always
have an LBound of zero and if they are empty they have a UBound of -1.

ParamArray is described in the help for the Sub statement