Excel：将数组传递给用户定义函数 (VBA)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18185089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Passing Array to User Defined Function (VBA)
提问by martin.lindenlauf
edit: solution- see original question below
编辑：解决方案- 请参阅下面的原始问题
In passing arrays like {1,2,3} to a UDF I found two things to be considered:
在将 {1,2,3} 之类的数组传递给 UDF 时，我发现需要考虑两件事：
the locale - see answer 1. The list separator on a german system (usually) is ";" therefore I need to use {1 ;2 ;3}.
the passed array appears as a 2-dimensional array within the function. Therefore it's n-th element must be targeted as myArray(n, 1). Disregarding this gave me the #VALUE! error.
语言环境 - 请参阅答案 1。德国系统上的列表分隔符（通常）是“;” 因此我需要使用 {1 ; 2 ; 3}。
传递的数组在函数中显示为二维数组。因此，它的第 n 个元素必须定位为 myArray(n, 1)。无视这一点给了我#VALUE！错误。
Thus, a short "select case"-UDF may look like this:
因此，一个简短的“选择案例”-UDF 可能如下所示：
Function SelCase(a1, a2, a3)
For i = 1 To UBound(a2)
If a2(i, 1) = a1 Then SelCase = a3(i, 1)
Next
End Function
called like (german locale!):
称为（德国语言环境！）：
=SelCase(A1;{1;2;3};{"a";"b";"c"})
giving "a", "b", or "c" in result depending on A1 holds 1, 2 or 3.
根据 A1 在结果中给出“a”、“b”或“c”，结果为 1、2 或 3。
A more elaborated "select case"-UDF is found here.
在这里可以找到更详细的“选择案例”-UDF 。
original question:
原问题：
I'd like to pass an array like {1,2,3,4} to a user defined function in Excel 2002 - and don't manage to find the way to do this.
我想将 {1,2,3,4} 之类的数组传递给 Excel 2002 中的用户定义函数 - 并且无法找到执行此操作的方法。
I'm using a german version, so "," is my decimal separator and also separates values in an horizontal (1D-)array - edit: this is wrong -, wheras ";" separates arguments in functions called in formulas from sheet and also seprates values in vertical (1D-)arrays - as far as I know.
我使用的是德语版本，所以“，”是我的小数分隔符，并且还分隔水平（1D-）数组中的值- 编辑：这是错误的 -，而“;” 将公式中调用的函数中的参数从工作表中分离出来，并在垂直（1D-）数组中分离值 - 据我所知。
I tried something like
我试过类似的东西
Function test(myArray)
Function test(myArray())
with something like
像
=test({1,2,3,4})
{=test({1,2,3,4})} (with ctrl+shift+enter)
in my sheet, but Excel alway asks me to correct my formula like "=test({1,234})" which is not what I want.
在我的工作表中，但 Excel 总是要求我更正我的公式，例如“=test({1,234})”，这不是我想要的。
If I try something like
如果我尝试类似的东西
=test({1;2;3;4})
=test(1,2,3,4)
=test(1;2;3;4) <- ok this would be for paramArray
as formula in the sheet I get an #VALUE! error.
作为工作表中的公式，我得到一个#VALUE！错误。
I can't use paramArray 'cause in the end I'll have to pass two arrays (with variable size) and one single value as 3 arguments. What syntax do I need in the sheet and in VBA to pass an array (which is not defined as a range)?
我不能使用 paramArray ，因为最后我必须传递两个数组（大小可变）和一个单个值作为 3 个参数。我需要在工作表和 VBA 中使用什么语法来传递数组（未定义为范围）？
Thank you in advance! Martin Lindenlauf
先感谢您！马丁·林登劳夫
edit:
编辑：
What I'm trying to build is a shorthand UDF for "Select Case", like:
我正在尝试构建的是“Select Case”的简写 UDF，例如：
Function SelCase() As Variant
a1 = "b"
a2 = Array("a","b","c")
a3 = Array("e1","e2","e3")
For i = 0 To UBound(a2)
If a2(i) = a1 Then SelCase = a3(i)
Next
End Function
with a1, a2, a3 not defined within the function but passed by function call like
a1、a2、a3 未在函数中定义，而是通过函数调用传递，例如
=SelCase(A1;{"a","b","c"};{"e1","e2","e3"})
giving "e1"..."e3" depending on A1 = "a", "b" or "c".
根据 A1 = "a", "b" 或 "c" 给出 "e1"..."e3"。
I could realize this with CHOOSE() and MATCH() but I neet it very often -> like to have a nice "short version", and btw. I'd like to understand what I'm doing wrong with arrays and UDFs... Thanks.
我可以使用 CHOOSE() 和 MATCH() 来实现这一点，但我经常使用它-> 喜欢有一个不错的“简短版本”，顺便说一句。我想了解我在使用数组和 UDF 时做错了什么......谢谢。
edit 2:
编辑2：
I found a working approach for "select case UDF" here. The general question remains: how to pass an array in a kind of {1,2,3}-notation to an UDF (if possible).
我在这里找到了“选择案例 UDF”的工作方法。一般问题仍然存在：如何以一种 {1,2,3} 表示法将数组传递给 UDF（如果可能）。
采纳答案by user3357963
Is this what you mean?
你是这个意思吗？
Excel formula:
excel公式：
=sumArray({1,2,3,4,5},2,{9.5,8.7,7.3,6,5,4,3},D1:D11)
=sumArray({1,2,3,4,5},2,{9.5,8.7,7.3,6,5,4,3},D1:D11)
UDF:
UDF：
Function sumArray(ParamArray arr1() As Variant) As Double
sumArray = WorksheetFunction.Sum(arr1(0)) + arr1(1) + _
WorksheetFunction.Average(arr1(2)) + WorksheetFunction.Sum(arr1(3))
End Function
update:
更新：
The code and formula above definitely work using UK locale with the settings below. If you are getting errors then either substitute the symbols from the control panel or update the formula with the correct list separator and decimal symbol.
上面的代码和公式肯定可以使用英国语言环境和下面的设置。如果您遇到错误，请替换控制面板中的符号或使用正确的列表分隔符和十进制符号更新公式。
