How do I solve so excel doesn't crash by my code?-CodePudding

I have a file which inconsistently crash. The only reliable way for me to make it crash is to copy the file and then when I run my macro (Which is to open a userform) it crashes. If I then use the recovered file instead, it will not crash. But as soon as I save the file again, it will display the same behavior.

I think i've traced the issue. I have userforms that will open within the 'Master' userform and if I delete a specific userform I can't get excel to crash. I've tried to delete all code belogning to that specific userform and that also seems to help. My last investigation was to delete a specific part of the code, which seems to be the problem as excel doesn't crash anymore once i've deleted that part of code.

Note that the code itself shouldn't be the problem as sometimes it works exactly as it should, without any issue at all. I'm starting to think that it's either because of the amount of code, or the amount of mousedown/mouseup events.

Part 1 of 289:

Private Sub Block1V1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    If GameOverStatus = "No" Then
        If Button = 2 Then
            'Rightclick
            Call PutFlag(0, 0)
            Else
                If Block1V1.Caption = "" Then
                    Block1V1.SpecialEffect = fmSpecialEffectSunken
                    Call Hit(0, 0)
                End If
        End If
    End If
End Sub
Private Sub Block1V1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    If GameOverStatus = "No" Then
        Block1V1.SpecialEffect = fmSpecialEffectFlat
    End If
End Sub

As you can see in the code, i'm reffering to "Block1V1". I have a total of 17x17 = 289 of these blocks with the exact same code, only difference is the numbering in which block it will reference.

All of that total 5 490 rows of code.

Do anyone have any idea why this might cause excel to sometimes crash and sometimes work flawless? I'm not aware of any limits of how much code can be stored within a Userform or if there is a limit on how many MouseDown/MouseUP event you can have.

NOTE that excel crashes without any of the above code being run. I don't even need to open the problematic userform for excel to crash.

NOTE: My investigation as to where the issue is was not good. I've been able to reproduce the issue even when that specific userform is deleted. However, I also found a bad fix. When I open the workbook, if I save it before I show the first userform it doesn't seem to crash.

CodePudding user response：

NOTE: THIS ANSWER DID NOT HELP.

I believe I found a solution by refactoring the code. I removed 1 operation as I can do this on a different place and then optimized the if statement. This allowed me to go from 5 490 rows to 3 179 rows.

I don't seem to be able to reproduce the error anymore. but I can't understand why this would help.

Private Sub Block1V1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    If Button = 2 Then
        Call PutFlag(0, 0)
    ElseIf Block1V1 = "" Then
        Block1V1.SpecialEffect = fmSpecialEffectSunken
        Call Hit(0, 0)
    End If
End Sub
Private Sub Block1V1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Block1V1.SpecialEffect = fmSpecialEffectFlat
End Sub

Following the comment I refactored it even more, to about 1 750 rows. But I can still reproduce the issue.

Private Sub Press(Button As Integer, x As Integer, Y As Integer)
    Dim Block As String
    Block = "Block" & x & "V" & Y
    If Button = 2 Then
        Call PutFlag(x - 1, Y - 1)
    ElseIf Block = "" Then
        Block.SpecialEffect = fmSpecialEffectSunken
        Call Hit(x - 1, Y - 1)
    End If
End Sub


Private Sub Block1V1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Call Press(Button, 1, 1)
End Sub
Private Sub Block1V1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Block1V1.SpecialEffect = fmSpecialEffectFlat
End Sub

I thought above worked, but as soon as I saved the file, and then reopened it, the issue started again.

CodePudding user response：

I believe I found my Answer in this thread. https://www.excelforum.com/excel-programming-vba-macros/867691-vba-causes-excel-to-crash-when-re-opened-after-it-has-been-saved.html

Since I basically need to adjust all of my code (thousands lines of code) I havn't been able to confirm this, but his problem is identical to the one i'm experiencing.