当工作表单元格写入时，从单元格调用的 Excel VBA 公式停止处理 VBA，或遇到应用程序定义的错误

Question

我在单元格中有这个公式：

=GetData("Channel_01","Chicago")

执行此代码：

Public Function GetData(ChannelCode As String, Key As String) As String
    Dim sql As String
    Dim cmd As New ADODB.Command
    Dim outputTo As Range
    Set outputTo = Application.Caller    
    sql = "select * from ChannelData WHERE ChannelCode = ? AND Key1 = ?"
    Set cmd = getCommand(sql, ChannelCode, Key)
    Dim rs As ADODB.Recordset
    Set rs = cmd.Execute
    WritePivotRecordset ChannelCode, rs, outputTo.Offset(1, 0)
End Function

Public Sub WritePivotRecordset(ChannelCode As String, rs As ADODB.Recordset, destination As Range)
    Dim i As Integer
    '*** WHEN THIS LINE OF CODE IS REACHED AND EXECUTES, PROCESSING STOPS
    Set destination.Value = ChannelCode
    For i = 1 To rs.Fields.Count - 1    'skip first field
        destination.Offset(0, i).Value = rs.Fields(i).Name
    Next
    destination.Offset(1, 0).CopyFromRecordset rs
End Sub

问题发生在这一行：

'*** WHEN THIS LINE OF CODE IS REACHED AND EXECUTES, PROCESSING STOPS
    Set destination.Value = ChannelCode

设置此函数是否会调用电子表格重新计算，从而终止正在执行的 VBA 线程或类似的操作？我是这么想的，所以我在编写任何输出之前尝试了这个：

Application.Calculation = xlCalculationManual

但现在在同一行代码中我得到： 应用程序定义或对象定义的错误。

是否不允许从 VBA 函数写入调用 VBA 函数的同一工作表？

Answer 1

这只是 Excel 的内置行为。从工作表调用的函数（在 Excel 术语中通常称为 UDF - 用户定义函数）除了返回值之外不能对工作表执行任何操作。

不过，在上面的代码中似乎还有另一个错误。

Set destination.Value = ChannelCode 

应该会失败，因为您正在使用将对象变量设置为对象引用的语法。如果其中有错误处理程序，它会捕获错误。 （Excel 只是终止任何未处理错误的 UDF。）该行应该是：

destination.Value = ChannelCode 

但是，由于有关 UDF 对单元格没有副作用的规则，此时您的例程仍会失败。请注意，即使您确实有错误处理程序，它也不会捕获该错误。当 UDF 尝试修改单元格时，VBA 不会引发错误；它只是停止 UDF 并返回 #VALUE！错误。

在您的情况下，您似乎可以重写函数以返回包含所需值的数组，而不是尝试修改调用单元格右侧和下方的单元格。 （或者您可以从某个宏而不是 UDF 调用您的函数。）

编辑：

就返回数组而言，有一个 ADO 方法 - GetRows - 它将从 RecordSet 返回一个：

代码评论 - 我正在创建一台 Rube Goldberg 机器吗？< /a>