Narrowing Down What Caused a Crash

January 22, 2018 - by Bob Umlas

Sometimes Excel simply gives a message along the lines of “Excel has stopped working. We are sorry for any inconvenience.”

When you get such a message, you might press Ctrl + Alt + Delete and open the workbook again (hopefully having saved whatever work you had done!), wanting to step through the code to find the offending statement. When you single-step through the code, all may work fine, but when you run it at full speed, once again it may crash. How can you find the offending statement?

You can write a simple line of code between each line of code that could be the culprit. So the VBA code may originally look something like this:

Sub UICreation()
Dim x As String
    On Error Resume Next
    x = Sheets("Scenario").Name
    If Err.Number <> 0 Then
        MsgBox "Current workbook needs to have a Scenario sheet!", vbCritical
        Exit Sub
    End If
    ActiveWorkbook.Unprotect WorkbookPassword
    Err.Clear
    ActiveWorkbook.Unprotect SheetPassword
    If Err.Number <> 0 Then
        MsgBox "Workbook cannot be unprotected by the macro.", vbCritical
        Exit Sub
    End If
    Application.OnTime Now, "More"
    ThisWorkbook.Sheets("FastPricer").Copy Before:=ActiveWorkbook.Sheets(1)
End Sub

This procedure, in fact, does not crash, but it illustrates what you can do if you find that code crashes when run at full speed but not when you step through it.

You change the above code to this, with inserted statements Bug 1, Bug 2, etc.:

Sub UICreation()
Dim x As String
    On Error Resume Next
    Bug 1
    x = Sheets("Scenario").Name
    Bug 2
    If Err.Number <> 0 Then
        MsgBox "Current workbook needs to have a Scenario sheet!", vbCritical
        Exit Sub
    End If
    Bug 3
    ActiveWorkbook.Unprotect WorkbookPassword
    Err.Clear
    Bug 4
    ActiveWorkbook.Unprotect SheetPassword
    If Err.Number <> 0 Then
        MsgBox "Workbook cannot be unprotected by the macro.", vbCritical
        Exit Sub
    End If
    Bug 5
    Application.OnTime Now, "More"
    Bug 6
    ThisWorkbook.Sheets("FastPricer").Copy Before:=ActiveWorkbook.Sheets(1)
End Sub

Here’s the bug procedure:

Sub Bug(num As Integer)
    SaveSetting "EOTB2", "EOTB2", "EOTB2", num
End Sub

This procedure saves a value in the registry. The syntax for SaveSetting is:

For the first three parameters, say that you use EOTB2 (for Excel Outside the Box 2) - a random selection. You could instead use SaveSetting "X", "X", "X", num. If you use this a lot, you can take advantage of the three levels AppName, Section, and Key. That way, if you have many sections in the AppName, you can clean up the registry for all your settings by using the simple DeleteSetting "EOTB2" (or whatever you set for AppName), and all the sections and keys will also be deleted.

Now you run the procedure at full speed, and it crashes. So you restart Excel, get to the VBE, open the Immediate window (by pressing Ctrl + G), and type this:

? GetSetting(“EOTB2”,”EOTB2”,”EOTB2”)

If this procedure returns 4, for example, then it crashed sometime after Bug 4. It’s unlikely that the If/End If section was the culprit; more likely it was ActiveWorkbook.Unprotect SheetPassword. (Remember that this is just an example, not what has actually happened.)

If your initial run of Bug 1, Bug 2, etc. shows that the procedure crashed in a large section of code after Bug x, you can insert more bug calls to narrow it down further. You sort of do a binary search in a long procedure to find the culprit.

Title Photo: Grant Ritchie / Unsplash

This guest article is from Excel MVP Bob Umlas. It is from the book, More Excel Outside the Box. To see the other topics in the book, click here.