作为其他单元格数据源的VBA函数能否写入单元格？

操作步骤与现象 #

1. 环境说明：Excel 2019，单工作表工作簿，已从信任位置打开并启用宏。

2. 初始代码与测试：
   创建VBA模块并编写代码：

   Option Explicit
   
   Public Function Foo(ByVal Bar As Integer) As String
     Foo = "John"
   End Function
   

   在A1单元格输入公式=Foo(1)，按CTRL-ALT-F9强制计算后，A1显示John。

3. 读取单元格的测试：
   修改VBA函数为：

   Public Function Foo(ByVal Bar As Integer) As String
     Dim s As Worksheet
     Set s = ActiveWorksheet
     MsgBox s.Range("B1").Value
     Foo = "John"
   End Function
   

   在B1输入Mary，重新计算时弹出显示Mary的消息框，A1仍显示John。

4. 写入单元格的异常现象：
   再次修改VBA函数为：

   Public Function Foo(ByVal Bar As Integer) As String
     Dim s As Worksheet
     Set s = ActiveWorksheet
     s.Range("C1").Value = "April"
     Foo = "John"
   End Function
   

   预期重新计算后C1显示April、A1显示John，但实际：

   • A1出现#VALUE!错误，提示“公式中使用的值数据类型错误”；
   • C1无内容，调试时写入单元格的代码未执行且无报错；
   • 在VBA编辑器“立即窗口”执行? Foo(1)时，C1正常显示April（已确认C1未受保护且格式为文本）。

提问 #

Excel是否会阻止作为其他单元格数据源的VBA函数写入单元格？

答案 #

是的，Excel会阻止工作表单元格公式调用的自定义VBA函数修改工作表单元格内容。

这是因为Excel的自定义函数（UDF）被设计为纯函数——仅允许根据输入参数返回结果，不能产生修改单元格、改变工作表状态这类“副作用”。当UDF被单元格公式调用时，Excel会在受限的执行环境中运行它，任何试图修改工作表的操作都会被静默拦截，直接导致函数执行失败并返回#VALUE!错误。

而在VBA编辑器立即窗口调用函数时，函数处于不受限制的VBA执行环境，不属于工作表公式计算的上下文，因此可以正常修改单元格内容。

如果需要实现“计算时修改其他单元格”的需求，不能通过单元格公式调用UDF的方式实现，推荐改用以下方案：

• 绑定工作表的Calculate事件，在计算完成后执行修改操作；
• 使用按钮触发的宏来完成计算与单元格修改；
• 将逻辑整合到独立的VBA过程中，而非作为单元格公式的UDF。

内容的提问来源于stack exchange，提问作者Binarus