VBA project instability

[Ken Dahlberg]

EXCEL 2002. This is a follow-up to another post of mine that hasn't
appeared yet: "Whose error: Mine or Excel's?". That was about the
same problem described here, but some of the information given there
is probably irrelevant.

I'm not really sure what is relevant. The workbook in question has 7
visible worksheets and 3 hidden worksheets. There are about 70
embedded controls spread over 3 of the worksheets that also contain
one embedded chart each. The size of the .xls file is about 1 MB. So
it's not a huge project.

OK, here's the problem. I add a new CommandButton control to one of
the worksheets that already has a chart and 21 other controls. I
don't do anything else - no code added behind the button. I save the
workbook, close it, and open it again. Now, I get the dreaded box
that says "Microsoft Excel has encountered a problem and needs to
close. We are sorry for the inconvenience". And if I follow the
option to "Recover my work and restart Excel", it opens the file with
ALL the VBA code stripped out. This happens even if I disable macros
on startup.

I can add a CommandButton and even a couple of extra OptionButtons to
either of the other two worksheets containing charts and controls, and
the project survives. So there's something about the particular sheet
I'm trying to change. But I'm clueless what else to try, or what
information to provide here, unless someone knows enough about this
kind of problem to ask the right question.....???

Thanks,
Ken Dahlberg

[keepitcool]

excel makes extensive use of the temp directory for displaying
embedded controls.

excel is known to crash if the temp directory contains many files.

in other words:

either force regular cleaning of the temp directory
dont use embedded controls

Open the temp dir in the users profile in explorer.
delete all files and directories. (some are locked by windows.)

start excel, open your book:=>

now have a look at that tmp directory.
see all those emf and tmp files?
NOW you know what i avoid embedded controls like the plague.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

[John Wilson]

Ken,

I have a similar problem with a rather large workbook that I use.
For some unknown reason, whenever I add a control to certain
UserForms, I get that same dreaded message.

One thing that always helps. I use it sporadically on other
workbooks but always use it on this one is Rob Bovey's
"Code Cleaner". It works for me every time.

You can get a copy of it here:
http://www.appspro.com/

John

"Ken Dahlberg" <kedahlb...@yahoo.com> wrote in message
news:39bf9b19.0310...@posting.google.com...

[Ken Dahlberg]

keepitcool,

I see what you're talking about. I deleted several thousand files
from this temp directory, including many that looked like my embedded
controls, multiplied by several working copies of the workbook. I
expected, then, to be able to add the extra controls I needed. But no
such luck. The workbook still dies the same death.

Unfortunately my client wants controls embedded on his worksheets
instead of in userforms. If there is no workaround for this then I
will just have to educate him.

Thanks for the insight.... any other ideas??
Ken

keepitcool <xrrcv...@puryyb.ay> wrote in message news:<Xns940DF2CF746...@207.46.248.16>...

[Ken Dahlberg]

John,
I use the "Code Cleaner" religiously, but it's not doing it for me in this case.
Thanks,
Ken

"John Wilson" <jwi...@optonline.net> wrote in message news:<#cYNj7Rj...@TK2MSFTNGP12.phx.gbl>...

[Tim Zych]

Does switching from ActiveX to Forms button(s) help at all?

"Ken Dahlberg" <kedahlb...@yahoo.com> wrote in message

news:39bf9b19.03100...@posting.google.com...

[keepitcool]

Ken:

try to stick to data validation and excel's builtin controls from the
forms toolbar as opposed to the embedded contols from the control box
toolbar.

Apparently you also create controls "on the fly"?

I think that stability improves if you work with an existing control and
not delete/create controls over and over again.

As you may have noticed Excel doesn't renumber some of the collection
indexes for these objects. You'll be inserting Shape 4567 before you
know it... During development and testing I've seen totally empty books
(1 module, 1 sheet, 100 cells in usedrange) which when saved where 1
Megabyte or more.

Ideas for repairing:

Also note the VBA cleaner just cleans out code modules and userforms.
Then saves an uncompiled copy of it. It works very effectively on
userforms, but cannot clean a worksheet.

I have seen but never used some workbook repair utilities. Maybe those
can be effective. Since the number of sheets isn't that high yuo may
well do it manually.

create a new workbook and carefully copy over the cells to new sheets.
Then copy the objects copy the code. You cant use copy sheet in this
case as you'll copy over the errors.

I'd be happy to have a look at the thing. Zip and mail pls.

[Ken Dahlberg]

KeepItCool,

Several people have suggested using Forms controls instead of Controls
toolbox controls. I'll give this a try. And I'll try cleaning the
particular worksheet where the problem occurs, following your
description.

I don't much want to have someone looking into the guts of this
particular item because it isn't a pretty sight. There are plenty of
things in there that need straightening out, that I don't have time to
mess with in this phase of the project. It is most generous of you to
offer to look at it.

Regards,
Ken

keepitcool <xrrcv...@puryyb.ay> wrote in message news:<Xns940E5C71BDF...@207.46.248.16>...

> Ken:
>
> try to stick to data validation and excel's builtin controls from the
> forms toolbar as opposed to the embedded contols from the control box
> toolbar.
>
>

[Ken Dahlberg]

A final followup: I cleaned the one worksheet where the problem
started, per your description. Then I was able to add the last few
controls to complete the project... I had been unaware that the Bovey
"code cleaner" didn't handle the worksheets.

It's not my preference to use so many embedded controls. If I need to
do it again I'll try to stick to "Forms" controls.

Many thanks for all the help.

Ken D

keepitcool <xrrcv...@puryyb.ay> wrote in message news:<Xns940E5C71BDF...@207.46.248.16>...

> Ken:
>
> try to stick to data validation and excel's builtin controls from the
> forms toolbar as opposed to the embedded contols from the control box
> toolbar.
>

[keepitcool]

Good to hear your problem is solved.

For good order:
Bovey's Code cleaner is as the name implies: it cleans code.

Afaik it does clean a sheet's code. Not the sheet or its objects.

What is does is automate the export & reimport of all the userforms and
code modules from a VBAproject (which you could do manually from the
VBE)..

It definitely has been demonstrated to have a healing & shrinking effect
:)

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

[TroyW]

>> Afaik it does clean a sheet's code. Not the sheet or its objects. <<

I don't believe Rob's code "cleans" the sheets. My understanding is that the
"cleaning" effect is achieved by removing the code module from the project.
This apparently results in Excel doing some cleanup internally. Then you
import the code back into a blank NEW code module.

I don't believe the Excel VBE "Remove" command works on the sheets. Hence,
no cleaning is possible. If you export the code modules, you'll notice the
sheets are saved as a .cls and the modules as .bas. I don't remember if
Rob's program will remove comments from the sheets, but this is not same
thing as "cleaning". Cheers.

Troy

"keepitcool" <xrrcv...@puryyb.ay> wrote in message

news:Xns940EEA4348B...@207.46.248.16...