We have, over the years, developed many Excel 2000 based applications which
make very heavy use of VBA to turn Excel into a workable standalone database
application. The key word there is standalone - whilst a real client/server
app would be ideal (web client/server even better), these are small one-off
projects with limited budgets.

But one thing we painfully know is that Excel is not very stable and has a
very annoying habit of corrupting the XLS file or simply crashing. The
following is a simple example of the frustration we often feel :-)

I wonder if there are any other Excel VBA developers out there who can offer
any advise. There is nothing wrong with the code. What we suspect has
happened is that something BEFORE has corrupt the XLS file and put it in a
very unstable and unusable state.

We've suspected everything like:

o NEW in Private m_XYZ As New Something
o Using Class_Initialize
o Nested WITH statements

But getting rid of them doesn't seem to help. Well maybe it might but the
XLS file is already corrupt by now. The example below is a very simple
workbook - just two sheets, blank with a VB button on one of them.

A prime suspect is using VB buttons on sheets - the ones that trigger VBA
events, not the Excel ones that trigger macros. We've had trouble with these
before corrupting Excel workbooks. Just to clarify - these as the controls
managed by Microsoft Forms 2.0 in FM20.DLL and added to a sheet using the
Controls toolbox. Not, confusingly, the Forms toolbox - that's the Excel
forms :-)

Cheers, Rob.

BEGIN Example 1
Open up a workbook
Click Alt-F8, select DebugApp macro and start stepping into the code
Single step steps through fine using F8 each time
END

BEGIN Example 2
Open up same workbook
Open VBA editor using Alt-F11
Find the Sub DebugApp
Start stepping though using F8
Press

The code (same in both examples:

Sub DebugApp
StartApp True

.....entering StartApp function....
Private Function StartApp(Debugging As Boolean) As IZincException
If Not App Is Nothing Then
App.Cleanup
End If
Set App = New App

.....entering App class....
private m_ZincApp As New ZincApp (private variable of App
class)
Private Sub Class_Initialize()
m_ZincApp.ParentApp = Me (2nd method of executing CRASHES
here with a memory can't be read exception)

.....entering ZincApp class...
Private m_ParentApp As IZincApp
Property Let ParentApp(RHS As IZincApp)
Set m_ParentApp = RHS

The code really doesn't do that much:

o Starts a subroutine
o Calls another subroutine
o Creates a class object
o Creating triggers the Class_Initialize of the class object
o Calls the property routine of an object defined in the class

So single step one way, and it's fine. Start single stepping another and it
crashes. As I said, unstable :-)