1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Gum
  • 11
  • 2
  • You seem to be misunderstanding what a range is or how ParamArray works. `=Func1(A1,A2)` is passing two parameters, each is a range consisting of one cell. `=Func1(A1:A2)` is passing one parameter, a range consisting of two cells. I'm sure the issue is with how you are using the passed parameters,. If you edit your Q to show how you are trying to use the ParamArray I may be able to help. – chris neilsen Nov 23 '21 at 00:03
  • Also, you might find that [`Aggregate`](https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df) already does what you want – chris neilsen Nov 23 '21 at 00:10
  • Another option is to Union the ranges in the function call. Eg `Func1((A1, C3))`. Note the double brackets. The inner pair is the union operator. Written like this, Func1 is passed a single parameter, which is a single non-contiguous range `A1,C3` – chris neilsen Nov 23 '21 at 00:15
  • It might also be relevant which version of Excel you're using, specifically if it supports Dynamic Array functions – chris neilsen Nov 23 '21 at 00:19
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Spinner Nov 26 '21 at 03:38

1 Answers1

0

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
Rory
  • 32,730
  • 5
  • 32
  • 35
  • As mentioned, I'm not just using SUM, but several other built-in functions as well which means I can't just add the results of each range together. I was hoping there was some way that wouldn't involve me trying to rewrite a dozen or so built-in functions from scratch. For example if I had : `Func1 = Application.WorksheetFunction.Stdev.P(Ranges) * Application.WorksheetFunction.Median(Ranges)`, I'd have to rewrite the median and Stdev formulas and apply them to each cell. – Gum Nov 22 '21 at 23:25
  • @gum you need to make your question more specific then. The appropriate answer will depend on what you are actually doing. Not all functions will work with discontiguous ranges anyway. – Rory Nov 22 '21 at 23:27
  • Just added some clarification. All of the functions I am working with will take discontiguous ranges, and they all do so in the form of (range1, [range2], [range3], ...), just like the Sum, Stdev, and Median functions. I was hoping for an approach which would work for any function taking inputs in that form. – Gum Nov 22 '21 at 23:44
  • In that case, you could loop through and use `Union` to join the input ranges. – Rory Nov 22 '21 at 23:51