My macro enabled spreadsheet has started randomly crashing. The only diagnostics is the message Microsoft Excel has stopped working. I am running Excel 2010 under Windows 7.

I think I have isolated the problem to a vba subroutine (InitializeReport) that populates a Type data structure (Public Type TableMetadata) that is defined in the Declarations section of Module1. In the same section I set up a public variable (Public lref as TableMetadata).

My test subroutine displays MsgBox popups before and after calling InitializeReport. If the crash occurs, it is between the two popups. However, if I run in Debug mode (just a few steps) the problem never occurs and in fact may go away for a while.

Obviously, something is not being initialized correctly. The code in InitializeReport looks very innocuous.

My main frustration is that I have no tool to get further information on the problem, since everything works fine in Debug mode, and also the spreadsheet will often work for periods with no occurrence.

Would appreciate if anybody has a suggestion on a strategy to get more info on the problem. Can't understand why Excel cannot produce a dump or some information on what went wrong.

I am even prepared to purchase Excel 2014 to see if the problem will go away.

Thank you