Excel：将数组传递给用户定义的函数（VBA）

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

  语言环境 - 请参阅答案1.德语系统上的列表分隔符（通常）是“;”因此我需要使用{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.

  传递的数组在函数中显示为二维数组。因此，它的第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保持1,2或3，在结果中给出“a”，“b”或“c”。

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-）数组中的值分开 - 编辑：这是错误的 - ，wheras“;”如我所知，在公式中从sheet中调用函数中的参数，并在垂直（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（如果可能的话）。

1 个解决方案

#1

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.

上面的代码和公式肯定使用英国语言​​环境以及下面的设置。如果出现错误，则替换控制面板中的符号或使用正确的列表分隔符和小数符号更新公式。