Passing multiple ranges to Excel function 2

[janineo]

I'm trying to build a function that takes at least 1 range, and possibly several ranges as parameters - a little like the SUM worksheet function.

I've got this so far:
Public Function MyFunction(range1 As Range, _
Optional range2 As Range, _
Optional range3 As Range, _
Optional range4 As Range, _
Optional range5 As Range) As Integer

Dim count As Integer
Dim c As Range

count = 0

For Each c In range1.Cells
If c.Value = "S" Then
count = count + 1
End If
Next c


If range2 Is Not Nothing Then
For Each c In range2.Cells
If c.Value = "S" Then
count = count + 1
End If
Next c
End If

.... (same for range3 - range5)

MyFunction = count

End Function


When I run this I get an 'Invalid Use of Object' error on the function declaration line.
Can anyone help me please?

 

[JustinEzequiel]

Option Explicit

Private Sub Testing()
Dim a As Range
Dim b As Range
Dim c As Range

Set a = Range("A1", "A26&quot
Set b = Range("C1", "C26&quot
Set c = Range("E1", "F26&quot

Debug.Print test(a, b, c)
Debug.Print test(b, c)
Debug.Print test(c)
Debug.Print test
End Sub

Private Function test(ParamArray Ranges()) As Long
Dim nLow As Long
Dim nUpper As Long
Dim i As Long
Dim c As Range
Dim nCount As Long

nLow = LBound(Ranges)
nUpper = UBound(Ranges)

For i = nLow To nUpper
For Each c In Ranges(i)
If c.Value = "S" Then
nCount = nCount + 1
End If
Next c
Next i

test = nCount
End Function

 

[SkipVought]

Thanx, Justin!

I learned something (ParamArray)

You deserve another STAR. Skip,
metzgsk@voughtaircraft.com