Input range to UDF ?

[Guest]

My function is

Function nEven(mRange As Range) As Long
Dim cell As Range, score As Integer
For Each cell In mRange
score = cell.Value
If score > 10 Then
nEven = nEven + 1
End If
Next cell
End Function

In the worksheet the cells A1 down to C4 have
12 0 0
0 13 0
0 0 45
0 3 0

now then =nEven(A1:C4) works, but =nEven(A1,B2,C3) with three separate cells
does not.

Looking at the second equation, I see that Excel would have trouble telling
the difference a single range with three cells and three separate arguments.

Thanks for any help you can give me.

 

[Guest]

in the VBA help for function, look at the information for a paramarray.


ParamArray Optional. Used only as the last argument in arglist to indicate
that the final argument is an Optional array of Variant elements. The
ParamArray keyword allows you to provide an arbitrary number of arguments. It
may not be used with ByVal, ByRef, or Optional.

 

[Bob Phillips]

Function nEven(ParamArray mRange()) As Long
Dim cell As Range, score As Integer
Dim i As Long
For i = LBound(mRange) To UBound(mRange)
If TypeOf mRange(i) Is Range Then
For Each cell In mRange(i)
If cell.Value > 10 Then nEven = nEven + 1
Next cell
End If
Next i
End Function



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thank you Bob & Tom. I can't believe how fast you guys are.