Can I pass a range to a paramarray argument?

[JenniferMurphy]

This function works if I pass it a list of cell addresses, like =Rangesum(C4,D4,E4)

Function RangeSum(ParamArray list())
Dim i As Integer
RangeSum = 0
For i = 0 To UBound(list)
  RangeSum = RangeSum + list(i)
Next i

But it gets a value error if I call it with a range, like "RangeSum(C4:E4)" or "RangeSum((C4:E4))".

Is there any way to write the function so that both calls will work, like the Sum function, which works either way?

=Sum(C4,D4,E4)
=Sum(C4:E4)

 

[footoo]

Function RangeSum(ParamArray list())
Dim i As Integer, c
RangeSum = 0
For i = 0 To UBound(list)
    For Each c In list(i)
        RangeSum = RangeSum + c
    Next
Next i
End Function

 

[JenniferMurphy]

Wow! That works!!! But I guess you already knew that.

Can you help me understand why this works and my version doesn't?

I did a little testing and discovered that "c" must be type Variant or Object. Does it matter which? I've never used Object variables before.

Anyway, thanks for the help. This is great.

 

[Rick Rothstein]

Just so you can see alternatives, here is another way to code your RangeSum function...

Function RangeSum(ParamArray list())
  Dim X As Integer
  RangeSum = 0
  For X = 0 To UBound(list)
    If TypeOf list(X) Is Range Then
      RangeSum = RangeSum + Application.Sum(list(X))
    Else
      RangeSum = RangeSum + list(X)
    End If
  Next
End Function

 

[JenniferMurphy]

Just so you can see alternatives, here is another way to code your RangeSum function...

I'm guessing that this version would be a little faster if the arguments are large ranges because the sums would be done in the VBA interpreter, not my VBA code. Would it be a bit slower if the arguments are all single cells because of the extra If or not because of the extra inner loop?

I've done a bit more testing and I think I (partially) understand what is going on. My function failed when passed a range because it was treating the range as a single cell. The inner loop or your call to the application Sum function dealt with the elements inside that range.

Is that close?

 

[Tetra201]

... I did a little testing and discovered that "c" must be type Variant or Object. Does it matter which?..

Actually, it can be declared as Range as well. However, list() must be declared as Variant, so it's logical to keep its sub-elements as Variants.

To give you even more food for thoughts:
- footoo's solution works with =RangeSum(C4,D4:E4), but stumbles on =RangeSum(C4,D4:E4,1)
- Rick's solution works with =RangeSum(C4,D4:E4,1), but stumbles on =RangeSum(C4,D4:E4,{1,2})
- the solution below works with =RangeSum(C4,D4:E4,{1,2}), but it's essentially just an inefficient wrapper for the SUM() function.

Function RangeSum(ParamArray list())
    Dim i As Integer
    RangeSum = 0
    For i = 0 To UBound(list)
        RangeSum = RangeSum + WorksheetFunction.Sum(list(i))
    Next i
End Function

 

[Rick Rothstein]

- Rick's solution works with =RangeSum(C4,D4:E4,1), but stumbles on =RangeSum(C4,D4:E4,{1,2})

This modification should fix the that problem with my code...

Function RangeSum(ParamArray list())
  Dim X As Integer
  RangeSum = 0
  For X = 0 To UBound(list)
    If TypeOf list(X) Is Range Or VarType(list(X)) > 8191 Then
      RangeSum = RangeSum + Application.Sum(list(X))
    Else
      RangeSum = RangeSum + list(X)
    End If
  Next
End Function

 

[JenniferMurphy]

Actually, it can be declared as Range as well. However, list() must be declared as Variant, so it's logical to keep its sub-elements as Variants.

OK

To give you even more food for thoughts:
- footoo's solution works with =RangeSum(C4,D4:E4), but stumbles on =RangeSum(C4,D4:E4,1)

I don't think I'd ever need to include a literal, but it's good to be as general as possible.

- Rick's solution works with =RangeSum(C4,D4:E4,1), but stumbles on =RangeSum(C4,D4:E4,{1,2})

Is that an array? I've tinkered with array functions a little, but I don't understand what this one does. Can you enlighten me?

- the solution below works with =RangeSum(C4,D4:E4,{1,2}), but it's essentially just an inefficient wrapper for the SUM() function.

The RangeSum function was just an example. The actual function where I want to use this technique is much more complicated and has no worksheet equivalent. I chose a simple function to avoid confusing the issue.

Thank you for the help

 

[JenniferMurphy]

This modification should fix the that problem with my code...

    If TypeOf list(X) Is Range Or VarType(list(X)) > 8191 Then

OMG 8191? Really? Excel uses 13 bits to record the number or arguments?

 

[Tetra201]

... Is that an array? I've tinkered with array functions a little, but I don't understand what this one does...

Yes, this is an array constant.