Worksheet_Change无法触发公式更新单元格的解决方案咨询

Worksheet_Change 事件仅在单元格被手动编辑、VBA直接写入值时触发，公式重算产生的值变化不会激活该事件，这是Excel的事件机制设计，不是原有代码的语法问题。

Worksheet_Calculate会在工作表公式重算时触发，但事件本身不会返回具体是哪个单元格发生了变化，因此需要提前存储第30列的历史值，重算时逐行对比新旧值定位变化单元格，即可实现需求。
完整实现代码（放在对应工作表的模块中）：

' 模块级数组，存储第30列的历史值
Dim oldCol30Vals As Variant

Private Sub Worksheet_Activate()
    ' 工作表激活时初始化历史值数组
    Dim lastRow As Long
    lastRow = Me.UsedRange.Rows.Count + Me.UsedRange.Row - 1
    oldCol30Vals = Me.Range("AD1:AD" & lastRow).Value
End Sub

Private Sub Worksheet_Calculate()
    Dim lastRow As Long, i As Long
    Dim currentVal As Variant
    
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    
    lastRow = Me.UsedRange.Rows.Count + Me.UsedRange.Row - 1
    ' 数据行数增加时扩展数组
    If lastRow > UBound(oldCol30Vals, 1) Then
        ReDim Preserve oldCol30Vals(1 To lastRow, 1 To 1)
    End If
    
    ' 逐行对比新旧值
    For i = 1 To lastRow
        currentVal = Me.Cells(i, 30).Value
        ' 跳过错误值、空值
        If IsError(currentVal) Or IsEmpty(currentVal) Then
            oldCol30Vals(i, 1) = currentVal
            GoTo NextRow
        End If
        ' 判定值是否变化
        If CDbl(currentVal) <> CDbl(oldCol30Vals(i, 1)) Then
            If currentVal > 0.1 Then
                Me.Range("AJ" & i).Select
                Call Mail_with_outlook
            End If
            ' 更新历史值
            oldCol30Vals(i, 1) = currentVal
        End If
NextRow:
    Next i

ExitHandler:
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Resume ExitHandler
End Sub

' 兼容手动修改第30列的场景
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    If Target.Column = 30 Then
        thisRow = Target.Row
        If Not IsError(Target.Value) And Target.Value > 0.1 Then
            Range("AJ" & thisRow).Select
            Call Mail_with_outlook
        End If
        ' 同步更新历史值
        If thisRow <= UBound(oldCol30Vals, 1) Then
            oldCol30Vals(thisRow, 1) = Target.Value
        End If
    End If
ExitHandler:
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Resume ExitHandler
End Sub

注意事项：

• 所有事件逻辑中必须加Application.EnableEvents = False避免递归触发，且要在错误处理中保证事件开关最终恢复为True
• 必须对公式返回的错误值（如#DIV/0!、#N/A）做跳过判断，避免数值比较时报错
• 如果不需要选中AJ列单元格，可以直接删除.Select相关代码，减少对用户操作的干扰

你当前第30列的公式为=A1-C1，仅依赖A列和C列的值，只有这两列的单元格发生修改时才会导致第30列结果变化，因此可以直接监听A、C列的Change事件，无需存储历史值，性能更好：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkRng As Range, c As Range
    Dim thisRow As Long
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    
    ' 仅当修改区域涉及A列、C列时处理
    Set checkRng = Intersect(Target, Union(Me.Columns("A"), Me.Columns("C")))
    If Not checkRng Is Nothing Then
        For Each c In checkRng
            thisRow = c.Row
            ' 跳过错误值判断
            If Not IsError(Me.Cells(thisRow, 30).Value) Then
                If Me.Cells(thisRow, 30).Value > 0.1 Then
                    Me.Range("AJ" & thisRow).Select
                    Call Mail_with_outlook
                End If
            End If
        Next c
    End If
ExitHandler:
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Resume ExitHandler
End Sub

注意事项：如果后续修改第30列的公式、新增了其他引用列，需要把对应列加入到Union的判断范围中，否则会出现触发遗漏。

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