Excel 2000 very unstable as a development environment

[Rob Nicholson]

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

 

[Rob Nicholson]

So single step one way, and it's fine. Start single stepping another and
it

crashes. As I said, unstable

Having worked on this today for quite a while and doing things in a
different order, it appears to be a lot more stable.

One key thing I'm not doing is creating an ADOX database and then opening an
ADO connection to it from a MSForms.CommandButton click event. I might be
dreaming it but I have a dim and distant memory of problems running certain
code within the event handler of what is in effect, an ActiveX object.

I certainly remember problems of programmatically adding MSForms controls to
a sheet - it resets the dynamic variables. I wonder if there is some
associated problem with calling some ActiveX components within a click
event.

It certainly can't be ANY ActiveX control as I'm sure we've all done
something like this in a click event:

Sub MyButton_Click()
Dim rst As New ADODB.Recordset
rem Carry out some recordset operation
End Sub

 

[Peter Huang [MSFT]]

Hi

Based on my experience, if Excel file is corrupted, the file will not be
opened corrected.
So do you mean you still can open the excel file while the code will no
longer work?
If you wonder if the VBA code's malfuntion is caused by the corrupted, you
may try to copy the code to a new created workbook to see if that works.

Also office can be consider as a single threaded program, any functional
call will be blocked until that call return.

If you still have any concern please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Rob Nicholson]

So do you mean you still can open the excel file while the code will no

longer work?

Yup - that's about it. The file opens okay but as you start single stepping
through it crashes with a "memory cannot be read" which I'm assuming is a
bad pointer somewhere inside the binary.

If you wonder if the VBA code's malfuntion is caused by the corrupted, you
may try to copy the code to a new created workbook to see if that works.

Spooky - we tried this on Friday. We've written a module to copy sheets, VBA
& references to a new workbook. After the copy, single stepping doesn't
crash. However, run it a few more times and the crash will re-appear.

It's not completely repeatable though - sometimes it will crash, other times
it won't.

We've restructured the initialisation code to do more from auto_open (such
as establishing connections to ADO Jet database) and this appears to be more
stable.

I'm not expecting a "this will fix it" kind of answer, just trying to see if
anyone has any tips. Obviously corrupt Excel workbooks aren't uncommon
considering the number of repair tools. We've used Excel-Fix several times
to save a lot of data

Cheers, Rob.

 

[Peter T]

I'm not expecting a "this will fix it" kind of answer,

If I had these problems I would be seeking one

Peter T

 

[Jon Peltier]

Suggestion: Try Rob Bovey's Code Cleaner add-in. This exports and deletes all code
modules, then re-imports them, which can clean out a lot of junk that otherwise
accumulates in VBA. It's a free download from http://appspro.com

Also, while I do most of my development in 2000, I find that opening and saving a
funky workbook in 2002 will do some repair that will allow me to return to 2000
without the issues I was having.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Stephen Bullen]

Hi Rob,

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

If I'm reading this correctly, you have a class that defines an interface
IZincApp. You have a class called App that implements the IZincApp interface,
but also has a child class called ZincApp. When App instantiates ZincApp, you
want to pass it a reference to the App class, so some code within ZincApp can
call back into App?

If so, you might find it more stable to use a Property Set ParentApp instead
of a Property Let, and it's best practice to instantiate classes when they're
needed, rather than declaring them 'As New':

Class IZincApp (defines the IZincApp interface)
==============
Public Sub CallBack()
End Sub


Class App (parent class, implementing the IZincApp interface)
=========
Implements IZincApp
Private m_ZincApp As ZincApp

Private Sub Class_Initialize()
Set m_ZincApp = New ZincApp
Set m_ZincApp.ParentApp = Me
End Sub

Private Sub IZincApp_CallBack()
End Sub


Class ZincApp (child class, calling back via the IZincApp interface)
=============
Private m_ParentApp As IZincApp
Public Property Set ParentApp(RHS As IZincApp)
Set m_ParentApp = RHS
End Property

Public Sub SomeMethod()
m_ParentApp.CallBack
End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Stephen Bullen]

Hi Jamie,

Of course, one must be extremely careful when retaining a reference to
a parent in a child object. All references to the parent must be
released before the object can be destroyed ... you know where I'm
going here. This is one of the best ways to create a memory leak <g>. I
can see how using As New and setting a reference in Class_Initialize
could cause problems.

Absolutely!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Peter Huang [MSFT]]

Hi

Have you tried Jon's suggestion?
If you still have any concern, please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Rob Nicholson]

If so, you might find it more stable to use a Property Set ParentApp
instead

of a Property Let, and it's best practice to instantiate classes when they're
needed, rather than declaring them 'As New':

After a *lot* (and I mean a lot) of debugging, removing lines, adding lines,
I've finally tracked it down to what I think is a bug in interfaces. I'd
slowly started rebuilding the application line by line, module by module
testing as I went along. Although the context is meaningless, the crashes
occur when you try and use your classes as return values in interfaces. For
example:

IZincScreen.CLS:

Option Explicit

Function Init() As ZincException
End Function

Sub DrawScreen()
End Sub

Function AfterDraw() As ZincException
End Function

Property Get ZincScreen() As ZincScreen
End Property

And the crash only occurs when you happen to have two or more classes
implementing the same interface: TitleScreen & ChartScreen in my case.
Replace the references above to ZincException & ZincScreen to Object (two
other classes in the project) then program works a treat.

I guess a by-product of using Object is that late binding is used as opposed
to early binding.

Although it's taken a staggering amount of time to reach this conclusion, at
least we can move forward again knowing it won't randomly crash.

Cheers, Rob.

 

[Peter Huang [MSFT]]

Hi

Can you provide a simple reproduce sample and send to me by removing the
"online" from my email address?

Here is my test code.
[Interface Class1]
Public Function Test() As Class1
End Function

[Class2]
Implements Class1

Private Function Class1_Test() As Class1
MsgBox "Class2"
Set Class1_Test = New Class3
End Function

[Class3]
Implements Class1

Private Function Class1_Test() As Class1
MsgBox "Class3"
End Function

[Test module]
Sub Test()
Dim o As Class1
Set o = New Class2
o.Test
Dim b As Class1
Set b = o.Test()
b.Test
End Sub

If I have any misunderstanding, please feel free to post here.



Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.