vba Excel VBA中的数组函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10290591/
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
Array function in Excel VBA
提问by Joe
I have a function in VBA that generates an array of strings. It works fine when called from another VBA function, but not when called from the worksheet.
我在 VBA 中有一个函数可以生成一个字符串数组。从另一个 VBA 函数调用时它可以正常工作，但从工作表调用时则不能。
Here's how it should be used:
以下是它的使用方法：
- select A1:A3
- write in the formula bar
=Test(), then hitCtrl-Shift-Enterto make it an array function - A1 should contain
A, A2 should containB, and A3 should containC
- 选择 A1:A3
- 在公式栏中写入
=Test()，然后点击Ctrl-Shift-Enter使其成为数组函数 - A1 应包含
A，A2 应包含B，A3 应包含C
When I actually try this, it puts Ain all three cells of the array. How can I get the data returned by Testinto the different cells of the array?
当我实际尝试此操作时，它会放入A数组的所有三个单元格。如何将返回的数据Test放入数组的不同单元格中？
For those who'd like to see it, here's the code of the function. Keep in mind, the function works fine when called from other functions.
对于那些想要查看它的人，这是该函数的代码。请记住，当从其他函数调用时，该函数可以正常工作。
Function Test() As String()
Dim a(1 To 3) As String
a(1) = "A"
a(2) = "B"
a(3) = "C"
Test = a
End Function
回答by Mike T
You function works fine if you transpose the data. To do this, you need Variant()instead of String():
如果您转置数据，您的功能就可以正常工作。为此，您需要Variant()代替String()：
Function Test() As Variant()
Dim a(1 To 3) As Variant
a(1) = "A"
a(2) = "B"
a(3) = "C"
Test = Application.Transpose(a)
End Function
回答by andy holaday
You might want to check Chip Pearson's advice on returning an array from a UDF.
您可能想查看Chip Pearson 关于从 UDF 返回数组的建议。
This is my first response here. I hope this is not inappropriate.
这是我在这里的第一反应。我希望这不是不合适的。
回答by Charles Williams
I find it simplest to always work with 2-dimensional arrays, then you don't need transpose etc
我发现始终使用二维数组是最简单的，然后您就不需要转置等
Function Test() As String()
Dim a(1 To 3, 1) As String
a(1, 1) = "A"
a(2, 1) = "B"
a(3, 1) = "C"
Test = a
End Function
回答by Joe
Just figured out the problem -- the first dimension of an array going from VBA to Excel is horizontal, not vertical. To see the output of Test, put the array function =Test()in A1:C1(not A1:A3).
刚刚解决了问题——从 VBA 到 Excel 的数组的第一个维度是水平的，而不是垂直的。要查看 的输出Test，请将数组函数=Test()放在A1:C1（而不是 A1:A3）中。
