Optional Arguments in Custom Fuctions

[eggman]

I am trying to build a custom function and could use some advice. There is
more to it than this, but at the core what I am trying to do is a custom sum
function that lets you put any number of cell references in the formula.

This is a very simplified version, but if I can get this to work, then I can
get the rest to work.

Thanks in advance


Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4)

For X = 1 to 4
if ismissing(RangeX) then Exit Function Else MySum = MySum +
Cells(RangeX.Row, RangeX.Column)
Next X

 

[Tom Ogilvy]

You need to use a paremarray:

Public Function MySum(ParamArray v() As Variant)
Dim sum As Double
For i = LBound(v) To UBound(v)
Select Case TypeName(v(i))
Case "Range"
Set r = v(i)
For Each Cell In r
If IsNumeric(Cell) Then
sum = sum + Cell
End If
Next
Case "Variant()"
For j = LBound(v(i)) To UBound(v(i))
If IsNumeric(v(i)(j)) Then
sum = sum + v(i)(j)
End If
Next
Case "Integer", "Long", "Double", "Single"
sum = sum + v(i)
Case Else
End Select
Next i
MySum = sum
End Function

Usage examples:

[from the immediate window in the VBE:]
? mysum(1,"A",2)
3
? mysum(range("A1:A10"),1,"B",Array("A",1,2,3,"Z"),Range("A11"),10)
83

from the worksheet
=mysum(A1:A10,{1,2,3,4,5},A11)
returned 81

 

[Bernie Deitrick]

From

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Variant ParamArray

The second method for working with optional parameters is to use a ParamArray Variant parameter. A
ParamArray allows any number of parameters, including none at all, to be passed to the function. You
can have one or more required parameters before the ParamArray, but you cannot have any optional
parameters if you have a ParamArray. Moreover, the ParamArray variable must be the last parameter
declared for a function. The ParamArray variables must be Variant types. You cannot have a
ParamArray of other types, such as Long integers. If necessary, you should validate the values
passed in the ParamArray, such as to ensure they are all numeric. If your function requires one or
more inputs followed by a variable number of parameters, declare the required parameters explicitly
and use a ParamArray only for the optional parameters. For example, the function SumOf below accepts
any number of inputs and simply adds them up:


Function SumOf(ParamArray Nums() As Variant) As Variant
''''''''''''''''''''''''''''''''''
' Add up the numbers in Nums
''''''''''''''''''''''''''''''''''
Dim N As Long
Dim D As Double
For N = LBound(Nums) To UBound(Nums)
If IsNumeric(Nums(N)) = True Then
D = D + Nums(N)
Else
SumOf = CVErr(xlErrNum)
Exit Function
End If
Next N
SumOf = D
End Function
In your function code, you can use:

Dim NumParams As Long
NumParams = UBound(Nums) - LBound(Nums) + 1

to determine how many parameters were passed in the ParamArray variable Nums. This will be 0 if no
parameters were passed as the ParamArray. Of course, the code above counts the number of parameters
within the ParamArray, not the total number of parameters to the function.

 

[Chip Pearson]

Perhaps you should be using a ParamArray for the optional arguments. A
ParamArray accepts any number of arguments (including none) and these must
be Variant data types.

Function MySum(Range1 As Range, ParamArray Args() As Variant)
Dim N As Long
Dim NumArgs As Long
NumArgs = UBound(Args) - LBound(Args)
For N = LBound(Args) To UBound(Args)
' do something
Next N
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

Perhaps you should be using a ParamArray for the optional arguments. A

ParamArray accepts any number of arguments (including none) and these must
be Variant data types.

Function MySum(Range1 As Range, ParamArray Args() As Variant)
Dim N As Long
Dim NumArgs As Long
NumArgs = UBound(Args) - LBound(Args)

You don't make use of it in your code, but you accidentally left off the
+1...

NumArgs = UBound(Args) - LBound(Args) + 1

Rick

 

[eggman]

Thanks all - much appreciated

You don't make use of it in your code, but you accidentally left off the
+1...

NumArgs = UBound(Args) - LBound(Args) + 1

Rick