I have experienced some problems with writting formulas
in visual basic for
application in Excel. I have been searching all over the
net and posting
questions on communities, but without any help. I haop
that you can help me.


We 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??
Are you aware of that
bug - and do you 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 notecing 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").Range("B2").Offset(x,
y)
.FormulaR1C1 = "= R[-1]C[-1]"
End With
Next y
Next x

End Sub