I have a workbook out in the field that is 3.5-4 MB, depending on how
compiled the VBA is. Right now I am getting complaints of it crashing
Excel. The solution is to close Excel without having it try to recover,
then do an Open and Repair on the last saved file. But, of course, users
don't like this.

The Open and Repair leaves it with no compiled code. I can run a VBA Code
Cleaner and achieve about the same thing. Or I could clean the code and
then fully compile before I release it to the field. Right now it is
semi-compiled, meaning I have not hit the compile menu selection since my
last clean, so only the popular VBA code has been compiled when it was run.

So my question is what can I do to maximize its stability?

Don <www.donwiss.com (e-mail link at home page bottom).

On Sun, 05 Jun 2005 21:41:56 -0400, Don Wiss wrote:

I have a workbook out in the field

It's xl2002.

Don <www.donwiss.com (e-mail link at home page bottom).

Don,

Some thoughts that came to mind, after reading your post...

The workbooks won't be out in the field for very long if
they are crashing Excel. Nobody will use them.

I know of no reason, not to fully compile a workbook's code
before using it. It speeds up the initial operation of the workbook
and if you do it before release, can let you know if there is a
problem with the code.

Did you test the workbook on the same operating systems
that the people in the field use and with the their XL version?

Have you observed any of the people in the field actually use
the workbook? You could be very surprised as to what
"users" think the normal way to do things is. I would bet,
in most cases, that, the programmers vision of how the masterpiece
will be used will be blown away in the first hour.

Did you clean up the code modules before the release,
by copying the code to notepad, removing all the modules,
inserting brand new modules and pasting in the saved code.

Is it possible to remove the essential formulas and text from
the workbook and start over with a brand new workbook?

Have you set down and studied the code you wrote?
I find printing it out and reading it while eating breakfast
can sometimes be very enlightening. Have you had
another programmer look at it?

It is incorrect code, a corrupt workbook or a
combination of both that can cause problems.

Hope this outburst is of some help. Best of luck with the workbook.

Regards,
Jim Cone
San Francisco, USA




"Don Wiss" wrote in message
...
I have a workbook out in the field that is 3.5-4 MB, depending on how
compiled the VBA is. Right now I am getting complaints of it crashing
Excel. The solution is to close Excel without having it try to recover,
then do an Open and Repair on the last saved file. But, of course, users
don't like this.

The Open and Repair leaves it with no compiled code. I can run a VBA Code
Cleaner and achieve about the same thing. Or I could clean the code and
then fully compile before I release it to the field. Right now it is
semi-compiled, meaning I have not hit the compile menu selection since my
last clean, so only the popular VBA code has been compiled when it was run.

So my question is what can I do to maximize its stability?

Don <www.donwiss.com (e-mail link at home page bottom).

On Sun, 5 Jun 2005 19:33:39 -0700, Jim Cone wrote:

The workbooks won't be out in the field for very long if
they are crashing Excel. Nobody will use them.

They don't have a choice. It is the required tool for pricing their type of
business. They don't all blow up Excel. When they do it is unpredictable.

I know of no reason, not to fully compile a workbook's code
before using it. It speeds up the initial operation of the workbook
and if you do it before release, can let you know if there is a
problem with the code.

The users e-mail the workbooks around. Some code would rarely be run. Fully
compiling just before release makes it bigger. I do fully compile before I
code clean. And development has been limited in the past couple weeks. So
highly unlikely there is any code problem.

Did you test the workbook on the same operating systems
that the people in the field use and with the their XL version?

Yes. We all have identical XP SP1. SP2 is being tested now. All have
identical xl2002.

Have you observed any of the people in the field actually use
the workbook? You could be very surprised as to what
"users" think the normal way to do things is. I would bet,
in most cases, that, the programmers vision of how the masterpiece
will be used will be blown away in the first hour.

I am in phone contact with the users many times a day. The example that was
shown to me on Friday blew up Excel as he was opening it. He had received
it by e-mail.

Did you clean up the code modules before the release,
by copying the code to notepad, removing all the modules,
inserting brand new modules and pasting in the saved code.

