Infinite Parameters in UDF

[kpark91]

Hello,

I was just wondering if it is possible to create a UDF with infinite number of arguments just like =SUMPRODUCT() or =SUM() in excel functions.

If I can not, what is an alternative if any?

Thank you in advance,
KPark.

 

[Rick Rothstein]

Infinite? No. I think the limit is 30 arguments total. You can use a ParamArray to take an arbitrary number of arguments. The ParamArray must be the last argument in the argument list and there cannot be any Optional arguments when a ParamArray is involved. Here is an example.

Function ConcatenateAndSum(Employee As String, ParamArray Numbers() As Variant) As String
  Dim X As Long, Total As Double
  For X = LBound(Numbers) To UBound(Numbers)
     Total = Total + Numbers(X)
  Next
  ConcatenateAndSum = Employee & "'s Total: " & Total
End Function

And here are a couple of examples of its use...

=ConcatenateAndSum("KPark",1,2,3.4,5.67)

=ConcatenateAndSum("Rick",1,3,5,7,9,11,13,15,17,19)

 

[kpark91]

Awesome. Thank you!

In addition, since you mention about optional arguments.
How do I define them in UDF?

 

[Rick Rothstein]

In addition, since you mention about optional arguments.
How do I define them in UDF?

Optional arguments must come after all mandatory arguments in the list of argument (if any... it is possible to have only Optional arguments if you wish to). To declare an argument as Optional, you simply precede its name in the argument list with the keyword Optional...

Function MyUDF(UserName As String, City As String, State As String, Optional TelephoneNumber As String, Optional FaxNumbare As String) As Variant

Inside your code, depending on its functionality, you will probably have to provide code to test if the argument was passed or not (if it wasn't passed, the argument will have the default value of the data type you declare it as). If you make the Optional argument a Variant, you would use the IsMissing function to test if a value was passed into it or not. There is an additional functionality associated with Optional arguments that might make the above testing unnecessary... you can assign a default constant value to an Optional argument inside the function declarations statement and, if you do, the Optional argument would assume that value if the user does not pass a value in, otherwise the passed in value would be used. Here is an example of declaring an Optional argument with a default value...

Function PI(Optional MaxNumberOfDecimalPlaces As Long = 8) As String
  PI = 4 * Atn(1)
  PI = Round(PI, MaxNumberOfDecimalPlaces)
End Function

Here are some examples of calling this UDF and the results it returns...

=PI()   ==>  3.14159265
=PI(3)  ==>  3.142
=PI(6)  ==>  3.141593

 

[kpark91]

wow. Thank you very much :D