如何合并多个Worksheet_Calculate过程并解决相关运行报错问题

核心问题解答 #

• 同一个工作表模块内仅支持存在1个Worksheet_Calculate事件过程，多个同名称过程会直接触发VBA语法错误。多个逻辑完全可以合并，但不能直接粘贴代码就结束，需要额外处理循环触发和异常兼容问题，你之前遇到的报错就是缺失这部分处理导致的。

正确合并代码方案 #

你的两个场景仅触发条件不同，执行逻辑完全一致，直接将两个判断条件用Or拼接即可，合并后完整代码如下，已经兼容你遇到的报错场景：

Private Sub Worksheet_Calculate()
    ' 关闭事件触发，避免删除行触发重算导致无限循环，这是你资源耗尽报错的核心原因
    Application.EnableEvents = False
    ' 增加错误捕获，遇到异常自动恢复事件开关，不会导致后续所有宏失效
    On Error GoTo ErrHandler

    ' 两个触发条件满足任意一个就执行删除操作
    If Sheets("Ledger").Range("AC2").Value < 0.4 Or Sheets("Ledger").Range("L4").Value < 0 Then
        newLastRow = Range("A6").End(xlDown).Row()
        Intersect(Range("ABCDEF"), Rows(newLastRow)).Delete
    End If

ErrHandler:
    ' 无论代码是否报错，最终都恢复事件触发开关
    Application.EnableEvents = True
    ' 类型不匹配报错时给出提示，方便排查问题单元格
    If Err.Number = 13 Then
        MsgBox "请检查Ledger表AC2、L4或Data表I2单元格是否存在#N/A/#DIV/0!等错误值", vbOKOnly, "类型不匹配"
    End If
End Sub

报错原因说明 #

1. Excel资源耗尽报错 #

删除行的操作会触发工作表重新计算，重算会再次调用Worksheet_Calculate事件，形成无限循环，持续占用内存直到触发资源不足提示，只要在执行修改工作表的操作前关闭事件开关、操作结束后再打开即可解决。

2. 运行时错误'13'：类型不匹配 #

触发原因是你做数值比较的单元格（此处为Data表I2，或Ledger表AC2、L4）内的值不是有效数字，而是公式计算产生的错误值，错误值无法和数字做大小比较就会抛出该错误。你可以将Data工作表的事件代码优化为如下版本，提前判断值类型避免报错：

Private Sub Worksheet_Calculate()
    ' 先判断I2是否为有效数值，不是则跳过比较逻辑
    If IsNumeric(Sheets("Data").Range("I2").Value) Then
        If Sheets("Data").Range("I2").Value < 0.4 Then
            MsgBox "Green Investments Must Comprise 40% of Portfolio", vbRetryCancel, "Error!"
        End If
    End If
End Sub

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