[RESOLVED] frustrating excel crash

[pgag45]

This one really has me baffled...

I've been working in an Excel 2003 VBA project for over a year, and just recently having some very strange bugs.

I have older versions (by just 2 days) of my project working perfectly.. but now after some quick edits, the most simple of simple stuff is crashing Excel.

A simple Sheets.Add completely crashes Excel, not VBA. I know it's not the code, but something must have changed... I have the same exact references as other workbooks that work just fine. The only thing I can even think of is that there is something wrong with my Excel or something on the machine I am developing on...

Can anyone shed some light on this issue?

Thanks

[pgag45]

hmm.. in my Microsoft Excel Objects under the VBA project I have 16 Sheets... am I hitting some sort of maximum Sheet count?

[pgag45]

eh yeah I'm starting to think that only 16 Sheet Objects are allowed... Does this seem low to anyone? Can anyone confirm this?

this is in Excel 2003

[si_the_geek]

That sounds extremely low, I've often had files with far more sheets than that (the highest I can remember is about 40).

I'm not sure what the problem could be tho.. perhaps there is a limit on the file size? ..or perhaps a limit to the number of objects (including forms & charts etc)?

[Siddharth Rout]

I've been working in an Excel 2003 VBA project for over a year, and just recently having some very strange bugs.

I would suggest a complete re-install of office 2003. It is a much simpler option than to find the bug...

Regarding the maximum number of sheets that you can have in excel, it is infinite. When I say infinite, it means you can have as many sheets as the memory in your computer allows....

[pgag45]

yeah as I continue to test.. it doesn't seem like 16 sheets is any kind of limit... kind of baffling.

my workbook is currently at 28 mb.. but I've had it much bigger than that, so I'm sure it's not that.... Koolsid you think something could have corrupted my Office?

[dmaruca]

I wouldn't be so quick to do an Office re-install. You say that previous versions work fine, so that makes me believe your install is fine. There are lots of things that can cause crashes.

I'm thinking something happened to corrupt the document itself. You should try some things to get rid of the corruption. You could try saving the xls file as xml and seeing if that helps. If not, try migrating all of the code and data to a new workbook one object at a time. if possible. This can help pinpoint what is causing the crash.

This may not be related, but having the document saved too deep in the directory tree where the full path name very long has caused crashes.

[pgag45]

hmm... yeah I don't think it's any specific object or code throwing the error, I'm thinking more the workbook in general. I will try migrating the code over to a new workbook if I can't figure this out.

Appreciate the help..

Does anyone know the conditions when a Sheets.add will crash? MS documentation is of no help.. as usual.

[dmaruca]

That's never happened to me - the sheets.add crash. Does it crash of you do the same thing manually? Just curious.

[anhn]

This is all Excel 2003 specifications and limits.

Reinstall Office is an ill-advice and that won't fix the problem. The problem is in that particular workbook, no problem with other workbooks.

Beside touching Excel limitations, usually a workbook with code may contains a lot of garbage particularly with large file after multiple modifying and compiling.

You should try "Clean-up" the workbook. Within Office suit, Access has a command line option to "Decompile" the file but that is not available for Excel.
You can find the code to help you to do so (I am intending to write an Add-in for this) or you can do it manually like this:

1. Copy the problem workbook, called BookA, to BookB.
2. Open both BookA and BookB in the same instant of Excel.
   
   In VBA Project box:
   
3. Remove all forms, modules and class modules from BookA.
4. Delete all codes in Sheet modules and ThisWorkbook module.
5. Compile BookA
   
   Now, BookA is clean, it has no code al all.
   
6. Drag ALL forms, modules and class modules from BookB to BookA.
7. Copy all codes in Sheet modules and ThisWorkbook module from BookB to BookA with matching modules.
8. Close BookB without Save.
9. Compile BookA again.
10. Save BookA.

You can compare the file sizes of BookA and BookB now to see how much different.

[Siddharth Rout]

my workbook is currently at 28 mb.. but I've had it much bigger than that, so I'm sure it's not that.... Koolsid you think something could have corrupted my Office?

By this time you would have installed office....

Anyways, if you want to find the actual problem, mail me the file at the email address which I have pm'd you...

Also just to confirm, you are having this problem with the rest of excel files as well. right?

EDIT

After reading Si's opinion, if the problem exists only in this workbook, then you don't need to re-install the office. simply mail me the file and I'l check it for you...

[si_the_geek]

Other files apparently work fine - it was mentioned twice in the first post.

As such, I don't see how re-installing Excel could help.

[pgag45]

thanks for all the feedback! I will try anhn's method and see if that helps... Unfortunately I cannot email the file Koolsid, although I appreciate the help. How do you check the file to find the problem?

[Siddharth Rout]

thanks for all the feedback! I will try anhn's method and see if that helps... Unfortunately I cannot email the file Koolsid, although I appreciate the help. How do you check the file to find the problem?

First of all I wanted to check how is the file size so high?

If you search the forums, we have already tackled this issue before. File size can increase due to many reasons

1) VBA Codes/Forms/Modules (like Anhn suggested)
2) Too many Formulas
3) heavy Conditional Formating / Validation
4) Use of Shapes/Ole objects in a worksheets
5) Using Colors

etc etc

Try out Anhn's method above but if the file is not heavy owing to the 1st reason as mentioned above then his suggestion won't help...

[pgag45]

worksheet count changes.. but usually varies from 15-20 sheets... some with over 10,000 x 15 rows of information. In development I usually have those sheets cleared or filled with minimal data.

Also I have around 50,000 lines of codes.. some of which could be condensed, but this is rather large project. The code is either in the 60 odd userForms or 2 modules.

Also, have a number of images on some of those userForms.

Is this project getting too big for Excel VBA?

[Siddharth Rout]

Also I have around 50,000 lines of codes.. some of which could be condensed, but this is rather large project. The code is either in the 60 odd userForms or 2 modules.

It is indeed a large project

Did you try Anhn's suggestion?

[pgag45]

Ok... so I just reverted to a file that was a couple days older than the file which was giving me problems and this appears to be working fine.

Thanks for all the help! and hopefully this problem doesn't come back (knock on wood...)