Variable number of arguments

[mcescher]

Hi All,
I'm sure this has been addressed before, but I couldn't come up with
the correct search terms to find it.

It seems that I heard one time that there is a way to define a
function with a variable number of arguments. Is this possible? What
is the construct?

Thanks for your time,
Chris M.

 

[egerds]

search for Optional
http://msdn.microsoft.com/en-us/library/aa164808(office.10).aspx
in practice instead of using the ismissing function, I define the
default value, though this might not work if the optional argument is
a Variant

 

[Dale_Fye via AccessMonster.com]

In addition to Optional parameters, you might also want to consider parmeter
arrays. I use one of these in the following function, which allows me to
pass as many values to the function as I want. The key to using parameter
arrays is that they must be listed as the final parameter in a function.

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

HTH
Dale

 

[Marshall Barton]

I'm sure this has been addressed before, but I couldn't come up with
the correct search terms to find it.

It seems that I heard one time that there is a way to define a
function with a variable number of arguments. Is this possible? What
is the construct?

Decalare your procedure's last argument with the ParamArray
keyword. See Function Statement in VBA Help

E.g.

Public Function MySum(ParamArray varList())
For Each X in varList
MySum = MySum + x
Next x
End Function

 

[mcescher]

Decalare your procedure's last argument with the ParamArray
keyword.  See Function Statement in VBA Help

E.g.

Public Function MySum(ParamArray varList())
        For Each X in varList
                MySum = MySum + x
        Next x
End Function

Thanks to all who replied. That was just what I was looking for.

Chris M.