Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a Function with a variable number of arguments
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a Function with a variable number of arguments
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) "TonyJeffs" wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a Function with a variable number of arguments
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a Function with a variable number of arguments
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extend the number of arguments of a function | Excel Worksheet Functions | |||
How do I put a variable column number in the sum() function? | Excel Worksheet Functions | |||
Variable in arguments | Excel Worksheet Functions | |||
Passing Variable Number of Arguments to a Sub | Excel Discussion (Misc queries) | |||
Question: Macro overloading, passing variable number of arguments | Excel Programming |