CODEWITHSUNDEEP
excelvba
MatthewHagemann
MatthewHagemann

Reputation: 1195

What VBA code can I write that will guarantee a "Microsoft Excel has stopped working" error message?

I'm sure this seems like a strange question, but one of my clients is having problems with Excel randomly crashing and giving the error message "Microsoft Excel has stopped working" (Problem Event Name: APPCRASH) while my C# program is using Excel (invisibly), and I'm trying to duplicate the problem so I can figure out a way for my program to handle an Excel crash. So I need some VBA code that will cause Excel to crash with the APPCRASH event. I tried the below, but it won't cause a crash unless Excel is visible. Any suggestions?

Do
   x = 1
Loop

Here is the error message: enter image description here

Upvotes: 2

Views: 603

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

This is very likely an X-Y problem.

The way to fix the issue isn't to figure out some random VBA code that gets you a similar APPCRASH event log entry - none will. Or if any does, then you're not going to learn anything new anyway.

The way to fix the problem is to attach the VS debugger to the EXCEL.EXE process, reproduce user steps, then watch VS break on an unhandled exception in the C# code.

Most likely you're getting a COMException somewhere, your Excel instance suddenly dies as a result, and writes an APPCRASH that's a side-effect of an exception that occurred out-of-process, in the managed code that spawned the EXCEL.EXE process.

Make sure your C# code correctly releases all COM objects it's accessing (that's every single Worksheet, Range /cells, Worksheets collections, Application instance), and isn't chaining object member calls (resulting in a memory leak), e.g.:

xlApp.Worksheets("foo").Range("A1").Value = 42;

The above is leaking the Worksheets collection, the Worksheet instance, and the Range for the accessed cell.

Managed and COM threading models aren't exactly compatible either, so if your C# code is accessing COM objects from multiple threads, expect COMException galore.

The first thing to do is reproduce the problem with a debugger attached, and see if your C# code is throwing an exception. Trying to find some VBA code that will crash Excel isn't going to help with anything.

Upvotes: 7

Comintern
Comintern

Reputation: 22195

Stick your hands in its stuff, twist them around, grab something soft and squishy, and then rip it out.

This should do the trick:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
  (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Const GWL_WNDPROC As Long = (-4)

'SAVE YOUR WORK!
Private Sub BuhBye()
    Dim hWnd As Long
    hWnd = FindWindow(vbNullString, Application.Caption)
    SetWindowLong hWnd, GWL_WNDPROC, AddressOf OopsIDivedItAgain
End Sub

Public Function OopsIDivedItAgain(ByVal hWnd As Long, ByVal Msg As Long, _
    ByVal wParam As Long, ByVal lParam As Long) As Long
    OopsIDivedItAgain = 1 / 0
End Function

It basically subclasses the main Excel window and intentionally throws from inside the message pump.

Upvotes: 9

Related Questions