Excel：将数组传递给用户定义函数（VBA） [英] Excel: Passing Array to User Defined Function (VBA)

问题描述

编辑：解决方案 - 请参阅下面的原始问题

在将{1,2,3}的数组传递给UDF时，我发现两个要考虑的事项：

• 语言环境 - 见答案1.德语系统上的列表分隔符（通常为） 因此，我需要使用{1 ; 2 ; 3}。




• 功能内的二维数组。因此，它的第n个元素必须定位为myArray（n， 1 ）。不顾这个给了我#VALUE！

因此，一个简短的选择案例-UDF可能如下所示：

 函数SelCase（a1，a2，a3）
对于i = 1 To UBound（a2）
如果a2（i，1 ）= a1 Then SelCase = a3（i，1）
下一个
结束函数
  

被称为（德语语言环境）：

  = SelCase（A1; {1; 2; 3}; {a ;b;c}）
  

给予a，b c的结果取决于A1保持1,2或3。

更详细的选择案例-UDF被发现此处。

---

原始问题

我想传递一个像{1,2,3,4}的数组到用户定义的函数Excel 2002 -
，并没有设法找到这样做。

我使用的是德语版本，所以，是我的十进制分隔符，也可以分离一个水平的值ntal（1D-）数组 - 编辑：这是错误的 - ，wheras;在函数中使用函数调用函数中的参数，也可以在垂直（1D-）数组中对值进行分解，只要我知道。

我尝试过像

 函数测试（myArray）
函数测试（myArray（））
  

与

  = test（{1,2， 3,4}）
 {= test（{1,2,3,4}）}（用ctrl + shift + enter）
  

在我的工作表中，但Excel还要求我更正我的公式，如= test（{1,234}）这不是我想要的。

如果我尝试像

  = test（{1; 2; 3; 4}）
 = test（1,2,3,4）
 = test（1; 2; 3; 4）<  -  ok这将是对于paramArray 
  pre> 
 
 
作为表格中的公式，我得到一个#VALUE！错误。
 
 
 
我不能使用paramArray，因为最后我必须传递两个数组（具有可变大小）和一个单个值作为3个参数。我需要在表格和VBA中传递一个数组（没有定义为范围）的语法？ 
 
 
 
提前谢谢！ 
 Martin Lindenlauf 
 
 
 
 编辑： 
 
 
 
构建是Select Case的缩写UDF，如：
 函数SelCase（）As Variant 
 a1 = b
 a2 = Array（a，b，c）
 a3 = Array（e1，e2，e3）
对于i = 0到UBound（a2）
如果a2（i）= a1则SelCase = a3（i）
下一个
结束函数
  
 
 
与a1，a2，a3未定义在函数内，但通过函数调用如
  = SelCase（A1; {a，b，c}; {e1，e2，e3}）
  / pre> 
 
 
根据A1 =a，b或c给e1...e3。
 
 
 
我可以通过CHOOSE（）和MATCH（）来实现这一点，但是我经常很喜欢 - >喜欢有一个很好的短版本和btw。我想了解我在数组和UDF中做错了...谢谢。
 
 
 
 编辑2： 
 
 
 
我发现了一个select case UDF的工作方法这里。一般的问题仍然是：如何将一个{1,2,3} -notation的数组传递给UDF（如果可能的话）。
解决方案
这是什么意思？
 
 
 
 Excel公式：
 
 
 
  = sumArray（{1,2,3,4,5}，2，{9.5,8.7,7.3,6,5,4,3}，D1：D11） 
 
 
 
 UDF：
 函数sumArray（ParamArray arr1（）As Variant）As Double 
 
 sumArray = WorksheetFunction.Sum（arr1（0））+ arr1（1）+ _ 
 WorksheetFunction.Average（arr1（2））+ WorksheetFunction.Sum（arr1（3））
 
结束功能
  
更新：
 
 
 
上面的代码和公式绝对可以使用以下设置的英国语言环境。如果您收到错误，则可以使用控制面板中的符号替换或使用正确的列表分隔符和十进制符号更新公式。
 
 
 
  
 
edit: solution - see original question below

In passing arrays like {1,2,3} to a UDF I found two things to be considered:


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.


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"})
giving "a", "b", or "c" in result depending on A1 holds 1, 2 or 3.

A more elaborated "select case"-UDF is found here.



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.

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.

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.

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.

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

Thank you in advance!
Martin Lindenlauf

edit:

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
with a1, a2, a3 not defined within the function but passed by function call like
=SelCase(A1;{"a","b","c"};{"e1","e2","e3"})
giving "e1"..."e3" depending on A1 = "a", "b" or "c".

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.

edit 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).
解决方案
Is this what you mean?

Excel formula:

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



                        
这篇关于Excel：将数组传递给用户定义函数（VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！