VBA Causing Excel To Crash

[Gareth]

This is my first post onto the group and I appologise for the length of
it, but I felt some background would be useful.

I have recently started building a system using Excel and VBA. Whilst
my experience in coding VBA is limited I have been programming for a
long time and have used other languages including Java.

Coming from a Java background I am keen to use an OO approach and spent
some time investigating other peoples views. In conclusion I decided
that the project could be done in OO style and I have mapped each
business process to a Class in VBA. All my classes are 'Public Not
Creatable' and I have one Module containing a method for each class
that creates an instance. ( a 'Constructor' if you like). On the whole
this approach has served me well, allowing for good code separation and
re-use.
I have also used Interfaces as a 'work around' to inheritance and
although a little clumsy I continue to use it, If at some time in the
future the code is migrated to another environment like C# or indeed
Java then this approach will prove useful.

However, during the programming day I get quite a lot of odd behaviour
and Excel crashes. I am wondering wether or not I am missing something
basic in my approach. In fact Excel crashes so often I have also
written some basic VBA code to export all my modules out to text files,
delete them from the work book, then re-import them afterwards. This
process does seem to fix most problems but eventually new ones appear.

The most recent is a line of code 'Set v_MyClassA = new clsMyClassA'
this actually calls the Class_Initialize of clsMyClassB. Again I
exported all the modules then re-imported them and it has now fixed the
problem. But why, why, why !!!!

I would appreciate it anybody could share their experience and perhaps
highlight a basic problem in my approach.

 

[NickHK]

Gareth,
There nothing wrong with your approach ; you're merely using the constructs
available to the language.
However, as VBA stores code in a variety of compiled states whilst active
(and in the workbook), the IDE can become confused with multiple complex
changes.
It may help to do a full compile (Debug>Compile VBA Project) and fix any
errors found.
If you are using API calls, ensure you check their success/failure (if
possible), as VBA has no knowledge of them.

Some people do report frequent crashes although I seldom experience this
problem. Whether due to your installation, add-in, Windows etc.. I can't
say.
Not much help, but nothing in your description indicates a cause.

NickHK

 

[Gareth]

NickHK

Thanks for your reply. I have done a little reading on add-ins and I
understand I could convert my project to an xla and therefore seperate
my code from the workbook which in essence is just being used as the
presentation layer.
Do you have any views in that direction ?

Also I didn't mention yesterday that I am writing code against MS
SQLServer using ActiveX Data Objects 2.8. I wonder if there are any
known issues when using this library ?

Thanks again