如何修改VBA的eval函数使其随字符串内单元格引用变更自动重算？

你的原函数依赖Evaluate执行字符串表达式，但Excel无法自动识别字符串内部的单元格引用作为函数依赖，因此当这些单元格值变化时，函数不会自动重算。以下是两种解决方案，优先推荐高效的依赖追踪方案：

高效方案：解析单元格引用并注册依赖 #

通过正则表达式提取字符串中的单元格引用，让Excel将这些单元格标记为函数的前置依赖，只有当这些单元格值变化时才触发重算，避免无意义的重复计算。

完整代码 #

Public Function EVAL(s As String) As Variant
    Dim cellRefs As Collection
    Set cellRefs = ExtractCellReferences(s)
    
    ' 注册依赖，让Excel追踪指定单元格的变化
    Dim ref As Variant
    For Each ref In cellRefs
        On Error Resume Next ' 忽略无效引用，避免函数报错
        Application.Volatile False ' 关闭全局重算触发
        Dim targetRng As Range
        Set targetRng = Range(ref)
        If Not targetRng Is Nothing Then
            ' 仅读取单元格值，让Excel识别为依赖项
            Dim dummyVal As Variant
            dummyVal = targetRng.Value2
        End If
        On Error GoTo 0
    Next ref
    
    ' 执行表达式计算
    EVAL = Evaluate(s)
End Function

Private Function ExtractCellReferences(s As String) As Collection
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    ' 匹配规则：支持带工作表名的引用、绝对/相对引用、单元格区域
    regex.Pattern = "(?:['""\w\s]+!)?\$?[A-Z]+\$?\d+(?::\$?[A-Z]+\$?\d+)?"
    
    Dim matches As Object
    Set matches = regex.Execute(s)
    
    Set ExtractCellReferences = New Collection
    Dim match As Object
    For Each match In matches
        On Error Resume Next ' 避免添加重复引用
        ExtractCellReferences.Add match.Value, Key:=match.Value
        On Error GoTo 0
    Next match
End Function

说明 #

• 正则表达式会匹配常见的单元格引用格式（如A1、$A$1、Sheet1!B2:C5），并去重后收集为依赖项。
• 通过读取目标单元格的值，让Excel将这些单元格标记为函数的前置依赖，仅当它们的值变化时才触发重算，效率远高于全局重算。
• 加入了错误处理，避免无效引用导致函数报错。

简单但低效方案：全局触发重算 #

如果场景简单，对性能要求不高，可以直接使用Application.Volatile True强制函数在任何单元格变化时重算：

Public Function EVAL(s As String) As Variant
    Application.Volatile True ' 强制函数随任意单元格变化重算
    EVAL = Evaluate(s)
End Function

说明 #

• 实现简单，但效率低：工作表中任何单元格的变动都会触发该函数重算，数据量大时会明显拖慢Excel运行速度。

注意事项 #

• 上述正则表达式无法覆盖所有复杂引用（如结构化引用、自定义命名区域），如果需要支持这些场景，需调整正则匹配规则或添加额外的解析逻辑。
• 测试时确保Excel的自动重算功能处于开启状态（默认开启）。

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