EXECEL - Does excel have a max file size??

ntyburski
ntyburski used Ask the Experts™
on
I have created an extremely interactive workbook with over 50 sheets and tons of vba, etc.  Excel has already worned me that I have a max cell format limit of 4000.  Which I figured out away around that!  But now, my workbook is approximately 6MB and I am having trouble opening it.  After I open the file and enter in the password to open the workbook, it acts as though it is opening but all i see is a default grey background.  BUT, if I go to print preview I can see all of my sheets, etc, plus i can even print the entire workbook.  So, my questios is:

Why CAN I see it in print preview, print it out, etc but NOT actually see the workbook or change anything?  

I have already tried to debug excel, and reload MSO SR-1 Premium twice, and it is still doing it.  Therefore, I beleive it has to be the file, and possible its size and not a program glitch.??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ntyburski,
Why is it that big? i mean : does it make sens for you that it is 6mb?
'cause it has lots of data, lots of code? If not, maybe it would be a good
idea to transfer all code modules and sheets to a new blank workbook,
then save that one.
It might fix the problem.

Regards,
Sébastien
As an aside, 6 mb seems to small for it to be a size issue, i've seen maths models in excel of well over 25megs.

Is it something in your code that is perhaps hiding all the workbook? Is there an auto open module that runs code on entering? Does this code contain anything suspect?
Check out the trick given by sebastienm first as this is almost garanteed to reduce the size of your file.

What follows probably does not answer your question but in the spirit of sharing experience, they be of help to you when entering +5 Meg Excel territory. Take a look at point 5 in particular. You sound like you may have some tips to add to these.

Tips for reducing the size / improving the speed of your Excel workbook:

1. Extra worksheets take up a lot of space. More worksheets = step change in filesizes. Where possible try to stick to a scheema where you have a few very large "data" sheets and have seperate sheets for the user interface.

2. On a worksheet use up columns before rows where possible. Test this by saving a workbook with 200 columns x 10 rows of a's and then repeat with 200 rows and 10 columns. The 200 rows x 10 columns worksheet will be about 10% larger.

3. Workbooks saved with lots of hidden rows will open very slowly. Write a macro to hide them after opening and unhide them before saving.

4. If you have used a lot of space on a worksheet and have then deleted it, it will still take up a lot of room even though it has been deleted. Where possible, delete entire rows and columns to reduce the used range on your worksheets. This is one reason why the trick posted by sebastienm works very well.

5. VBA code. The rate limiting step in most VBA code in Excel is at the point where the code has to read or write properties (e.g. values) to the worksheet's cells and other objects.

A good example of this is where you need the values from a 10,000 x 1 cell range. You could cycle through each one of the cells to get its value independently which would require 10,000 interactions of VBA with the worksheet. The better method of getting all the values would be to set up a 1000 by 1 element array (note, MyArray(100,1), not MyArray(100) for this to work) you can then set the value of the array to the value of the range in one step. This places all the values of the cells in the range into the array. Use the reverse process for writing back to the sheet.

6. Turn off Application.ScreenUpdating when the user does not have to see what is going on.

7. Workbook/worksheet passwords. They tend to slow things down / mess things up / cause your computer to crash. Try to avoid them if you can. Maybe Zip the model and password protect or use some sort of password protection function in your code if you need the (very low) level of security that workbook passwords provide.

Best regards.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Top Expert 2006

Commented:
Hello ntyburski,any better now?
ntyburski:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Most Valuable Expert 2013

Commented:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Points split sebastienm & steve_randomno


Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

MASQUERAID
EE Cleanup Volunteer - Applications

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial