Excel/size of workbook

Advertisement


Question
QUESTION: Hi Tom

I have a workbook that is 9.6mb in size.  Everytime I run this simple piece of code the file increases in size.  I opened the file and deliberately ran the code 40 times.  This added 1mb to the size of the workbook once it was saved.  

Could you provide a possible explanation for this?

Thanks in advance.

Chris Mitchell

Sub masterlistreplacement()
'
' masterlistreplacement Macro

  Application.Calculation = xlCalculationManual
  Sheets("Spare sheet2rng").Range("A1:AG2020").Copy _
     Sheets("Sheet2rng").Range("A1")
    
  Sheets("Choose class").Range("B4").Copy

   ActiveSheet.Paste
   Application.CutCopyMode = False
   Selection.ClearContents
  ActiveSheet.Calculate
 
  
End Sub

ANSWER: Christopher Mitchell,

? Range("A1:AG2020").Count
66660
? 2^20
? (2^20) / 66660
15.7302130213021

2^20 is one MB


so you copy 66660 cells.  If you increase the amount of data excel has to track, then that could cause an increase.  (I don't know what was in the destination before you ran the macro).  There are also formatting considerations.  If you have individual cell formats that are copied that are different from what was there, that could increase size.  Or if  some areas were formatted as a group and the copy causes individual formatting to take place, then that could contribute.  

If you want, the file is stored as XML which is a text format.  It is compressed but can be uncompressed with winzip.  So you can have before and after versions of the file and take a look.  I have never had any reason to do it, but that is what I understand.

You say you ran the code 40 times, but did the increase only occur the first time or was a little added each time.  

Rob Bovey created a code cleaner routine that exports all you VBA modules, deletes the ones in  your file, saves the file, then imports the VBA.  The thought being that there is some "junk" which accumulates from VBA.  In Microsoft Access, there is an option to compress the file to clean up overhead, but this is not an option in Excel.  Now these were done back when Excel had a binary file format.  I don't know if this is not a problem with the XML formatted files.

Also, sheet protection incurs an algorithm that encodes the data associated with a sheet.  If you are protecting and hiding and so forth, this could incur a difference.

I have saved a file in a different version of excel and sometimes see a big difference in file size - if I am paying attention.  Even though you are not doing that, it just demonstrates that there can be variability in size.  If you have been using this file a long time and if it would grow each time, then it seems it would be a lot bigger than 10MB or so.  

--
Regards,
Tom Ogilvy
 




---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Thank you for your comprehensive consideration.  Rather than have the whole of the department's data in each file, I have given each teacher the opportunity to select only their classes in their workbook.  The macro is designed for those who make a mistake and wish to start again with the original data.

So the macro copys over whatever was in the destination file.

The file size increases incrementally each time the macro is run.  I do not anticipate individual teachers running the macro more than a couple of times during the academic year.

It's just that the increase in size slows the workbook down.  It also seems irrational seeing that I am seemingly just replacing data.

I will implement the suggestions that you have made.

Thanks again

Chris Mitchell

Answer
Christopher Mitchell,

If people are reducing the amount of data, then they should do so by deleting entire rows.  Then when done, when they save the workbook, it should reduce the size of the workbook.

If you are concerned about speed, then you can visit Charles Williams' site,

http://www.decisionmodels.com

He has extensive information on excel performance including an application called FastExcel which allows you to analyze your formulas. (this is not a free application, but the information on his site has no cost).

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.