What is the maximum number of parameters in a UDF.

[kutabale]

In a udf function what is the maximum number of parameters that an UDF
and have. I am try to create a function with is macro type true with
35 parameters. how do i do this.

[Govert van Drimmelen]

Hi there,

Under Excel 2003 and earlier, the maximum number of parameters that a
function can take is 30.

In Excel 2007 and later the limit was raised to 255 parameters, but
there are some additional limitations to what is displayed in the
function wizard. The total argument names (including a ',' separator)
must not be more than 255 characters.

I guess you're using Excel 2003 or earlier?

Regards,
Govert

[ajwillshire]

Hi,

 

My solution to this if I am passing a lot of values into my UDF is to pass them as an array and then split them out into the separate parameters inside the compiled code.

Obviously it doesn't work in all cases and it might require a slight reorganisation of the Excel sheet but it works quite well and makes life easier for populating the formulae as well.

 

e.g., a function which takes some data and transforms it according to a list of parameters might be:

 

<ExcelFunction(IsMacroType:=True)> Public Shared Function MyFunction(ByVal InputData() As Double, ByVal ControlArray() As Object) As Double() 

 
For i = 0 To ControlArray.GetUpperBound(0) 

If TypeOf ControlArray(i) Is ExcelDna.Integration.ExcelEmpty Then ControlArray(i) = 0  'Converts empty cells in the control array to 0

Next i

Dim Parameter1 As String = CStr(ControlArray(1)) 
Dim Parameter2 As Integer = CInt(ControlArray(2)) 
Dim Parameter3 As Integer = CInt(ControlArray(3)) 
Dim Parameter4 As Double = CDbl(ControlArray(4)) 

 etc...

 

Hope this helps,

Andrew

[kutabale]

Thank you very much for the help. I start using .xlsx so now i can
pass 35 or more parameters.