VBA写入工作表单元格触发Error 1004错误，如何解决？

编写了一段VBA代码用于向工作表单元格写入值，但始终触发Error 1004错误，尝试多种解决方案仍未解决，期望在A1单元格查看写入测试的结果。

Public Function test(a, b As Integer) As Integer
    test = a * b
    Call WriteCell(test)
End Function

Public Sub WriteCell(a As Integer)
    On Error GoTo myerr
    
    Dim ws As Worksheet
    Dim TestCell As Range
    Set ws = Application.ActiveSheet
    Set TestCell = ws.Range("A1")
    TestCell.Value = "Test" & a
    
    Exit Sub
    
    myerr:
    Debug.Print Err.Number, Err.Description
    Stop
    
End Sub

核心原因 #

这段代码的问题出在工作表自定义函数（UDF）无法直接修改工作表单元格内容。test是一个自定义函数，当你在工作表单元格中输入=test(x,y)调用它时，Excel的安全机制会阻止UDF执行修改工作表的操作（包括写入值、设置格式等），这就是触发Error 1004的根本原因。

解决方法 #

根据需求，有两种可行的修正方向：

方向1：将自定义函数改为普通子程序调用

如果不需要在单元格中作为公式使用，直接通过VBA编辑器运行或绑定按钮触发：

Public Sub test(a As Integer, b As Integer)
    Dim result As Integer
    result = a * b
    Call WriteCell(result)
End Sub

Public Sub WriteCell(a As Integer)
    On Error GoTo myerr
    
    Dim ws As Worksheet
    Dim TestCell As Range
    Set ws = Application.ActiveSheet
    Set TestCell = ws.Range("A1")
    TestCell.Value = "Test" & a
    
    Exit Sub
    
myerr:
    Debug.Print Err.Number, Err.Description
    Stop
    
End Sub

调用时可在VBA编辑器中直接运行test子程序，或给它绑定一个工作表按钮。

方向2：间接方式修改单元格（不推荐，仅作参考）

如果必须保留UDF形式，可以通过Application.OnTime延迟执行写入操作绕开限制，但这种方法容易引发意外问题：

Public Function test(a, b As Integer) As Integer
    test = a * b
    ' 延迟0秒执行写入，让UDF先完成计算
    Application.OnTime Now, "'WriteCell " & test & "'"
End Function

Public Sub WriteCell(a As Integer)
    On Error GoTo myerr
    
    Dim ws As Worksheet
    Dim TestCell As Range
    Set ws = Application.ActiveSheet
    Set TestCell = ws.Range("A1")
    TestCell.Value = "Test" & a
    
    Exit Sub
    
myerr:
    Debug.Print Err.Number, Err.Description
    Stop
    
End Sub

注意：这种方法会在UDF每次重新计算时触发写入，可能造成数据覆盖或性能问题，仅适用于特殊场景。

内容的提问来源于stack exchange，提问作者Dario B.