vba - Excel VBA : VBA Function Called as Formula Runs Differently?

Question

这是我的宏

Option Explicit

Function AutoFormula(blocks As Range, target As Range)
    Dim blockedArr() As String
    Dim cellValue As String
    Dim ret As String
    Dim i As Integer

    'sprawdzenie zakresu
    If (blocks.Cells.Count > 1) Then
        AutoFormula = "Tylko 1 komórka jako parametr!"
        Exit Function
    End If

     'wczytanie komorki
    cellValue = blocks.Cells(1, 1).Value

    'split
    blockedArr = Split(cellValue, ",")

    ret = "=WYSZUKAJ.PIONOWO(E7;$E$2:$G$5;3;FAŁSZ)"

    For i = LBound(blockedArr, 1) To UBound(blockedArr, 1)
        ret = ret + "+SUMA.JEŻELI(A7:A1000;" + blockedArr(i) + ";G7:G1000)"
    Next i

    target.Cells(1, 1).FormulaLocal = ret

    AutoFormula = 1
End Function

Sub auto()
    Call AutoFormula(Worksheets("reorganizacja").Range("D7"), Worksheets("reorganizacja").Range("G7"))
End Sub

当我使用 auto() sub 运行它时，它工作正常并产生所需的结果。但是，如果我在 Excel 单元格中将其称为 =AutoFormula(D7;G7) 等公式，则它不起作用。在 Excel 单元格中，我收到 #ARG 错误。当我调试宏时，它会正常运行，直到 target.Cells(1, 1).FormulaLocal = ret 行，当我跨过它时，它会立即中断函数而不完成它。有什么想法吗？

Answer 1

UDF 只能将值返回到调用它的单元格 - 它不得修改任何单元格的内容或格式，也不得修改 Excel 的操作环境。如果您尝试更改任何内容，该函数将立即终止并向调用单元返回 #VALUE 错误。 Source - Cpearson.com

在您的情况下，您正在尝试更改 Excel 中范围参数的值，例如target 是一个参数，通过 target.Cells(1, 1).FormulaLocal = ret 您要求 Excel 更改它。

---

换句话说，假设您想通过 UDF 将 A1 的值加倍。实现它的一种方法是通过以下方式:

Option Explicit

Public Function DoubleArgument(a As Range) As Double
    a = a * 2
    DoubleArgument = a
End Function

Public Sub TestMe()
    Debug.Print DoubleArgument(Range("A1"))
End Sub

但是，正如前面提到的，您无法通过 UDF 更改 Excel 中的值，因此如果您在 Excel 中运行 =DoubleArgument(A1) 将会出现错误。

---

如果您想简化示例，消除参数，请尝试以下操作:

Public Function Put5InA1()
    Range("A1") = 5
End Function

Public Sub TestMe()
    Debug.Print Put5InA1
End Sub

TestMe() 将在 A1 中生成 5，并且 UDF =Put5InA1() 将返回错误.