Can I pass a range to a paramarray argument?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,691
Office Version
  1. 365
Platform
  1. Windows
This function works if I pass it a list of cell addresses, like =Rangesum(C4,D4,E4)

Code:
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?

Code:
=Sum(C4,D4,E4)
=Sum(C4:E4)
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Code:
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
 
Upvote 0
Wow! That works!!! But I guess you already knew that. :laugh:

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.
:beerchug:
 
Upvote 0
Just so you can see alternatives, here is another way to code your RangeSum function...
Code:
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
 
Last edited:
Upvote 0
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? ;-)
 
Upvote 0
... 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.
Code:
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
 
Upvote 0
- 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...
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top