vba 将一组单元格作为范围传递给函数

vba pass a group of cells as range to function

提问by mrdaliri

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.

如果我传递一个范围（带:符号）作为第一个参数，它会起作用，但是如果我选择一些单元格作为它的范围（A1, A3, B6, B9），它就会失败。它只是将逗号之前的第一个单元格作为第一个参数。但我想要整个细胞。

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?

还有一件事：是否可以将一组范围作为参数传递？如何？

回答by chuff

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.

要允许在函数中使用可变数量的范围，您需要在参数列表中声明一个 ParamArray 变体数组。然后，您可以依次处理数组中的每个范围。

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

回答by Iain S

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

还有另一种方法可以将多个范围传递给一个函数，我认为这对用户来说感觉更清晰。当您在电子表格中调用您的函数时，您将每组范围括在括号中，例如：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.

上述调用假设您的两个会话位于 A1 和 A3，而您的两个客户位于 B6 和 B9。

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

为了使这项工作，您的函数需要遍历Areas输入范围中的每个。例如：

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

好消息是，如果您的两个输入都作为一个连续范围，您可以像调用普通函数一样调用此函数，例如calculateIt(A1:A3, B6:B9).

Hope that helps :)

希望有帮助:)

回答by Pankaj

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.

由于我是 vba 的初学者，我愿意深入了解 vba，了解所有 excel 内置函数如何在那里工作。

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.

- a：上面的代码减去这会导致计数加倍，因此您添加的值会将第一个值添加两次。