Excel is closing itself...Yikes!!

[Guest]

Background:
For the better part of a year I've been developing a vba application.
I've got 48,000 lines of code in about 350 subs and functions in two modules.

I use Rob Bovey's code cleaner about every other day.
I've never developed a large application before, so I don't know if I'm
bouncing up
against 'limits' that vba has. A while ago, I had one procdure that vba
said was too big, so I broke it into two independant pieces.

In the main module, I have a declaractions section that's big. At least I
think its big.
it contains about 7 pages of variables, that are 98% constants, i.e. read only
infomation that the subs and functions use. The other 2% are 'real'
variables which I'm in process of dim'ing in the subs that use them.

Problem: In the last week, I've been getting a 'box' from Excel saying it's
encountered an error and has to close. I check the box to recover the
documents.
I have 3 excel workbooks open at the time. I took a look at the error report
that's produced but don't begin to understand it. Today for the first time,
after the recovery I got the "box" to close immediately after reopening the
workbook that has the code.

I've exported the code, rebuilt the main workbook by copying worksheets from
backup copies, imported the code, but that does not seem to consistently
help.
I make a lot of use of one and two dimension arrays, and there is some
re-dim'ing going on. The only theme I can discern re: the "excel must
close" box is that it seems to occur after a progamming error dealing with an
index to an array being invalid.

Question: What are some of the things I should be looking at to fix this?
I really don't know where to begin. Is the app too big? Any help is greatly
appreaciated. If you need more info from me, please ask.
thanks much,

 

[DAV135]

I had this problem a while ago, can't remember exactly what it was but
think it may have been something to do with the pc capabilities or an
error which it couldn't handle (something corrupt within the file).
First, you'll need to tracj the problem, easiest way to do this is put
msgbox's throughout your code. Find where your last msgbox appears
before it errors and then put them in more frequentyly. Shouldn't take
too long to track down and if it does appear to be valid code (try this
as a smaller sub as a test) then the chances are you're working with a
corrupt file.

 

[Zone]

More suggestions. Do you have an open workbook event or an auto_open
sub or something else that fires when you open your main workbook? Try
this: Open Excel by itself with no file. Click on File on the menu.
The name of your workbook should be in the list of recent files. Hold
down Shift and click on the name of the workbook to open it without
firing the events. If this works, the problem is probably in one of
your open workbook event procedures, or possibly a worksheet on
activate procedure. Zone