求助：VBA宏无法正确提取单元格修改前后的公式与值

原代码的核心问题 #

• 捕获旧值/旧公式的时机错误：Workbook_SheetSelectionChange仅在选中单元格时触发，无法保证选中的单元格一定会被修改，且直接粘贴修改多个单元格时，会丢失正确的旧数据。
• oldValue赋值错误：用VarType(Target.Value)得到的是数据类型编号，并非单元格的实际值。
• 日志存储格式混乱：每条修改记录的字段被逐行写入，应将同一记录的所有字段放在同一行的不同列，方便后续查看。
• 未处理多单元格修改场景：原代码默认Target是单个单元格，实际操作中可能涉及多个单元格批量修改。

修正后的代码实现 #

使用Workbook_SheetBeforeEdit事件在单元格进入编辑模式前捕获旧值和旧公式，配合Workbook_SheetChange记录修改后的新值、新公式，同时优化日志存储格式：

' 模块级变量，用于跨事件传递编辑前的单元格信息
Dim oldSheetName As String
Dim oldCellAddress As String
Dim oldCellValue As Variant
Dim oldCellFormula As String

Private Sub Workbook_SheetBeforeEdit(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    ' 仅记录非日志表的单元格
    If Sh.Name <> "Log Details" Then
        oldSheetName = Sh.Name
        oldCellAddress = Target.Address(0, 0)
        oldCellValue = Target.Value
        oldCellFormula = Target.Formula2
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim logSheet As Worksheet
    Dim nextRow As Long
    
    Set logSheet = ThisWorkbook.Sheets("Log Details")
    
    ' 跳过日志表自身的修改，避免循环触发
    If Sh.Name = "Log Details" Then Exit Sub
    
    Application.EnableEvents = False
    
    ' 遍历所有被修改的单元格，处理多单元格修改场景
    Dim cell As Range
    For Each cell In Target
        nextRow = logSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        ' 将同一记录的所有字段写入同一行的不同列
        logSheet.Cells(nextRow, 1).Value = oldSheetName & "-" & oldCellAddress
        logSheet.Cells(nextRow, 2).Value = oldCellValue
        logSheet.Cells(nextRow, 3).Value = oldCellFormula
        logSheet.Cells(nextRow, 4).Value = cell.Value
        logSheet.Cells(nextRow, 5).Value = cell.Formula2
        logSheet.Cells(nextRow, 6).Value = Environ("username")
        logSheet.Cells(nextRow, 7).Value = Now
        
        ' 添加跳转至原单元格的超链接
        logSheet.Hyperlinks.Add _
            Anchor:=logSheet.Cells(nextRow, 8), _
            Address:="", _
            SubAddress:="'" & oldSheetName & "'!" & oldCellAddress, _
            TextToDisplay:=oldCellAddress
    Next cell
    
    Application.EnableEvents = True
End Sub

关键说明 #

• SheetBeforeEdit事件：在单元格进入编辑模式前触发，能精准捕获修改前的原始值和公式，避免SelectionChange的误触发问题。
• 模块级变量：用于跨事件传递编辑前的单元格信息，确保SheetChange事件能获取到正确的旧数据。
• 多单元格处理：通过For Each cell In Target遍历所有被修改的单元格，保证批量修改时每条记录都被正确记录。
• 日志格式优化：将每条修改记录的所有字段（位置、旧值、旧公式、新值、新公式、操作人、时间、跳转链接）放在同一行的不同列，提升可读性。

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