excel - 循环变成无穷大并导致excel崩溃

Question

我目前正在处理我的 VBA 代码的一部分，如果遵守某些条件，该代码应该从另一个表中的不同表中计算总数。当我的条件不被遵守时，代码很好，但是当我进入条件时，带有 *** 的行会使我的代码运行到无穷大并使 excel 崩溃。我已经检查了语法，这应该不是问题。老实说，我不明白这行代码有什么问题。

预先感谢您的帮助!

'----------------------------------------------------
'-- Buildings' peak cooling load total calculation --
'----------------------------------------------------

Dim m As Integer, n As Integer

If Range("NP_BUILDING_TOTAL_YN") = "Yes" Then

If (Range("NP_BUILDING_TOTAL_OF") = "No" Or Range("NP_BUILDING_TOTAL_OF") = "") And (Range("NP_BUILDING_TOTAL_DF") = "No" Or Range("NP_BUILDING_TOTAL_DF") = "") Then

    For m = 1 To Sheet4.Range("NP_NBR_Y")

        For n = 1 To Sheet4.Range("NP_NBR_BUILDING")

            If Range("NP_BUILDING_TOTAL_YN" & n) = "Yes" Then

                ***Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value = Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value + Range("INPUTS_BUILDING" & n)(m + 3, 2).Value***

            End If

        Next n

    Next m

End If

If Range("NP_BUILDING_TOTAL_OF") = "Yes" And (Range("NP_BUILDING_TOTAL_DF") = "No" Or Range("NP_BUILDING_TOTAL_DF") = "") Then

    For m = 1 To Sheet4.Range("NP_NBR_Y")

        For n = 1 To Sheet4.Range("NP_NBR_BUILDING")

            If Range("NP_BUILDING_TOTAL_YN" & n) = "Yes" Then

                Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value = Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value + Range("INPUTS_BUILDING" & n)(m + 3, 2).Value * Range("INPUTS_BUILDING" & n)(m + 3, 4).Value

            End If

        Next n

    Next m

End If

If (Range("NP_BUILDING_TOTAL_OF") = "No" Or Range("NP_BUILDING_TOTAL_OF") = "") And Range("NP_BUILDING_TOTAL_DF") = "Yes" Then

    For m = 1 To Sheet4.Range("NP_NBR_Y")

        For n = 1 To Sheet4.Range("NP_NBR_BUILDING")

            If Range("NP_BUILDING_TOTAL_YN" & n) = "Yes" Then

                Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value = Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value + Range("INPUTS_BUILDING" & n)(m + 3, 2).Value * Range("INPUTS_BUILDING" & n)(m + 3, 5).Value

            End If

        Next n

    Next m

End If

If Range("NP_BUILDING_TOTAL_OF") = "Yes" And Range("NP_BUILDING_TOTAL_DF") = "Yes" Then

    For m = 1 To Sheet4.Range("NP_NBR_Y")

        For n = 1 To Sheet4.Range("NP_NBR_BUILDING")

            If Range("NP_BUILDING_TOTAL_YN" & n) = "Yes" Then

                Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value = Range("NP_BUILDING_TOTAL_TABLE")(m + 1, 2).Value + Range("INPUTS_BUILDING" & n)(m + 3, 2).Value * Range("INPUTS_BUILDING" & n)(m + 3, 4).Value * Range("INPUTS_BUILDING" & n)(m + 3, 5).Value

            End If

        Next n

    Next m

End If

End If

Answer 1

您正在更改更改事件 - 这会触发更改事件的调用，您正在更改数据的位置 - 这会触发更改事件的调用(等等......)

为了防止事件例程被递归触发，将此行放在例程的顶部:

Application.EnableEvents = False

最后

Application.EnableEvents = True