带On Error Resume Next的Worksheet_Change事件触发异常问题排查

问题分析 #

Power Query刷新时会批量更新单元格，可能导致Target包含多单元格范围，原代码的数值判断逻辑未精准锁定B2的有效值，且缺少状态记录机制，要么误发邮件要么导致事件触发异常。

修正方案 #

核心逻辑 #

• 精准锁定B2单元格的变化，排除其他单元格干扰
• 新增状态标记，避免同一超标状态下重复发送邮件
• 兼容Power Query刷新的批量更新场景，确保判断准确

完整代码 #

' 模块级变量，记录当前是否已发送过超标通知
Private hasSentAlert As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim monitoredCell As Range
    ' 仅关注B2单元格的变化
    Set monitoredCell = Intersect(Range("B2"), Target)
    
    ' 若变化范围不包含B2，直接退出
    If monitoredCell Is Nothing Then Exit Sub
    
    ' 验证数值有效性并判断是否超标
    If IsNumeric(monitoredCell.Value) And monitoredCell.Value > 15 Then
        ' 仅在未发送过通知时执行邮件发送
        If Not hasSentAlert Then
            Call Mail_small_Text_Outlook
            ' 标记为已发送，避免重复触发
            hasSentAlert = True
        End If
    Else
        ' 当水位回落至阈值及以下时，重置发送状态
        hasSentAlert = False
    End If
End Sub

关键说明 #

• 状态重置机制：当B2数值回到15或以下时，自动重置发送标记，下次超标时会重新触发邮件
• Power Query兼容：通过Intersect精准锁定B2，避免批量更新时其他单元格的变化干扰判断；直接使用monitoredCell.Value确保取到的是目标单元格的准确值
• 错误排查优化：移除了On Error Resume Next（隐藏错误会增加排查难度），建议提前单独测试Mail_small_Text_Outlook函数确保其运行正常

可选优化：按时间间隔避免重复发送 #

如果需要设置重复发送的时间间隔（比如每小时仅发送一次），可以借助辅助单元格（如C2）记录上次发送时间：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim monitoredCell As Range
    Set monitoredCell = Intersect(Range("B2"), Target)
    
    If monitoredCell Is Nothing Then Exit Sub
    
    If IsNumeric(monitoredCell.Value) And monitoredCell.Value > 15 Then
        ' 检查是否未发送过，或上次发送已超过1小时
        If Range("C2").Value = "" Or Now() - Range("C2").Value > TimeValue("1:00:00") Then
            Call Mail_small_Text_Outlook
            ' 记录本次发送时间
            Range("C2").Value = Now()
        End If
    Else
        ' 水位回落时清空发送时间记录
        Range("C2").Value = ""
    End If
End Sub

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