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

User · Answer

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 :)

User · Answer

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     amp  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     amp  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    lt somevalue gt  End Function

User · Answer

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

[vba] vba pass a group of cells as range to function

Examples related to vba

Examples related to excel