VBA自定义Eval函数偶发#NAME?错误，如何解析该异常行为？

相关代码与配置 #

VBA自定义函数 #

Public Function eval(s As String) As Variant
    Application.Volatile
    eval = Evaluate(s)
End Function

待计算的字符串公式 #

IF(@size<=10,100,IF(AND(@size>10,@size<=20),200,""))

公式存储表格（table1） #

调用表格及公式 #

预期计算结果 #

异常现象 #

功能基本可用，但偶发#NAME?错误，例如双击行列分隔线调整大小时会触发。重新进入含EVAL函数的单元格按回车，错误即可恢复正常。已添加Application.Volatile，本以为函数会在工作簿有任何变更时重新计算，无法理解该异常原因。

注：水果和尺寸为命名范围，因此@引用有效。

问题分析 #

1. Application.Volatile的触发限制：
   该声明仅让函数在单元格内容变更、公式依赖项变更时触发重算，但Excel的部分UI操作（如调整行列大小）不会标记所有volatile函数为需要重算，尤其是当函数依赖的命名范围未被关联到这类操作时。

2. Evaluate的上下文丢失：
   自定义函数中直接使用Evaluate时，默认计算上下文是当前活动工作表，而非调用函数的单元格所在工作表。在某些后台计算场景（如调整行列大小触发的轻量重算）中，上下文可能未正确绑定，导致@size这类结构化引用无法被解析，从而抛出#NAME?错误。

解决方案 #

方案1：修正Evaluate的计算上下文 #

修改VBA函数，明确使用调用函数的单元格所在工作表执行Evaluate，确保上下文正确：

Public Function eval(s As String) As Variant
    Application.Volatile
    ' 绑定到调用单元格所在的工作表执行计算
    eval = Application.Caller.Worksheet.Evaluate(s)
End Function

方案2：强制全量重算（可选） #

如果仍有偶发问题，可在工作表事件中添加强制全量重算，确保UI操作后所有公式都更新（注意：会影响性能，按需使用）：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CalculateFull
End Sub

方案3：无VBA替代方案（Excel 365+） #

放弃字符串公式存储，直接在表格中使用动态数组逻辑替代，例如将table1的价格列改为条件判断，调用时直接引用：

=XLOOKUP(@水果,table1[水果],IF(table1[尺寸]<=10,100,IF(AND(table1[尺寸]>10,table1[尺寸]<=20),200,"")),,0)

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