当将工作表单元格写入单元格时，从单元格调用的Excel VBA公式会停止处理VBA或遇到App定义的错误 [英] Excel VBA formula, called from a cell, stops processing VBA, or encounters an App-Defined error, when a worksheet cell is written to

问题描述

我在一个单元格中有这个公式:

I have this formula in a cell:

=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线程或类似内容?我是这么认为的，所以我在写任何输出之前都尝试过:

Is setting this invoking a spreadsheet recalc, which terminates the executing VBA thread or something like that? I thought so, so I tried this before writing any output:

Application.Calculation = xlCalculationManual

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

But now on that same line of code I get: Application-defined or object-defined error.

是否正在从VBA函数写入调用VBA函数的同一工作表中，只是不允许这样做?

Is writing from a VBA function to the same worksheet from which the VBA function is called, just not allowed?

推荐答案

这只是Excel的内置行为.从工作表中调用的函数(在Excel术语中通常称为UDF-用户定义的函数)除了返回值外对工作表无能为力.

This is just the built-in behavior of Excel. Functions called from the worksheet (often called UDFs in Excel terminology - user-defined functions) can't do anything to the worksheet other than return a value.

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

In your code above, there appears to be another error, though.

Set destination.Value = ChannelCode 

应该失败，因为您正在使用将对象变量设置为对象引用的语法.如果那里有错误处理程序，它将捕获错误. (Excel会终止任何使错误无法解决的UDF.)该行应为:

should fail because you're using the syntax for setting an object variable to an object reference. If you had an error handler in there it would catch the error. (Excel just terminates any UDF that lets an error go unhandled.) The line should be:

destination.Value = ChannelCode 

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

However, your routine will still fail at that point because of the rule about UDFs not having side effects on cells. Note that even if you did have an error handler, it wouldn't catch that. VBA doesn't raise an error when a UDF tries to modify a cell; it just stops the UDF and returns a #VALUE! error.

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

In your case, it looks like you can rewrite your function to return an array containing the values you want, instead of trying to modify cells to the right and below of the calling cell. (Or you can call your function from some macro instead of a UDF.)

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

As far as returning the array, there is an ADO method - GetRows - that will return one from a RecordSet:

代码批判-我是在创建Rube Goldberg机器?

这篇关于当将工作表单元格写入单元格时，从单元格调用的Excel VBA公式会停止处理VBA或遇到App定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！