Why does Excel File Size explodes when using VBA?!

[Ekelund1]

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

 

[johnwm]

Hi and welcome to the forum. To get the best from the forums, I suggest you read faq222-2244, particularly paragraph 3.

As your question is a specific VBA question, and as this is the VB6 forum, you may do better to post in the VBA forum, forum707

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'