Excel 2007 VBA UDF Argument Limits

[djt76010]

I am writing some VBA UDF's in Excel 2007 and was wondering if there are limits on the number of variables in a UDF as well as a limit on the number of characters.

 

[Jerry Sullivan]

By "number of variables in a UDF" do you mean Arguments to the User Defined Function (not Variables inside the UDF Procedure)?

Microsoft's Specifications and Limits for xl2007 can be found at this link.
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

The two limits related to your question would be:
Length of formula contents: 8,192 characters
Arguments in a function: 255

Microsoft lists those same two limits for xl2010.

Through some testing in xl2010, the Limit on the number of Arguments to a function appears to have some further restrictions.
For required arguments, the limit appears to be 60.
To use 255 arguments, one needs to use 1 required argument and a Parameter Array of 254 Optional Arguments.

Here is some code that you could use to confirm those findings.

[COLOR="#008080"]'The Maximum Limit for Required Arguments is 60[/COLOR]
Function MAX_ARGS_1( _
    a001&, a002&, a003&, a004&, a005&, a006&, a007&, a008&, a009&, a010&, _
    a011&, a012&, a013&, a014&, a015&, a016&, a017&, a018&, a019&, a020&, _
    a021&, a022&, a023&, a024&, a025&, a026&, a027&, a028&, a029&, a030&, _
    a031&, a032&, a033&, a034&, a035&, a036&, a037&, a038&, a039&, a040&, _
    a041&, a042&, a043&, a044&, a045&, a046&, a047&, a048&, a049&, a050&, _
    a051&, a052&, a053&, a054&, a055&, a056&, a057&, a058&, a059&, a060&) As Variant
    
    MAX_ARGS_1 = a001 & "...." & a060
End Function

[COLOR="#008080"]'The Maximum Limit for a ParamArray Arguments is 254[/COLOR]
Function MAX_ARGS_2(ParamArray aMax() As Variant) As Variant
    MAX_ARGS_2 = aMax(LBound(aMax)) & "...." & aMax(UBound(aMax))
End Function

[COLOR="#008080"]'The Maximum Limit for UDF Arguments is 255 (1 Required plus 254 in ParamArray)[/COLOR]
Function MAX_ARGS_3(aRequired&, ParamArray aMax() As Variant) As Variant
    MAX_ARGS_3 = aRequired & ", " & aMax(LBound(aMax)) & "...." & aMax(UBound(aMax))
End Function

Example Test Formula:
=MAX_ARGS_1(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)

 

[djt76010]

By "number of variables in a UDF" do you mean Arguments to the User Defined Function (not Variables inside the UDF Procedure)?

Yes, that is what I mean.

Thanks for the info. I am developing UDFs for my office to use and so I am trying to use variable names that tell people what to enter for each argument. Is there a limit of characters for function declarations?

 

[mikerickson]

" Is there a limit of characters for function declarations?"
There is probably such a limit.

But I would me more concerned about the limit imposed by your human co-workers ability to read a long list of argument names.
If the user is expected to enter more than 5 arguments, you might think about assigning arguments via a user form, rather than expecting the user to get all that info from an intisense window.

 

[Jerry Sullivan]

Mike's right, of course. A UDF formula with so many arguments would be really difficult to use.
If you're pushing the limits of how many characters or arguments are allowed, you might want to revisit your approach.

One clarification. I used the term "Required Arguments" in my initial response. What I meant is "Separate arguments other than a Parameter Array".
Some of the 60 allowable separate arguments could use the keyword Optional, so "Required Arguments" isn't an accurate term.

 

[djt76010]

Unfortunately the number of variables is not really something that I can play with as I am designing a replacement for a similar tool.

I don't really know anything about user forms. What I am looking to do is create functions that users would be able to use in any spreadsheet that they are working in. Up to this point I have been creating my UDF's in an .xlam add-in file. Can I create a user form that will work off of this UDF in an add-in file?

Thanks for the idea.

 

[Jerry Sullivan]

Can I create a user form that will work off of this UDF in an add-in file?

Yes, that could be done by having a Sub in your add-in that Shows the UserForm then enters a UDF formula string based on the Users' entries.

If you explain a few more details about your process, it will be easier to suggest options that are a good fit.

 

[djt76010]

I'm not sure what details you need but I will try to explain what I am doing.

I have designed a UDF that has a large number of arguments. I was trying to give each argument a meaningful name, but apparently I have exceeded the allowable number of characters. As Mike suggested, I was looking into input forms and trying to see if I could design them to act like the function dialog box that a UDF comes with, but with a more user friendly layout. I guess the two things that look like they present the biggest challenge would be

1. To allow the user to click into spreadsheet cells for references that could be used in the formula. I would like the user to be able to click on a cell (i.e. : A1), and have that cell reference (A1) be passed into that field instead of the value held in A1

2. Once the user has a function setup through the input form, if the user opens the input form while on a cell that has my UDF being used in it, I would like the variables to fill the appropriate fields in the input form.

 

[Jerry Sullivan]

How many arguments does the UDF take?
Are the arguments required or optional?
Typically, how many parameters would be entered as cell references vs constants?

 

[Jerry Sullivan]

The big picture that I'm trying to understand in order to try to suggest an approach is whether the user needs to do any entry direct entry into the cells that hold the UDF formulas.

A typical way to collected data from the user would be to have them enter the values into a data table that has Fields for each Column Heading and a Row for each record. This is a familiar interface for users and it makes for easy editing of multiple-cells though copy-paste.
The users can enter constants or Cell references into these fields.

You could have your UDF reference these values up to the maximum number of arguments allowed.
Is that an approach that might work for you?