That is the hard way to do it. VBA Code Cleaner does that all
automatically. It was run a couple weeks ago.

Is it possible to remove the essential formulas and text from
the workbook and start over with a brand new workbook?

Nope. It has 48 sheets.

Have you set down and studied the code you wrote?
I find printing it out and reading it while eating breakfast
can sometimes be very enlightening. Have you had
another programmer look at it?

I study my code all the time. I am a perfectionist. The code is clean. It
is fully commented. And it is organized in a way to eliminate code
redundancy. The calculations are checked by an actuary/programmer. There is
no one else around that comes close to my programming skills.

It is incorrect code, a corrupt workbook or a
combination of both that can cause problems.

I thought I explained that it is workbook corruption that I am trying to
minimize.

Don <www.donwiss.com (e-mail link at home page bottom).

Hi Don,

You mentioned that the workbook blowing up excel is unpredictable, but will
it affect the same user consistently? If so, can you have that user mail it
back to you and see if it blows up your Excel?

Do they have to do anything besides open it in order for Excel to crash?









"Don Wiss" wrote in message
...
On Sun, 5 Jun 2005 19:33:39 -0700, Jim Cone
wrote:

The workbooks won't be out in the field for very long if
they are crashing Excel. Nobody will use them.

They don't have a choice. It is the required tool for pricing their type
of
business. They don't all blow up Excel. When they do it is unpredictable.

I know of no reason, not to fully compile a workbook's code
before using it. It speeds up the initial operation of the workbook
and if you do it before release, can let you know if there is a
problem with the code.

The users e-mail the workbooks around. Some code would rarely be run.
Fully
compiling just before release makes it bigger. I do fully compile before I
code clean. And development has been limited in the past couple weeks. So
highly unlikely there is any code problem.

Did you test the workbook on the same operating systems
that the people in the field use and with the their XL version?

Yes. We all have identical XP SP1. SP2 is being tested now. All have
identical xl2002.

Have you observed any of the people in the field actually use
the workbook? You could be very surprised as to what
"users" think the normal way to do things is. I would bet,
in most cases, that, the programmers vision of how the masterpiece
will be used will be blown away in the first hour.

I am in phone contact with the users many times a day. The example that
was
shown to me on Friday blew up Excel as he was opening it. He had received
it by e-mail.

Did you clean up the code modules before the release,
by copying the code to notepad, removing all the modules,
inserting brand new modules and pasting in the saved code.

That is the hard way to do it. VBA Code Cleaner does that all
automatically. It was run a couple weeks ago.

Is it possible to remove the essential formulas and text from
the workbook and start over with a brand new workbook?

Nope. It has 48 sheets.

Have you set down and studied the code you wrote?
I find printing it out and reading it while eating breakfast
can sometimes be very enlightening. Have you had
another programmer look at it?

I study my code all the time. I am a perfectionist. The code is clean. It
is fully commented. And it is organized in a way to eliminate code
redundancy. The calculations are checked by an actuary/programmer. There
is
no one else around that comes close to my programming skills.

It is incorrect code, a corrupt workbook or a
combination of both that can cause problems.

I thought I explained that it is workbook corruption that I am trying to
minimize.

Don <www.donwiss.com (e-mail link at home page bottom).

On Sun, 5 Jun 2005, William Benson wrote:

You mentioned that the workbook blowing up excel is unpredictable, but will
it affect the same user consistently? If so, can you have that user mail it
back to you and see if it blows up your Excel?

Good idea. Since this is Notes he will still have the e-mail with the
embedded attachment and can forward it to me.

Do they have to do anything besides open it in order for Excel to crash?

Not in the one that was shown to me. I don't know about others, as this was
second hand info from the fellow that showed it to me. It just happens that
he is the manager over all the people in the field. It periodically also
blows up Excel for me. Whenever it happens is random. When it does happen
to me the first thing I do is to run the VBA Code Cleaner.

Don <www.donwiss.com (e-mail link at home page bottom).