excel在end with和for循环之间崩溃？

Question

添加一行随机使我的 excel 崩溃。

崩溃可能发生在 end with或 for rij = 4 to lastRow .

与行之间的 30 秒暂停相比，通过行非常快速地进行调试会使程序崩溃更快。

我在不同的模块中有完全相同的代码，它确实在那里工作。

lastrow 在我调试时的值为 5，所以问题不可能是 Rij > lastRow

代码:

Option Explicit

Dim lastRow As Long
Dim IngevuldeCellen As Integer
Dim Ingevuld As Integer
Dim Rij As Integer
Dim tbl As Object
Dim AantalRijen As Integer
Dim laatsteRij As Long

Private Sub WorkSheet_Change(ByVal Target As Range)
     'Als laatste rij 6 ingevulde velden heeft doe dit
    lastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
    IngevuldeCellen = WorksheetFunction.CountA(Range("B" & lastRow & ":G" & lastRow))
    If IngevuldeCellen >= 4 Then
        Call Module3.AddRowToBottom
        Range("F1:F" & lastRow).Interior.Color = RGB(59, 148, 0)
        Range("A1:A" & lastRow).Interior.Color = RGB(59, 148, 0)

    ElseIf IngevuldeCellen < 4 Then
    Range("A" & lastRow).Interior.Color = RGB(255, 0, 0)
    Range("F" & lastRow).Interior.Color = RGB(255, 0, 0)
    End If
    
    
    Ingevuld = WorksheetFunction.CountA(Range("H6:O18"))
    If Ingevuld >= 10 Then
        With Sheets("Ruimtelijst")
            lastRow = .Cells(Cells.Rows.Count, "G").End(xlUp).Row
        End With
        For Rij = 4 To lastRow
            If Not Intersect(Target, Blad3.ListObjects("tbl_" & Rij - 3).Range) Is Nothing Then
                'Zoek de laatste rij van de tabel op
                Set tbl = Blad3.ListObjects("tbl_" & Rij - 3)
                AantalRijen = tbl.Range.Rows.Count
                laatsteRij = tbl.Range.Cells(AantalRijen, "E").Row
                'Roept module
                Call Module3.AddRow(laatsteRij)
            End If
        Next Rij
    End If
End Sub

Answer 1

感谢 Teamothy，我发现 VBA 模块可能会随机损坏。我通过复制所有代码并将其粘贴到新的电子表格中来解决此问题。