在 VBA 中，如何使用函数返回数组/或写入单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/846682/
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
In VBA, how to return an array / or write to cells using a function?
提问by Eric
Using this very simple function:
使用这个非常简单的函数：
Function WriteArray() as Variant
Dim array(0 To 2)
array(0) = "A"
array(1) = "B"
array(2) = "C"
WriteArray = array
End Function
I was expecting to see in result the whole array in my Excel spreadsheet, but that's not the case: I get only the first string. I know there is trick to show the whole array in the spreadsheet (by selecting a range of cells with the formula + F2 + CTRL+SHIFT+ENTER), but I would prefer VBA to handle it all.
我希望在我的 Excel 电子表格中看到整个数组的结果，但事实并非如此：我只得到第一个字符串。我知道有一个技巧可以在电子表格中显示整个数组（通过使用公式 + F2 + CTRL+SHIFT+ENTER 选择一系列单元格），但我更喜欢 VBA 来处理这一切。
I also tried to use the Application.Caller variable to write directly in the "Caller" range but the code breaks.
我还尝试使用 Application.Caller 变量直接在“Caller”范围内写入，但代码中断了。
Thanks a lot for your help!
非常感谢你的帮助！
EDIT: Here is another code I tried to use:
编辑：这是我尝试使用的另一个代码：
Function WriteArray() As Variant
Dim arr(0 To 2)
arr(0) = "A"
arr(1) = "B"
arr(2) = "C"
WriteArray = arr
Dim StartRow, i As Integer
For i = 0 To UBound(arr)
Range("A" & i).Value = arr(i)
Next
End Function
It breaks at the line "Range("A" & i).Value = arr(i)". Is my Excel broken?!
它在 "Range("A" & i).Value = arr(i)" 行中断。我的Excel坏了吗？！
回答by Eric
The following code writes the array to a range of cells beautifully:
以下代码将数组完美地写入一系列单元格：
Function WriteArray() As Variant
Dim AbcList(0 To 2) as Variant
AbcList(0) = "A"
AbcList(1) = "B"
AbcList(2) = "C"
WriteArray = AbcList
End Function
Function WriteArrayToSpreadsheet()
Dim MyArray As Variant
MyArray = WriteArray()
Dim StartRow, i As Integer
StartRow = 1
For i = 0 To UBound(MyArray)
Range("A" & i + StartRow).Value = MyArray(i)
Next
End Function
That being said, I'd like to see the part of the code where you're actually trying to get it onto the spreadsheet, not where you build the array. Then I can help you out!
话虽如此，我想看看您实际尝试将其放入电子表格的代码部分，而不是您构建数组的位置。那我可以帮你！
回答by JDunkerley
You are not allowed to write to non-caller cells directly from a worksheet function in Excel.
不允许直接从 Excel 中的工作表函数写入非调用方单元格。
If you want to use an array function (using the Shift-Ctrl-Enter) you need to change your code to:
如果要使用数组函数（使用 Shift-Ctrl-Enter），则需要将代码更改为：
Function WriteArray() As Variant
Dim arr(0 To 2, 0 To 1)
arr(0, 0) = "A"
arr(1, 0) = "B"
arr(2, 0) = "C"
WriteArray = arr
End Function
If you want to write outside of the calling cells you would need to implement some form of callback which would use automation to write to the other cells. This is way more complicated and much more likely to break!
如果您想在调用单元格之外写入，则需要实现某种形式的回调，该回调函数将使用自动化写入其他单元格。这要复杂得多，而且更容易破裂！
回答by JFS
The secret is to define a two-dimensional array. The two dimensions of the array is simply the range which needs to be defined for a dataset. The first array dimension is the row offset and the second dimension is the column offset.
秘诀是定义一个二维数组。数组的二维只是需要为数据集定义的范围。第一个数组维度是行偏移量，第二个维度是列偏移量。
In you example the second dimension is just not "used":
在你的例子中，第二维只是没有“使用”：
Sub Ente()
Dim myArray(0 To 3, 0) As String
myArray(0, 0) = "A"
myArray(1, 0) = "B"
myArray(2, 0) = "C"
Range("B7:B" & UBound(myArray) + 6) = myArray
End Sub
So, no loops necessary! Simple and fast.
所以，不需要循环！简单快速。
回答by Roger Barreto
The best workaround I made so far was creating a procedure the get your range reference and your array and use it as a start point to show your data horizontaly or vertically...
到目前为止，我所做的最好的解决方法是创建一个程序来获取您的范围参考和您的数组，并将其用作起点来水平或垂直显示您的数据......
As follows:
如下：
Sub LoadArray(ByRef oRange, ByRef vArray)
Dim I
For I = 0 To UBound(vArray)
oRange.Offset(I, 0).Value = vArray(I)
Next
End Sub
'How to call:
Dim anyArray
anyArray = Array(1,2,3)
Call LoadArray(Range("anyRange"), anyArray)
Hope it helps.
希望能帮助到你。
