Excel 2002 has encountered a problem and needs to close /Code Clea

[Guest]

I have an Excel 2002 workbook that intermittently gets the above error
message ("Excel 2002 has encountered a problem and needs to close") and
corrupts.

The workbook contains class modules, standard modules and userforms. Most
frequently the error message occurs when opening the workbook and clicking a
button on the userform that opens on entry (this button simply says “exitâ€
and unloads the form). This isn’t always the cause and sometimes may trigger
when updating links, or clicking on another button outside if the userform.
The spreadsheet is not shared, and is used my many users across a network (ie
only one person can have write access at any one time).

If I run Rob Bovey’s Code Cleaner against this workbook (once it has been
corrupted) on entering (before clicking any buttons) then it resolves the
problem. However a few days/weeks/months later the problem may re-occur
despite the fact that no new vba code has been added or edited. I was under
the impression that the code cleaner only cleans stuff from vba memory that
accumulates when new code is added? If this is true then how does it resolve
my problem if nothing has since accumulated (because I have added no new
code)?!

Is there some way for me to identify what the root cause for the problem is?
Does stuff accumulate in memory if perhaps I have not set variables to
nothing at the end of procedures for example?

Is there a way for me to run this code cleaner against a spreadsheet
automatically every time the spreadsheet is opened? The spreadsheet is very
critical and doesn’t inspire user confidence if it crashes - this is the only
way I can think of to make sure the spreadsheet doesn’t crash?!

HELP!

 

[Guest]

Futher to the below, I have noticed that simply saving the workbook before
attempting to run any macros also seems to 'uncorrupt' the workbook so that
the next time it is opened it does not crash when using macros? maybe I
should set the workbook to save on opening (if not opened read only)?? This
of course would still leave the workbook liable to crash when opened as read
only!

 

[Peter T]

I don't have a clue as to what's wrong with your workbook or project. But it
might be worthwhile to rebuild a totally new wb from scratch. Don't copy
sheets but should be OK to copy cells.

Drag modules between projects, copy/paste any code in thisworkbook or sheet
modules.

Regards,
Peter T

 

[Guest]

May be worth a try but this will take me quite some time to accomplish for
each of the workbooks!!

One nasty bit I can think of are named ranges....I have loads of named
ranges in my workbook. Is there a way to copy the named ranges across
without them still referencing the old workbook that I am copying from? And
if I am copying the cells from one worksheet at a time these will often
refernce named ranges in other worksheets that do not yet reside in the new
workbook becuase I havent copied them yet! Not to mention the dynamic ranges
that dont appear in the worksheets themselves!!

I am thinking that as the problem seems to dissapear when the file is saved
first, is there maybe some issue with the user saving from different PCs that
may have actually caused this? and that it gets corrected when it is saved by
the machine that then has it open?!

 

[Peter T]

Yikes, you have same problem with all your workbooks!

I have no idea if building a new wb will solve but from what you described
I'd be tempted to try that, at least with the most troublesome wb.

For your sheets names & cells some ideas in the following -

Sub test()
Dim i As Long
Dim ws As Worksheet
Dim wbOrig As Workbook
Dim wbNew As Workbook
Dim nm As Name
Dim nmNew
Dim wsNew As Worksheet

Application.Calculation = xlCalculationManual
Set wbOrig = ThisWorkbook
Application.SheetsInNewWorkbook = 1
Set wbNew = Workbooks.Add
Application.SheetsInNewWorkbook = 3

For Each ws In wbOrig.Worksheets
i = i + 1
If i = 1 Then
wbNew.Worksheets(1).Name = ws.Name
Else
wbNew.Worksheets.Add(after:=wbNew.Worksheets(i - 1)).Name = ws.Name
End If
Next

With wbNew.Names
' if not 100% sure the nm.RefersTo string is less than 255
' don't use this (could be a lot more work involved)
For Each nm In wbOrig.Names
..Add nm.Name, nm.RefersTo
Next
End With

Application.DisplayAlerts = False
i = 0
With wbNew
For Each ws In wbOrig.Worksheets
i = i + 1
ws.Cells.Copy .Worksheets(i).Cells
Next
End With

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

Regards,
Peter T

 

[Darren Hill]

This macro you've posted could be a lifesaver for me, as I'm having a
very similar problem to the original poster, but only with one workbook.

Will this line:

ws.Cells.Copy .Worksheets(i).Cells

work for merged cells?
If not, what code would I need for that?

Thanks,
Darren

 

[Peter T]

Hi Darren,

I am relatively confident the line
ws.Cells.Copy .Worksheets(i).Cells
should work fine, even for quite complex areas of merged cells. But please
post back if you find it doesn't copy all correctly.

Notwithstanding, I should emphasize the macro was quickly put together,
lightly tested and as I said "for ideas". So you will certainly need to
check the integrity of the new workbook.

The code would need extending to make similar Worksheet controls (they won't
be copied), any macro onaction strings in buttons/shapes linking to own
workbook would need editing.

Regards,
Peter T

 

[Darren Hill]

That's good to hear. Regarding the quickly put together-ness of the
macro, don't worry, I won't blame you when it all goes pear-shaped

My workbook relies on activex controls on forms, rather than worksheet
controls, shapes, and onaction events, so I should be okay on that front.

Thanks,
Darren

 

[Guest]

Thanks for the suggestions Peter T.

When I have time to check the newly created workbook is exactly in synch
with the old one then I will see how that workbook behaves itself. The
trouble in testing the success of this unforunately stems from the fact that
I do not know the root cause and wont be able to say it's a success until it
has run over a few months without corrupting! (as it infrequently happens).

Although I may of course be able to say that it wasnt successful before
that!! If it doesnt work then I may be forced to set the workbook to save on
opening as that seems to rectify whatever problem Excel has.

Regards,
Peter