Excel has encountered a problem and needs...

[Greg Wilson]

I have a project with a relatively large amount of code. I now often get the
message: "Excel has encountered a problem and needs to close. We are sorry
for the inconvenience." I have used Rob Bovey's Code Cleaner several times to
no avail. I have no OLE objects in the project. It doesn't happen
often/reliably enough to try process of elimination, selectively commenting
out code and testing if it still happens. So I cannot source the problem.

I think it only happens after working on the code, and if I get it to go
away and don't do any more work on it, it may be OK. At least once, I made
the problem go away by just opening a code module and closing it without
making a single change. Weird?

Moday is the grand debut !!!

Hoping someone can provide a list of the usual suspects or has some good
suggestioins.

Much appreciative.

Greg

 

[Ken McLennan]

G'day there Greg,

I have a project with a relatively large amount of code. I now often get the
message: "Excel has encountered a problem and needs to close. We are sorry
for the inconvenience." I have used Rob Bovey's Code Cleaner several times to
no avail. I have no OLE objects in the project. It doesn't happen
often/reliably enough to try process of elimination, selectively commenting
out code and testing if it still happens. So I cannot source the problem.

I once had this crop up quite frequently on one particular
project. Following advice from this group I set Excel to not run macros
on startup (I had a few autorun bits in there), loaded my workbook,
opened the editor and from the Debug menu clicked CompileVBAProject.
When that finished I then saved the file and reopened normally. Problem
solved. I later stuffed up the code again and had the same error appear,
but that procedure fixed it again. Dunno if yours is the same problem,
but the process is painless & can't hurt anything if you try it.

See ya
Ken McLennan
Qld, Australia

 

[JP]

When do you get the error -- when you are running the code or just
sitting in the workbook? If it happens when you are running the code,
try stepping through the code and posting the section that caused the
error.

Thx,
JP

 

[Jim Cone]

Hi Greg,

Re: "relatively large amount of code"
If you have individual modules greater than 64kb then you can have trouble.
If so, split up the code into additional modules.

You could also try...
1. exporting copies all of your forms to a file folder.
2. deleting your original forms.
3. importing the copied forms.

Even better...
After exporting all the forms, create a new workbook and start over.
Create all new modules by copying and pasting from Notepad.

Have you checked all of the forms for extra or unused controls?
That may sound dumb, but you will really feel dumb when you find some. <g>
(I know)

Have you tried turning on "Break on All Errors" in the VBE and running your code?
That can sometimes result in surprises.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Greg Wilson"
wrote in message
I have a project with a relatively large amount of code. I now often get the
message: "Excel has encountered a problem and needs to close. We are sorry
for the inconvenience." I have used Rob Bovey's Code Cleaner several times to
no avail. I have no OLE objects in the project. It doesn't happen
often/reliably enough to try process of elimination, selectively commenting
out code and testing if it still happens. So I cannot source the problem.

I think it only happens after working on the code, and if I get it to go
away and don't do any more work on it, it may be OK. At least once, I made
the problem go away by just opening a code module and closing it without
making a single change. Weird?

Moday is the grand debut !!!
Hoping someone can provide a list of the usual suspects or has some good
suggestioins.
Much appreciative.
Greg

 

[Greg Wilson]

Thanks Jim for responding. The error happens on opening the wb. AutoRecover
saves a version minus all the code!!! This happens only occasionally. Only
opening the wb with macros disabled and then either running Code Cleaner or
even just opening and closing the VBE makes it go away (the latter may not be
reliable).

I think your suggestions are the way to go. I was ultimately planning to
rebuild the whole thing once the final version was complete. Tomorrow, it
needs to be put into service, but only needs to be a good prototype version.

1. My largest module is a UF module and is 45 kb.
2. When creating complex UF's as in this case, I write a macro that creates
the controls programmatically to avoid the problem you mentioned plus others.
So there can't be any controls I'm not aware of. The main UF is as clean as a
whistle structurally.
3. I checked out "Break on All Errors". It triggers a whole slew of
expected errors in any addins plus the project, such as:

"On Error Resume Next"
Application.CommandBars("XYZ").Delete 'which may or may not exist
On Error GoTo 0

I unselected all addins and tested it. I found only one intentional error
similar to the above example. But this was when the problem was not
occurring. I will keep "Break on All Errors" set and see what happens.
4. All the above said, I still think there's some garbage somewhere that
rebuilding may fix. I will try your suggestion of exporting the forms,
deleting the originals and importing the copied forms. I though Code Cleaner
did similar though.

Thanks again.

Greg

 

[Greg Wilson]

Thanks Ken for responding. I did as you advised several times before posting
and the Comple VBA Project doesn't find any errors. The problem still occurs.
I think I'll have to go with Jim's suggestions. See my post to Jim.

Thanks again.

Greg

 

[Greg Wilson]

Thanks JP for responding. It happens on opening the wb. The Debug toolbar's
Compile VBAProject finds no syntax errors in the project. The Workbook_Open
event only does a few things, all of which are correct. The problem only
happens occasionally. This makes me think it's due to some form of
corruption. See my post to Jim.

Thanks again.

Greg

 

[Ken McLennan]

G'day there Greg,

Thanks Ken for responding. I did as you advised several times before posting
and the Comple VBA Project doesn't find any errors. The problem still occurs.
I think I'll have to go with Jim's suggestions. See my post to Jim.

Thanks again.

Not a problem. I'm good at useless advice. Just ask my wife.

At least there were no loud explosions with Excel smeared over
your office walls

See ya
Ken McLennan
Qld, Australia.