如何从 VBA 函数填充 Excel 工作表中的单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/847301/
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 fill-up cells within a Excel worksheet from a VBA function?
提问by TigrouMeow
I simply want to fill-up cells in my spreadsheet from a VBA function. By example, I would like to type =FillHere() in a cell, and in result I will have a few cells filled-up with some data.
我只想从 VBA 函数填充电子表格中的单元格。例如，我想在一个单元格中键入 =FillHere()，结果我将有一些单元格填充了一些数据。
I tried with such a function:
我试过这样的功能：
Function FillHere()
Dim rngCaller As Range
Set rngCaller = Application.Caller
rngCaller.Cells(1, 1) = "HELLO"
rngCaller.Cells(1, 2) = "WORLD"
End Function
It breaks as soon as I try to modify the range. Then I tried this (even it's not really the behavior I'm looking for):
一旦我尝试修改范围，它就会中断。然后我尝试了这个（即使它不是我正在寻找的行为）：
Function FillHere()
Dim rngCaller As Range
Cells(1, 1) = "HELLO"
Cells(1, 2) = "WORLD"
End Function
This is not working neither. But it works if I start this function from VBA using F5!It seems it's not possible to modify anything on the spreadsheet while calling a function... some libraries do that though...
这也不行。但是，如果我使用 F5 从 VBA 启动此功能，它会起作用！似乎无法在调用函数时修改电子表格上的任何内容……尽管有些库会这样做……
I also tried (in fact it was my first idea) to return a array from the function. The problem is that I only get the first element in the array (there is a trick that implies to select a whole area with the formula at the top left corner + F2 + CTRL-SHIFT-ENTER, but that means the user needs to know by advance the size of the array).
我还尝试（实际上这是我的第一个想法）从函数返回一个数组。问题是我只得到数组中的第一个元素（有一个技巧意味着用左上角的公式 + F2 + CTRL-SHIFT-ENTER 选择整个区域，但这意味着用户需要知道通过提前数组的大小）。
I'm really stuck with this problem. I'm not the final end-user so I need something very easy to use, with, preferably, no argument at all.
我真的被这个问题困住了。我不是最终的最终用户，所以我需要一些非常易于使用的东西，最好是完全没有争论。
PS: I'm sorry I asked this question already, but I wasn't registered at that time and it seems that I can't participate to the other thread anymore.
PS：抱歉我已经问过这个问题了，但是我当时没有注册，看来我不能再参与其他线程了。
采纳答案by JDunkerley
You will need to do this in two steps:
您需要分两步执行此操作：
Change your module to be something like:
将您的模块更改为：
Dim lastCall As Variant
Dim lastOutput() As Variant
Function FillHere()
Dim outputArray() As Variant
ReDim outputArray(1 To 1, 1 To 2)
outputArray(1, 1) = "HELLO"
outputArray(1, 2) = "WORLD"
lastOutput = outputArray
Set lastCall = Application.Caller
FillHere = outputArray(1, 1)
End Function
Public Sub WriteBack()
If IsEmpty(lastCall) Then Exit Sub
If lastCall Is Nothing Then Exit Sub
For i = 1 To UBound(lastOutput, 1)
For j = 1 To UBound(lastOutput, 2)
If (i <> 1 Or j <> 1) Then
lastCall.Cells(i, j).Value = lastOutput(i, j)
End If
Next
Next
Set lastCall = Nothing
End Sub
Then in order to call the Sub go into the ThisWorkbook area in VBA and add something like:
然后为了调用 Sub 进入 VBA 中的 ThisWorkbook 区域并添加如下内容：
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call WriteBack
End Sub
What this does is return the value of the topleft cell and then after calculation completes populates the rest. The way I wrote this it assumes only one FillHere function will be called at a time. If you want to have multiple ones which recalculate at the same time then you will need a more complicated set of global variables.
这样做是返回左上角单元格的值，然后在计算完成后填充其余单元格。我写这篇文章的方式是假设一次只会调用一个 FillHere 函数。如果您想要同时重新计算多个变量，那么您将需要一组更复杂的全局变量。
One word of warning is that this will not care what it overwrites when it populates the other cells.
一个警告是，当它填充其他单元格时，它不会关心它会覆盖什么。
Edit: If you want to do this on a Application wide basis in an XLA. The code for the ThisWorkbook area should be something like:
编辑：如果您想在 XLA 中在应用程序范围内执行此操作。ThisWorkbook 区域的代码应该类似于：
Private WithEvents App As Application
Private Sub App_SheetCalculate(ByVal Sh As Object)
Call WriteBack
End Sub
Private Sub Workbook_Open()
Set App = Application
End Sub
This will wire up the Application Level calculation.
这将连接应用程序级别计算。
回答by Mike Woodhouse
What you're trying to do won't work in Excel - this is by design.
您尝试执行的操作在 Excel 中不起作用 - 这是设计使然。
You cando this, though:
不过，您可以这样做：
Function FillHere()
Redim outputArray(1 To 1, 1 To 2)
outputArray(1, 1) = "HELLO"
outputArray(1, 2) = "WORLD"
FillHere = outputArray
End Function
If you then select two adjacent cells in your worksheet, enter =FillHere()and press Control+Shift+Enter (to apply as an array formula) then you should see your desired output.
如果您随后在工作表中选择两个相邻的单元格，输入=FillHere()并按 Control+Shift+Enter（作为数组公式应用），那么您应该会看到所需的输出。
回答by Lunatik
Fundamentally, a function can only affect the cell it is called from. It sounds like you may need to look at using the Worksheet_Changeor Worksheet_SelectionChangeeventsto trigger the modification of cells in the intended range.
从根本上说，一个函数只能影响它被调用的单元格。听起来您可能需要考虑使用Worksheet_Change或Worksheet_SelectionChange事件来触发对预期范围内的单元格的修改。
回答by Charles Williams
You can do this indirectly using a 2-stage process: Write your UDF so that it stores data in a sufficiently persistent way (for example global arrrays). then have an Addin that contains application events that fire after each calculation event, looks at any data stored by the UDFs and then rewrites the neccessary cells (with warning messages about overwrite if appropriate) and reset the stored data.
您可以使用 2 阶段过程间接执行此操作：编写您的 UDF，以便它以足够持久的方式存储数据（例如全局数组）。然后有一个包含在每个计算事件后触发的应用程序事件的插件，查看 UDF 存储的任何数据，然后重写必要的单元格（如果合适，带有关于覆盖的警告消息）并重置存储的数据。
This way the user does not need to have any code in their workbook.
这样用户就不需要在他们的工作簿中有任何代码。
I think (but do not know for sure) that this is the technique used by Bloomberg etc.
我认为（但不确定）这是彭博等使用的技术。
