Solved: Inefficiencies of Multiple Sheets

[Cyberdude]

Because Excel has been crashing on me lately, one of the things I started wondering about is the use of multiple sheets in a workbook. I typically have 8 to 10 worksheets, some large, some not so large.

So my exploratory question is:
Assumming I could put all the stuff I now have on 10 wrorksheets onto a single sheet, would that be more efficient AND less prone to crash errors?

Is there an optimum number of sheets to use, like maybe 5?

Does the video card have a harder time servicing 10 sheets than 1 sheet?

Curious Sid

[mdmackillop]

Hi Sid,
I'm running some 30-40MB workbooks with 30-40 sheets on Excel 2000 without crashing problems. Admittedly they are mostly record with little interlinking formulae.
While you could compile into one sheet, I suspect problems keeping things "straight" if you need to change the layout at all.
Regards
Malcolm

[Bob Phillips]

I cannot see how a single sheet will improve any stability problems that you have, but it will make things a lot more obscure and harder to maintain.

The problem must be elsewhere.

[Cyberdude]

Thanks for the opinions, guys!

Just trying to fill in some more blank spots in my understanding of how things work. I didn't actually think the multiple pages were causing my somewhat random Excel crashes, but the thought did occur. Strangely the crashes occur (I think) upon exiting an Excel session AFTER everything has apparently stopped. Short delay, then crash with catestrophic repair. It even trys to repair some workbooks I haven't even been using. (Very weird) Thank goodness for multiple workbook backups.

[JWhite]

Have you tried running VBA Code Cleaner? Simply running it against my VBA code has eliminated mystery catastrophic failures several times. It's a free download (www.appspro.com).

I've only been able to definitely isolate two causes of catastrophic failures and both were related to UserForms. In one case I re-named an existing UserForm and started getting crashes right away so I was able to trace the problem quickly. Re-naming it back solved the problem and I later found that it has happened to other people.

The other time was when I set up a combo box with 2 columns. I only wanted to show the first column in the UserForm but I wanted the second column to be the selection. The documentation wasn't clear whether I could do that but I tried it and it worked fine for months. I was having random crashes during that time but didn't relate it to that UserForm until I just happened to make a minor change in the form and exited and it crashed. I kept narrowing it down until I proved to myself that that particular combo box was causing it to crash IF it was the last thing open in VBA when I exited.

Bottom line: VBA has problems that it doesn't know about until it actually runs and crashes. My bet is there's something in your code which may be quite legitimate and yet VBA is having a problem with it. But start with VBA Cleaner.