Excel - huge file sizes

  • Thread starter Thread starter Ekelund
  • Start date Start date
E

Ekelund

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
 
Hi,
I tried your problem using the code given and discovered the following.....

1. The workbook when first saved was 369kB

2. After closing, opening and resaving the volume reduced to 332kB

With no further changes to the file, it has stayed at this value despite
opening and closing the file and Excel several times.

I do not appear to have the same problem - is it your operating system ?

I use Excel 2002 SP2 on XP system.

Ekelund said:
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
Click to expand...
 
I had a similar experience with regard to the initial and subsequent file
sizes. Copying the sheet and deleting the original sheet shaved another 1kb
or so.

I also noticed that Ctrl-Home takes me to cell A2 and Ctrl-End takes me to
cell CX2 which is not what I would expect.

I'm using Windows 2000 and Excel 2000 SP-3

Regards

Trevor


Nigel said:
Hi,
I tried your problem using the code given and discovered the following.....

1. The workbook when first saved was 369kB

2. After closing, opening and resaving the volume reduced to 332kB

With no further changes to the file, it has stayed at this value despite
opening and closing the file and Excel several times.

I do not appear to have the same problem - is it your operating system ?

I use Excel 2002 SP2 on XP system.

Ekelund said:
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
Click to expand...




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
in a different environment:
I had the same experience as Nigel

367KB after running the macro
330 KB after all subsequent opening, saves, closings.

Excel 97 SR2, Win 98 SE

--
Regards,
Tom Ogilvy

Nigel said:
Hi,
I tried your problem using the code given and discovered the following.....

1. The workbook when first saved was 369kB

2. After closing, opening and resaving the volume reduced to 332kB

With no further changes to the file, it has stayed at this value despite
opening and closing the file and Excel several times.

I do not appear to have the same problem - is it your operating system ?

I use Excel 2002 SP2 on XP system.

Ekelund said:
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
Click to expand...




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
Back
Top