writing a Function with a variable number of arguments

[TonyJeffs]

How do I write a function that can take any number of integers?

for example

function MyAverage(x as integer, [any amount more] as integers)
....
end function

Thanks

Tony

 

[Bob Phillips]

Tony,

You need to use ParamArray. Here is a simple example

Function myTest(ParamArray vals())
Dim i As Long
Dim mysum


For i = LBound(vals, 1) To UBound(vals, 1)
mysum = mysum + vals(i)
Next i

myTest = mysum
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stephen Bullen]

Hi TonyJeffs,

How do I write a function that can take any number of integers?

for example

function MyAverage(x as integer, [any amount more] as integers)
....
end function

If you want your function to accept an arbitrary number of parameters, you use a ParamArray, but that has to be an array of Variants:

Function MyAverage(ParamArray vaToAverage() As Variant) As Double

If you're calling this from the worksheet, each element of the array could be a value, a range or another array or values, ranges and/or other
arrays. To efficiently and safely average them requires a recursive procedure to get the total and count, then do the average (watch out for
word-wrap):

'Public function to average an arbitrary number of
'elements (up to 30), where each element could be
'a number, range or a nested array of numbers and/or
'ranges
Function MyAverage(ParamArray vaToAverage() As Variant)

Dim dTotal As Double
Dim lCount As Long

On Error Resume Next

'Start the recursive totalling and counting
AverageArray vaToAverage, dTotal, lCount

If lCount > 0 Then
MyAverage = dTotal / lCount
Else
'Return #Value if no numeric items provided
MyAverage = CVErr(1007)
End If

End Function

'Recursive routine to sum and count the arbitrary elements given to a function
Private Sub AverageArray(ByVal vaArray As Variant, ByRef dTotal As Double, lCount As Long)

Dim vItem As Variant
Dim rngCell As Range
Dim dThisTotal As Double

On Error Resume Next

'Loop through what we were given
For Each vItem In vaArray

If IsArray(vItem) Then

'If it's a nested array of stuff,
'so recurse to average that
AverageArray vItem, dTotal, lCount

ElseIf TypeName(vItem) = "Range" Then

'If it's a Range, add up the values from the cells
For Each rngCell In vItem.Cells
If IsNumeric(rngCell.Value) Then
dTotal = dTotal + rngCell.Value
lCount = lCount + 1
End If
Next

ElseIf IsNumeric(vItem) Then
'If it's a number, we can just add it
dTotal = dTotal + vItem
lCount = lCount + 1
End If
Next

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[TonyJeffs]

Thanks Bob, Steve
Got it now!
What I was trying to write was, I discovered, the min() function from excel.
But I'll use the info for other things...

I'm destined to be a novice programmer all my life!

Tony
9 at tonyjeffs dot commmmmmmm