Why does Excel File Size explodes when using VBA?!

[Ekelund1]

Ekelund1 (TechnicalUser) Nov 9, 2003
I have experienced some problems with writting formulas in visual basic for application in Excel.

I have a rather large spreadsheet which is automated with visual basic modules. The problem is when we write the formulas to the spreadsheets in the VB module the spreadsheet file ends up being VERY large - usually
resulting in Excel crashing.

If the workbook with the formulas entered by VBA is saved, closed, opened and saved again the size of the file is about 1/20 of the size before. Or if the worksheets with the formulas that VBA wrote is copied and then deleted -
again the file size is again normal. How can that be?? and does anybody have a solution??

We have made a workarraound, but as soon as the spreadsheets reach a certain size it still causes Excel to crash. Any help would be appriciated!!

You can test the problem easily by running this simple code, saving the workbook and noting the size of the file. Then close the file, open it again and save and see how the file size is reduced.

Sub test()

For x = 1 To 100
For y = 1 To 100
With Worksheets("sheet1&quot.Range("B2&quot.Offset(x, y)
.FormulaR1C1 = "= R[-1]C[-1]"
End With
Next y
Next x

End Sub

Thanx!
Ekelund1

 

[simeybt]

i tryed this but the file only got 20k or so samller than the orignal. You could try coping the contents of you file into a blank excel spreedsheet and then saving the file again. do not complie your VBA code as this will make the file bigger again.

 

[Ekelund1]

Exactly - in this example it is only 20K bigger. Nut in the real model we are talking a file of 60 MB which is reduced to 5 MB!!! See the problem??

There must be a way to trigger the same procedure that reduces the file after copy or close,open,save agin?!

 

[RamziSaab]

i am not sure but i think is you save an excel file it keeps in memory all previous changes....

Try using save as and put it as a new name this might help...or mosre likely then not it will make no difference...anyway its worth a try...

 

[Ekelund1]

Didn't help I am affraid! Any other suggestions?

 

[pvw]

Hi Ekelund1

Your sample macro writes 10'000 formulas into cells. A formula is saved as part of each cell. This means that each cell has its own formula information, when you save the book. Because the formulas are all similar to each other (they are repeated), Excel can handle them as shared formulas. A shared formula is stored only once in the workbook and shared with each of the cells that require the formula. When you save the book, Excel does not convert each saved formula to a shared formula. So, the file size of may grow to an unexpected file size. To conversion does only takes place when you open a book. This means, when you close and reopen your workbook, the formulas will be converted to a shared formula - and when you then save and close the book, the file size is smaller than before. This behaviour is in all versions of Excel.

I tryed your macro in Excel 2000. The file size was 368 KB. After closing, reopening, saving and closing, the new file size was 332 KB (36 KB less, 9.8 %).

As a workaround, fill only a part (some hundreds or thousands) of your formulas into the book, then save, close, reopen and save again. After this, write the next part of the formulas (and so on).

BTW: Set the calc mode to manual before executing the macro. The macro then needs only 20 % of the time, when the calc mode is automatic. But this has no affect to the file size.

Philipp von Wartburg

 

[RobBroekhuis]

I'm sure your actual application is much more complex than your example, but perhaps it's worth mentioning that you can write the code in your example:

For x = 1 To 100
For y = 1 To 100
With Worksheets("sheet1&quot.Range("B2&quot.Offset(x, y)
.FormulaR1C1 = "= R[-1]C[-1]"
End With
Next y
Next x

simply as

worksheets("sheet1&quot.range("B2&quot.range(cells(1,1),cells(100,100)).formulaR1C1="=R[-1]C[-1]"

I think this avoids some of the bulking up.


Rob

 

[Ekelund1]

Pvw... that explains it. But is there any way I can execute the procedure Excel runs on open to covert them into shared formulas? Or is there another way to write the formulas so that they are saved as shared formulas??

Rob... yes I know and in the much more advanced code all formulas are written to ranges and not single cells to better performance. This was just a simple example to illustrate the problem.

 

[Chattin]

A couple of links you may find handy

http://www.microsoft.com/europe/vba/resources/downloads/125.htm

http://www.appspro.com/utilities/Cleaner.asp

HTH

Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help

 

[pvw]

Hi Ekelund1

There is no way to force Excel to convert the formulas. The conversion runs only when you open a book in Excel 5.0 or later, which was saved in Excel 5.0 or later workbook format. The 'shared formulas' method is a feature which was introduced in Excel 5.0 and is an internal function called by the open process.

I found two articles in the MS KB:

http://support.microsoft.com/?scid=kb;en-us;214310

http://support.microsoft.com/?scid=kb;en-us;824837

According to these articles, I guess that there is no possibility to enter formulas explicitly as shared formulas.

Philipp