VBA实现Excel公式结果变化时自动更新时间戳及状态日期

因为Worksheet_Change仅响应手动输入或VBA修改的单元格，公式计算结果变化不会触发它；而Worksheet_Calculate虽能捕获计算事件，但无法直接获取变化单元格，所以我们可以通过辅助列存储历史状态的方式解决这个问题：

步骤1：准备辅助列 #

在目标工作表中，选择与Status列（假设为B列）相邻的一列（比如C列），将其设为隐藏列，用于存储每一行上一次的Status值。首次使用时，可手动复制当前Status列的值到辅助列，或用VBA批量初始化。

步骤2：编写VBA代码 #

打开VBA编辑器（Alt+F11），找到目标工作表，替换为以下代码：

Private Sub Worksheet_Calculate()
    Dim statusRng As Range
    Dim cell As Range
    Dim lastRow As Long
    
    ' 假设Status列是B列，辅助列是C列，Entry Date是D列，Last Entry是E列，Exit Date是F列
    lastRow = Me.Cells(Me.Rows.Count, "B").End(xlUp).Row
    Set statusRng = Me.Range("B2:B" & lastRow) ' 从第2行开始，跳过表头
    
    Application.EnableEvents = False ' 禁用事件避免循环触发
    
    For Each cell In statusRng
        ' 对比当前Status值和辅助列存储的旧值
        If cell.Value <> cell.Offset(0, 1).Value Then
            ' 更新辅助列为当前值
            cell.Offset(0, 1).Value = cell.Value
            
            Select Case UCase(cell.Value)
                Case "ACTIVE"
                    ' 首次激活：Entry Date为空则填充，否则更新Last Entry
                    If cell.Offset(0, 2).Value = "" Then
                        cell.Offset(0, 2).Value = Now
                    Else
                        cell.Offset(0, 3).Value = Now
                    End If
                Case "INACTIVE"
                    ' 更新Exit Date
                    cell.Offset(0, 4).Value = Now
            End Select
        End If
    Next cell
    
    Application.EnableEvents = True ' 恢复事件
End Sub

代码说明 #

• 辅助列作用：通过存储上一次的Status值，实现"检测变化"的功能，弥补Worksheet_Calculate无法直接获取变化单元格的缺陷。
• Application.EnableEvents = False：防止更新日期时触发新的计算事件，造成循环。
• 列位置调整：如果你的列位置不同（比如Status不是B列），修改代码中的列偏移量或列标识即可。比如cell.Offset(0,2)代表当前单元格向右偏移2列，对应Entry Date列。
• UCase转换：避免大小写不一致导致的判断错误，比如"active"或"Active"都能被识别。

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