VBA Forms in Excel crashing on exit?

[Dom]

Hi,

I've made various forms in excel which are used to log types of calls
received at work, amongst other things.

The problem I have will ALL of them is that when anyone exits the
workbook after using it, Excel crashes and auto-recovers but I can't
work out what I'm doing wrong. I've tried the Unload (form) command, but
this doesn't seem to have made any difference.

The only thing I can think of is that I'm a bit lazy and don't always
declare variables, but the forms don't really use them anyway... Just a
couple are used for very basic validation of entries.

Unfortunately, I can't post code examples as they are at work and have
no way of getting them home, but I'm hoping this is a simple beginner's
mistake!?

Thanks for any advice in advance!
Dom

 

[Jim Cone]

Dom,

You aren't going to get much help without posting some code.
However, you could put "Option Explicit" as the first line
in every module and see what you come up with.

Does the workbook function on your computer but not on other computers?

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Hi,
I've made various forms in excel which are used to log types of calls
received at work, amongst other things.
The problem I have will ALL of them is that when anyone exits the
workbook after using it, Excel crashes and auto-recovers but I can't
work out what I'm doing wrong. I've tried the Unload (form) command, but
this doesn't seem to have made any difference.

The only thing I can think of is that I'm a bit lazy and don't always
declare variables, but the forms don't really use them anyway... Just a
couple are used for very basic validation of entries.

Unfortunately, I can't post code examples as they are at work and have
no way of getting them home, but I'm hoping this is a simple beginner's
mistake!?

Thanks for any advice in advance!
Dom

 

[Dom]

Does the workbook function on your computer but not on other computers?

No... The same error occurs on whatever computer I run it on.
What does the "Option Explicit" actually do?

Thanks for the pointer - I'll give it a try on Monday!

 

[Jim Cone]

1. Option Explicit will catch any misspelled variables.
You could think you are referring to one thing and Excel
could be seeing it as something else... lngNum vs. 1ngNum for instance.
2. It will also force you to declare all of your variables.
Undeclared variables become Variants, not always a good thing.
Also, make sure you compile the project before trying to run your code.
That can also point out problems...Debug | Compile VBAProject.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Does the workbook function on your computer but not on other computers?

No... The same error occurs on whatever computer I run it on.
What does the "Option Explicit" actually do?

Thanks for the pointer - I'll give it a try on Monday!

 

[Dom]

1. Option Explicit will catch any misspelled variables.
You could think you are referring to one thing and Excel
could be seeing it as something else... lngNum vs. 1ngNum for instance.
2. It will also force you to declare all of your variables.
Undeclared variables become Variants, not always a good thing.
Also, make sure you compile the project before trying to run your code.
That can also point out problems...Debug | Compile VBAProject.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Jim Cone wrote:




No... The same error occurs on whatever computer I run it on.
What does the "Option Explicit" actually do?

Thanks for the pointer - I'll give it a try on Monday!

The code itself runs fine... The form does it's job perfectly, it's when
the excel file is actually closed that the problem occurs - Excel
crashes and auto-recovers the file. It doesn't affect the
functionability of the form but is REALLY annoying when trying to
shutdown... I'm guessing it can't happen to everyone else, but I can't
see what might cause it. Would an undeclared variable cause this?

 

[Jim Cone]

re: "Would an undeclared variable cause this?"

I don't know.
It might be interesting to see what kind of problems occur
when unloading UserFormI instead of UserForm1...
It is hard to diagnose the illness without seeing the patient.
Jim Cone


"Dom" <[email protected]>
wrote in message...

1. Option Explicit will catch any misspelled variables.
You could think you are referring to one thing and Excel
could be seeing it as something else... lngNum vs. 1ngNum for instance.
2. It will also force you to declare all of your variables.
Undeclared variables become Variants, not always a good thing.
Also, make sure you compile the project before trying to run your code.
That can also point out problems...Debug | Compile VBAProject.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Jim Cone wrote:




No... The same error occurs on whatever computer I run it on.
What does the "Option Explicit" actually do?

Thanks for the pointer - I'll give it a try on Monday!

The code itself runs fine... The form does it's job perfectly, it's when
the excel file is actually closed that the problem occurs - Excel
crashes and auto-recovers the file. It doesn't affect the
functionability of the form but is REALLY annoying when trying to
shutdown... I'm guessing it can't happen to everyone else, but I can't
see what might cause it. Would an undeclared variable cause this?

 

[Bernd Pollermann]

Dear all,

from the previous contributions on the "Exiting a macro" I conclude
that it is impossible to leave a macro immediately in case of
an error in a subroutine.

Is this really true??

Please have a look at the following simple structure of a macro which
is consisting of a large number of subroutines:
''''''''''''''''''''''''''''''''''''''''''''''''''
Sub myMain()

Call mySUB1
Call mySUB2
..
..
Call mySUBn

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''
My aim is to keep the macro as simple as possible in case it detects an
error in a (sub-)subroutine.
I would like to have only one single type of behaviour:

1) tell the user which error occurred
2) stop the macro altogether

in my case the primitive behaviour is justified because errors will
be rare but always have to be corrected manually.

From what I understood the only way is to do it like follows:
''''''''''''''''''''''''''''''''''''''''''''''''''
Global GiveUp as Boolean
Sub myMain()

GiveUp = False
Call mySUB1 : If GiveUp Then Exit sub
Call mySUB2 : If GiveUp Then Exit sub
..
..
Call mySUBn

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''
and set GiveUp = True in case one of the expected errors occurs.
This is ugly, and gets worse if the mySUB.. consist themselves
of subroutines.

Is there really no "Exit macro" type of statement?

Thanks in advance for either confirming my suspicions or
- better - showing me a way out

Best regards, Bernd