Excel/size of workbook
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.
' masterlistreplacement Macro
Application.Calculation = xlCalculationManual
Sheets("Spare sheet2rng").Range("A1:AG2020").Copy _
Application.CutCopyMode = False
ANSWER: Christopher Mitchell,
? (2^20) / 66660
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.
---------- 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.
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,
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).