如何通过 VBA 函数填充 Excel 工作表中的单元格？

我只想通过 VBA 函数填充电子表格中的单元格。例如，我想在单元格中输入 =FillHere()，结果我将在一些单元格中填充一些数据。

我尝试过这样的功能：

Function FillHere()
  Dim rngCaller As Range
  Set rngCaller = Application.Caller
  rngCaller.Cells(1, 1) = "HELLO"
  rngCaller.Cells(1, 2) = "WORLD"
End Function

当我尝试修改范围时它就会中断。然后我尝试了这个（即使这不是我真正想要的行为）：

Function FillHere()
    Dim rngCaller As Range
    Cells(1, 1) = "HELLO"
    Cells(1, 2) = "WORLD"
End Function

这也不起作用。但如果我使用 F5 从 VBA 启动此函数，它就会起作用！似乎在调用函数时无法修改电子表格上的任何内容...但有些库会这样做...

我还尝试（事实上这是我的第一个想法）从函数返回一个数组。问题是我只获取数组中的第一个元素（有一个技巧意味着使用左上角的公式+ F2 + CTRL-SHIFT-ENTER 选择整个区域，但这意味着用户需要知道通过提前数组的大小）。

我真的被这个问题困扰了。我不是最终的最终用户，所以我需要一些非常易于使用的东西，最好是完全没有参数。

PS：很抱歉我已经问过这个问题了，但当时我还没有注册，看来我不能再参与其他线程了。

您需要分两步执行此操作：

将您的模块更改为：

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

然后为了调用 Sub，请进入 VBA 中的 ThisWorkbook 区域并添加如下内容：

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call WriteBack
End Sub

其作用是返回左上角单元格的值，然后在计算完成后填充其余单元格。我编写此代码的方式假设一次只会调用一个 FillHere 函数。如果您想要同时重新计算多个变量，那么您将需要一组更复杂的全局变量。

需要警告的是，当它填充其他单元格时，它不会关心它会覆盖什么。

编辑： 如果您想在 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

这将连接应用程序级别计算。