Excel中：传递数组到用户定义函数（VBA） [英] Excel: Passing Array to User Defined Function (VBA)
问题描述
编辑：解 - 见下
在经过阵列像{1,2,3}到UDF，我发现要考虑两件事情：
In passing arrays like {1,2,3} to a UDF I found two things to be considered:
-
的语言环境 - 看答案1.德国的系统（通常）是在列表分隔符;因此，我需要使用{1 2 3}
传递的数组显示为函数内的2维数组。因此它的第n个元素必须有针对性的myArray的（N 1 ）。忽视这些给我的#VALUE！错误。
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.
因此，短暂的选择案例-UDF可能是这样的：
Thus, a short "select case"-UDF may look like this:
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"})
给出一，B，或在结果的c取决于A1持有1，2或3个
giving "a", "b", or "c" in result depending on A1 holds 1, 2 or 3.
一个更加完备的选择案例-UDF发现这里。
A more elaborated "select case"-UDF is found here.
原题：
我想通过像数组{1,2,3,4}在Excel 2002用户定义的函数 -
不要设法找到这样做的方式。
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.
我使用的是德国版，所以，是我的小数点分隔符，并在水平（1D-）阵列分隔值 - 编辑：这是错误的 - ，wheras;分离称为从表公式函数的参数，也seprates值垂直（1D-）阵列 - 据我所知
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.
我想是这样
Function test(myArray)
Function test(myArray())
喜欢的东西。
=test({1,2,3,4})
{=test({1,2,3,4})} (with ctrl+shift+enter)
在我的表，但Excel的送花儿给人问我纠正我的公式，如=测试（{} 1,234），这是不是我想要的。
in my sheet, but Excel alway asks me to correct my formula like "=test({1,234})" which is not what I want.
如果我尝试像
=test({1;2;3;4})
=test(1,2,3,4)
=test(1;2;3;4) <- ok this would be for paramArray
作为公式中的表，我得到一个#VALUE！错误。
as formula in the sheet I get an #VALUE! error.
我不能使用的ParamArray因为在最后，我将不得不通过两个数组（可变大小）和一个单一的值作为3个参数。我需要在表格中和VBA有什么语法来传递一个数组（不定义为范围）？
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)?
感谢你在前进！
马丁Lindenlauf
Thank you in advance! Martin Lindenlauf
编辑：
我试图建立一个简写为UDF选择案例，如：
What I'm trying to build is a shorthand UDF for "Select Case", like:
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
与A1，A2，A3不是函数中定义而是通过函数调用传递像
with a1, a2, a3 not defined within the function but passed by function call like
=SelCase(A1;{"a","b","c"};{"e1","e2","e3"})
给出E1...E3取决于A1 =一，b或c的
giving "e1"..."e3" depending on A1 = "a", "b" or "c".
我能意识到这与选择（）和MATCH（），但我NEET很经常 - >想有一个漂亮的短版，并顺便说一句。我想知道我在做什么错阵列和UDF ...感谢。
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.
编辑2：
我找到了选案UDF这里工作方法。一般的问题是：如何在一种{1,2,3} -notation的数组传递给UDF（如果可能）
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).
推荐答案
这是什么意思？
Excel公式：
= sumArray（{1,2,3,4,5}，2，{} 9.5,8.7,7.3,6,5,4,3，D1：D11）
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
更新：
在code和上述公式肯定工作中使用英国区域设置与下面的设置。如果您收到错误，那么无论是从控制面板代替的符号或更新与正确的列表分隔符和小数点符号公式。
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.
这篇关于Excel中：传递数组到用户定义函数（VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
