Excel审计追踪VBA优化：实现单元格变更原值准确记录

问题根源 #

原代码存在三个核心问题导致历史值被记录为"0"：

• PreviousValue被声明为String类型，无法兼容单元格的多种数据类型，且代码末尾强制赋值为0，清空了之前捕获的真实值
• SheetChange事件中错误处理逻辑混乱，错误地将异常情况直接映射为0
• 类型判断逻辑冗余，干扰了真实值的记录

修正后的完整代码 #

Dim PreviousValue As Variant ' 改用Variant兼容所有单元格数据类型
Dim CurrentValue As Variant

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    LogChange "Closed " & ActiveWorkbook.Name
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    LogChange "Sheet Printed: " & ActiveWorkbook.Name
End Sub

Private Sub Workbook_NewChart(ByVal Ch As Chart)
    LogChange "New Chart Created"
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    LogChange "New Sheet Created"
End Sub

Private Sub Workbook_Open()
    LogChange "Opened " & ActiveWorkbook.Name
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' 跳过审计日志表的变更
    If Sh.Name = "Audit Trail" Then Exit Sub
    
    ' 捕获当前单元格值，处理可能的错误（比如合并单元格）
    On Error Resume Next
    CurrentValue = Target.Value
    On Error GoTo 0
    
    ' 处理空值情况
    If IsEmpty(PreviousValue) Or PreviousValue = "" Then
        PreviousValue = "EMPTY"
    End If
    If IsEmpty(CurrentValue) Or CurrentValue = "" Then
        CurrentValue = "EMPTY"
    End If
    
    ' 仅当值确实变更时记录日志
    If PreviousValue <> CurrentValue Then
        LogChange Target.Address & " changed from " & PreviousValue & " to " & CurrentValue
    End If
    
    ' 更新PreviousValue为当前值，用于下次变更追踪
    PreviousValue = CurrentValue
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ' 仅在选中单个单元格时捕获值，避免多单元格选中的异常
    If Target.Cells.Count = 1 Then
        On Error Resume Next
        PreviousValue = Target.Value
        On Error GoTo 0
    End If
End Sub

关键修改点 #

• 变量类型调整：将PreviousValue和CurrentValue改为Variant类型，支持文本、数字、空值等所有单元格数据类型
• 移除强制赋值0：删除原代码末尾PreviousValue = 0的错误赋值，确保历史值不会被清空
• 优化错误处理：仅在获取单元格值时启用错误处理，避免异常干扰正常逻辑
• 空值处理逻辑：直接判断单元格是否为空，替换为"EMPTY"，无需冗余的类型判断
• 多单元格选中过滤：在SheetSelectionChange中仅捕获单个单元格的值，避免多单元格操作导致的异常

修改后即可准确记录单元格真实历史值，实现类似$E$6 changed from ND to Q的正确日志格式。

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