Passing Variable # of Arguments to Built-in Functions with ParamArray

Question

I want to create a VBA function which combines several built-in Excel functions, each of which can take one or more cell ranges of different sizes [edit: such as SUM, COUNT, STDEV.P, AVERAGE, MIN, etc; though I would prefer a method that works for any formula which can take discontiguous ranges like these].

The number of ranges entered must be flexible, and these arguments will be entered directly in the worksheet. This should be easiest using VBA's ParamArray, but I'm unsure how to pass each cell range into the built-in functions. Here's a simple example of what I've tried:

Function Func1(ParamArray Ranges() As Variant)
    Func1 = Application.WorksheetFunction.Sum(Ranges)
End Function

This works for single cell arguments, but not ranges. For example, if cell A1=1 and A2=2, "=Func1(A1,A2)" returns 3, but =Func1(A1:A2) returns #VALUE!

I thought of looping through each range in Ranges(), but I can't think of any way to enter the arguments into the built-in SUM function one at a time while looping.

p.s. - in reality I'm calling about 10 different functions (not just sum, otherwise this would be trivial), each with the same set of ranges, and this code is meant to allow those to only be entered once per cell, rather than typed into each of the 10 functions.

Answer 1

You need to iterate the array - for example:

Function Func1(ParamArray Ranges() As Variant)
    Dim n As Long
    For n = LBound(Ranges) To UBound(Ranges)
        Func1 = Func1 + Application.WorksheetFunction.Sum(Ranges(n))
    Next
End Function