开启自动计算时，如何在布尔单元格因其他单元格变更时调用宏？

问题描述 #

现有Private Sub Worksheet_Change(ByVal Target As Range)代码仅在通过键盘手动修改单元格值（在True和False之间切换）时生效。请问是否有办法在单元格值因其他单元格变更（如公式联动更新）而改变时调用对应的宏？

现有代码示例：

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells1 As Range
    Dim KeyCells2 As Range
    Set KeyCells1 = Range("D8") ' 选择资产
    Set KeyCells2 = Range("F42") ' 警报状态
    
    If Not Application.Intersect(KeyCells1, Range(Target.Address)) _
      Is Nothing Then
        Macro1
    End If
    
    If Not Application.Intersect(KeyCells2, Range(Target.Address)) _
      Is Nothing Then
        Macro2
    End If
End Sub

解决方案 #

Worksheet_Change事件仅响应手动编辑单元格的操作，无法捕获公式联动导致的数值变化。要实现需求，需搭配Worksheet_Calculate事件，并通过模块级变量记录目标单元格的旧值，以此判断是否发生了真实变化。

完整代码实现 #

在目标工作表的代码模块中，添加以下代码：

' 模块级变量：存储目标单元格的旧值，需放在所有Sub之外
Dim oldD8Value As Variant
Dim oldF42Value As Variant

' 工作表激活时初始化旧值
Private Sub Worksheet_Activate()
    oldD8Value = Range("D8").Value
    oldF42Value = Range("F42").Value
End Sub

' 处理手动编辑单元格的场景（保留原有逻辑，同步更新旧值）
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells1 As Range
    Dim KeyCells2 As Range
    Set KeyCells1 = Range("D8") ' 选择资产
    Set KeyCells2 = Range("F42") ' 警报状态
    
    If Not Application.Intersect(KeyCells1, Target) Is Nothing Then
        Macro1
        oldD8Value = KeyCells1.Value ' 同步更新旧值，避免Calculate事件重复触发
    End If
    
    If Not Application.Intersect(KeyCells2, Target) Is Nothing Then
        Macro2
        oldF42Value = KeyCells2.Value ' 同步更新旧值，避免Calculate事件重复触发
    End If
End Sub

' 处理公式联动导致的单元格值变化
Private Sub Worksheet_Calculate()
    ' 检查D8值是否变化
    If Range("D8").Value <> oldD8Value Then
        Macro1
        oldD8Value = Range("D8").Value ' 更新旧值，下次触发时做正确对比
    End If
    
    ' 检查F42值是否变化
    If Range("F42").Value <> oldF42Value Then
        Macro2
        oldF42Value = Range("F42").Value ' 更新旧值，下次触发时做正确对比
    End If
End Sub

关键说明 #

1. 模块级变量的作用：oldD8Value和oldF42Value必须定义在所有Sub过程之外，否则每次事件触发都会重置变量，无法正确对比新旧值。
2. 初始化旧值：Worksheet_Activate事件确保每次打开或切换到该工作表时，都会获取目标单元格的当前值作为初始对比基准。
3. 避免重复触发：手动编辑单元格后，同步更新旧值，防止Worksheet_Calculate事件因值未变化却重复调用宏。
4. 自动计算要求：确保工作表的自动计算功能处于开启状态（默认开启），否则Worksheet_Calculate事件不会触发。

内容的提问来源于stack exchange，提问作者ANDRÉ DA MATTA