如何在 vba 函数中返回 array() 以在单元格中使用它数组公式（矩阵公式）：用于在多个单元格中拆分文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14875480/
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
How to return array() in vba function to use it in cells Array formulas (matricial formula) : for split texte in multi cells
提问by Alban
I'm writing a function in VBA to use in excel formula, it's ok if my function return a single value:
我正在 VBA 中编写一个函数以在 excel 公式中使用，如果我的函数返回单个值就可以了：
=MYVALUE(A1)
Now I wrote another function which returns an Array(1,2,3,4,...) and I replace my excel formula by Array formula:
现在我写了另一个函数，它返回一个 Array(1,2,3,4,...) 并且我用 Array 公式替换了我的 excel 公式：
{=MYARRAY(A1)}
But when I stretch the formula, all cells display the first value of my array. why ?
但是当我拉伸公式时，所有单元格都显示数组的第一个值。为什么 ？
Here is my VBA source code (complement.xlam) :
这是我的 VBA 源代码 (complement.xlam)：
Function MYVALUE(x as integer)
MYVALUE = 123
End Eunction
Function MYARRAY(x as integer)
MYARRAY = Array(10,20,30)
End Eunction
回答by Alban
Array formulas need use like that
数组公式需要这样使用
my VBA to split text in multi cells
我的 VBA 在多个单元格中拆分文本
Function EXPLODE_V(texte As String, delimiter As String)
EXPLODE_V = Application.WorksheetFunction.Transpose(Split(texte, delimiter))
End Function
Function EXPLODE_H(texte As String, delimiter As String)
EXPLODE_H = Split(texte, delimiter)
End Function
- Select region
C3:C7this define the vector direction. - Press F2to edit on the spot and type the following formula:
=EXPLODE_V($B$3;" ") Press CTRL+SHIFT+ENTER( INSTEAD of usual ENTER) - this will define an ARRAY formula and will result in
{=EXPLODE_V($B$3;" ")}brackets around it (but do NOT type them manually!).
- 选择
C3:C7定义矢量方向的区域。 - 按F2即可当场编辑并输入以下公式：
=EXPLODE_V($B$3;" ") 按CTRL+ SHIFT+ ENTER（INSTEAD of 通常ENTER） - 这将定义一个 ARRAY 公式并
{=EXPLODE_V($B$3;" ")}在它周围产生括号（但不要手动输入它们！）。
回答by InContext
the reason is that your vector actually is being displayed horizontally, if you create an array formula horizontally you will get your numbers. For you to have a vertical vector use the following:
原因是您的矢量实际上是水平显示的，如果您水平创建数组公式，您将获得数字。要获得垂直向量，请使用以下内容：
Function MYARRAY(x As Integer)
MYARRAY = Application.WorksheetFunction.Transpose(Array(10, 20, 30))
End Function
回答by David Zemens
A cell won't display an array, you would have to translate that array to a range/array of cells on the sheet. Alternatively, you could try converting the array to a delimited string, which may work as long as you aren't using very large arrays.
单元格不会显示数组，您必须将该数组转换为工作表上的单元格范围/数组。或者，您可以尝试将数组转换为分隔字符串，只要您不使用非常大的数组，它就可以工作。
Public Function MYARRAY(x As Integer)
Dim tmpArray() As Variant
Dim i As Long
Dim arrayString As String
tmpArray = Array(10, 20, 30)
For i = LBound(tmpArray) To UBound(tmpArray)
If arrayString = vbNullString Then
arrayString = tmpArray(i)
Else:
arrayString = arrayString & ", " & tmpArray(i)
End If
Next
MYARRAY = "{" & arrayString & "}"
End Function
