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

Question

我希望通过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：很抱歉我之前已经问过这个问题，但当时我没有注册，现在似乎我不能再参与其他的讨论了。

- TigrouMeow

---

你目前的方法假设水平方向上至少选择了两个单元格。如果垂直方向上选择，函数将失败。请问您实际上是想用这个函数做什么？这些信息可能会有所帮助。 - Tomalak

该函数将返回一个数据的“未知”大小（行和列）。例如，它可以是针对数据库中表的SQL请求。它将返回列的名称+相关行。 - TigrouMeow

基本上，从用户当前所在的单元格开始，您想向下和向左填充一些数据？ - Tomalak

没错！ 就像这些库一样，它们提供了从任何类型的数据源获取数据的函数。 - TigrouMeow

只需编写一个普通的 VBA 函数，通过“工具”>“宏”调用，并使用 Application.ActiveCell 确定结果放置的位置。 - barrowc

Answer 1

您需要分两步进行操作：

第一步，将您的模块更改为以下内容：

第二步，将代码更新为：

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

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

- JDunkerley

---

有趣的解决方案，使用字典来跟踪多个公式可能很好。但我的问题仍然存在：我不能要求用户在其电子表格的VBA中添加这样的代码。如果我可以直接从模块处理SheetCalculate事件，那就太完美了...但这是不可能的 :( - TigrouMeow

你的代码是否在XLA AddIn中？已经编辑响应以具有适用于应用程序的方法。仍然只能一次调用一个。 - JDunkerley

我不知道我们会这样做，这很有趣。但是这样会覆盖工作表的SheetCalculate方法，对吧？ - TigrouMeow

VBA代码嵌入XLA文件中，当应用程序完成任何工作表的计算后，会被调用。这不会覆盖WorkSheet_SheetCalculate事件，而是在其之后被调用。你可以进行一些小的改进，比如检查回调范围是否实际上在计算的工作表中，这样只有在需要时才会执行。这与我过去的做法类似，但我的事件处理程序是托管在C# COM AddIn中的，效果很好。 - JDunkerley

Answer 2

基本上，一个函数只能影响它被调用的单元格。看起来你可能需要考虑使用Worksheet_Change或Worksheet_SelectionChange事件来触发对预期范围内单元格的修改。

- Lunatik

---

最终用户将在他的电子表格中插入我的模块，我不能要求他在任何地方添加VBA代码。也许有一种方法可以从函数中注册SelectionChange事件，但我表示怀疑 :( - TigrouMeow

Answer 3

你在Excel中尝试做的事情是行不通的 - 这是设计上的问题。

但是，你可以尝试这样做：

Function FillHere()
    Redim outputArray(1 To 1, 1 To 2)
    outputArray(1, 1) = "HELLO"
    outputArray(1, 2) = "WORLD"
    FillHere = outputArray
End Function

如果您在工作表中选择了两个相邻的单元格，输入=FillHere()并按下Control+Shift+Enter（以数组公式应用），则应该会看到您想要的输出。

- Mike Woodhouse

---

我知道我可以做到这一点，但问题在于我们无法提前知道返回数组的长度。因此，我不能要求最终用户进行测试，直到找到完美的范围大小。不过我相信肯定有办法，因为有一些库（特别是市场接入库）可以很好地执行此任务。 - TigrouMeow

Answer 4

你可以通过两个步骤间接地实现这一点： 编写你的UDF，使其以足够持久的方式存储数据（例如全局数组）。 然后有一个Addin包含应用程序事件，每次计算事件后触发，查看UDFs存储的任何数据，然后重写必要的单元格（如果适当，带有覆盖警告消息）并重置存储的数据。

这样用户就不需要在他们的工作簿中编写任何代码。

我认为（但不能确定），这是Bloomberg等公司使用的技术。

- Charles Williams

---

什么是足够持久的方式？在UDF中，我只能使用静态变量...但它对其他任何东西都不可访问。或者在VBA中是否可能拥有一个Singleton类？我会尝试一下。Bloomberg使用COM库，似乎可以对Excel COM对象进行任何想做的事情，除了VBA。因此，该函数确实可以做任何事情（最重要的是，可以实时更新）。 - TigrouMeow