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:
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