vba pass a group of cells as range to function

Question

I'm getting a group of cells and doing some calculations over them in function below.

It works if I pass a range (with : sign) as first parameter, but it fails if I choose some cells as its range (A1, A3, B6, B9). It just gets first cell before comma as first parameter. But I want whole cells.

What can I do? (except using strings for passing ranges)

Function calculateIt(Sessions As Range, Customers As Range) As Single
    ' calculate them...
End Function

One thing more: Is it possible to pass a group of ranges as a parameter? how?

Answer 1

As written, your function accepts only two ranges as arguments.

To allow for a variable number of ranges to be used in the function, you need to declare a ParamArray variant array in your argument list. Then, you can process each of the ranges in the array in turn.

For example,

Function myAdd(Arg1 As Range, ParamArray Args2() As Variant) As Double
    Dim elem As Variant
    Dim i As Long
    For Each elem In Arg1
        myAdd = myAdd + elem.Value
    Next elem
    For i = LBound(Args2) To UBound(Args2)
        For Each elem In Args2(i)
            myAdd = myAdd + elem.Value
        Next elem
    Next i
End Function

This function could then be used in the worksheet to add multiple ranges.

For your function, there is the question of which of the ranges (or cells) that can passed to the function are 'Sessions' and which are 'Customers'.

The easiest case to deal with would be if you decided that the first range is Sessions and any subsequent ranges are Customers.

Function calculateIt(Sessions As Range, ParamArray Customers() As Variant) As Double
    'This function accepts a single Sessions range and one or more Customers
    'ranges
    Dim i As Long
    Dim sessElem As Variant
    Dim custElem As Variant
    For Each sessElem In Sessions
        'do something with sessElem.Value, the value of each
        'cell in the single range Sessions
        Debug.Print "sessElem: " & sessElem.Value
    Next sessElem
    'loop through each of the one or more ranges in Customers()
    For i = LBound(Customers) To UBound(Customers)
        'loop through the cells in the range Customers(i)
        For Each custElem In Customers(i)
            'do something with custElem.Value, the value of
            'each cell in the range Customers(i)
            Debug.Print "custElem: " & custElem.Value
         Next custElem
    Next i
End Function

If you want to include any number of Sessions ranges and any number of Customers range, then you will have to include an argument that will tell the function so that it can separate the Sessions ranges from the Customers range.

This argument could be set up as the first, numeric, argument to the function that would identify how many of the following arguments are Sessions ranges, with the remaining arguments implicitly being Customers ranges. The function's signature would then be:

Function calculateIt(numOfSessionRanges, ParamAray Args() As Variant)

Or it could be a "guard" argument that separates the Sessions ranges from the Customers ranges. Then, your code would have to test each argument to see if it was the guard. The function would look like:

Function calculateIt(ParamArray Args() As Variant)

Perhaps with a call something like:

calculateIt(sessRange1,sessRange2,...,"|",custRange1,custRange2,...)

The program logic might then be along the lines of:

Function calculateIt(ParamArray Args() As Variant) As Double
   ...
   'loop through Args
   IsSessionArg = True
   For i = lbound(Args) to UBound(Args)
       'only need to check for the type of the argument
       If TypeName(Args(i)) = "String" Then
          IsSessionArg = False
       ElseIf IsSessionArg Then
          'process Args(i) as Session range
       Else
          'process Args(i) as Customer range
       End if
   Next i
   calculateIt = <somevalue>
End Function

Answer 2

There is another way to pass multiple ranges to a function, which I think feels much cleaner for the user. When you call your function in the spreadsheet you wrap each set of ranges in brackets, for example: calculateIt( (A1,A3), (B6,B9) )

The above call assumes your two Sessions are in A1 and A3, and your two Customers are in B6 and B9.

To make this work, your function needs to loop through each of the Areas in the input ranges. For example:

Function calculateIt(Sessions As Range, Customers As Range) As Single

    ' check we passed the same number of areas
    If (Sessions.Areas.Count <> Customers.Areas.Count) Then
        calculateIt = CVErr(xlErrNA)
        Exit Function
    End If

    Dim mySession, myCustomers As Range

    ' run through each area and calculate
    For a = 1 To Sessions.Areas.Count

        Set mySession = Sessions.Areas(a)
        Set myCustomers = Customers.Areas(a)

        ' calculate them...
    Next a

End Function

The nice thing is, if you have both your inputs as a contiguous range, you can call this function just as you would a normal one, e.g. calculateIt(A1:A3, B6:B9).

Hope that helps :)

Answer 3

As I'm beginner for vba, I'm willing to get a deep knowledge of vba of how all excel in-built functions work form there back.

So as on the above question I have putted my basic efforts.

Function multi_add(a As Range, ParamArray b() As Variant) As Double

    Dim ele As Variant

    Dim i As Long

    For Each ele In a
        multi_add = a + ele.Value **- a**
    Next ele

    For i = LBound(b) To UBound(b)
        For Each ele In b(i)
            multi_add = multi_add + ele.Value
        Next ele
    Next i

End Function

- a: This is subtracted for above code cause a count doubles itself so what values you adds it will add first value twice.

Answer 4

I wrote a better function, inspired by @Ian S.

' Similar to SUMPRODUCT, but it works with non consecutive cells also, multiplying the price, in the first column, with
' the correspondent rate exchange of the second column.
' Usage:
' SUMCURRENCIES(D6:D10,E6:E10) will multiply D6 by E6, D7 by E7, and so on, finally sum all the multiplications.
' SUMCURRENCIES((D113,D117),(E113,E117)) instead will multiply D113 by E113 first, and then will add the result of D117 * E117.
Function SUMCURRENCIES(Prices As Range, ExchangeRates As Range) As Double

    ' Check if we passed the same number of areas.
    If (Prices.Areas.Count <> ExchangeRates.Areas.Count) Then
        SUMCURRENCIES = CVErr(xlErrNA)
        Exit Function
    End If

    Dim Price, ExchangeRate As Range
    Dim AreasCount, PricesCount As Integer
    
    Total = 0
    
    ' Runs through each area and multiple the value for the exchange rate.
    AreasCount = Prices.Areas.Count
    For i = 1 To AreasCount
        Set Price = Prices.Areas(i)
        Set ExchangeRate = ExchangeRates.Areas(i)
    
        If VarType(Price.Value2) = VBA.VbVarType.vbDouble Then
            Total = Price * ExchangeRate + Total
        Else
            PricesCount = Prices.Count
            For j = 1 To PricesCount
                Total = Prices(j).Value * ExchangeRates(j).Value + Total
            Next j
        End If
    Next i
    
    SUMCURRENCIES = Total
End Function