如何在Excel VBA中检测公式单元格结果变化并触发版本号更新

你当前使用的Workbook_SheetChange事件仅能检测单元格自身内容（手动输入值、公式文本修改等）的变更，公式依赖其他单元格变动导致的计算结果更新不会触发该事件，可通过搭配Workbook_SheetCalculate事件实现需求，具体实现方式如下：

• 第一步：在ThisWorkbook代码模块的顶部声明模块级字典对象，用于存储待监控单元格的历史值。需先在VBA编辑器的「工具」-「引用」中勾选Microsoft Scripting Runtime：

Dim oldCellValues As Dictionary

• 第二步：添加Workbook_Open事件，初始化字典并预存所有符合监控条件的单元格初始值：

Private Sub Workbook_Open()
    Set oldCellValues = New Dictionary
    Dim sh As Worksheet, cell As Range
    ' 遍历所有工作表的已使用单元格
    For Each sh In ThisWorkbook.Sheets
        For Each cell In sh.UsedRange
            If Not cell.Comment Is Nothing Then
                If cell.Comment.Text Like "*" & PropertyReferenceHeader & "*" Then
                    ' 以工作表名+单元格地址作为唯一键存储历史值
                    oldCellValues(sh.Name & "!" & cell.Address) = cell.Value
                End If
            End If
        Next
    Next
End Sub

• 第三步：添加Workbook_SheetCalculate事件，每次表格重算时对比值的变化，触发版本更新：

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim cell As Range, cellKey As String
    For Each cell In Sh.UsedRange
        If Not cell.Comment Is Nothing Then
            If cell.Comment.Text Like "*" & PropertyReferenceHeader & "*" Then
                cellKey = Sh.Name & "!" & cell.Address
                ' 对比当前值与历史值
                If oldCellValues.Exists(cellKey) Then
                    If cell.Value <> oldCellValues(cellKey) Then
                        RewriteCellComment cell, False, True
                        ' 更新存储的历史值
                        oldCellValues(cellKey) = cell.Value
                    End If
                Else
                    ' 新增符合条件的单元格直接存入字典
                    oldCellValues(cellKey) = cell.Value
                End If
            End If
        End If
    Next
End Sub

若表格数据量较大，每次遍历全部已使用单元格可能存在性能问题，可优化为单独维护待监控单元格列表，避免全表遍历。如果监控的单元格包含浮点数值，建议添加误差容忍判断，避免因浮点精度问题误触发版本更新。

内容的提问来源于stack exchange，提问作者Enrique González