File size grows 10-Fold after changing VB Code

[Rob]

I have an application built in Excel 2003 with user forms and numerous
macros. For years this worked fine and maintained a file size of
1.2MB. Then our company shifted to Excel 2007, and after I tweaked
some of the macros while in Excel 2007, and when I saved the file the
size exploded to 10 times the original size...11.2MB !!!

I think I narrowed it down to changing a Sort macro. I recorded this
Sort macro in Excel 2007 and saved it and that's when the file size
grew so much.

Again, when I tweaked the Sort macro on a machine running with Excel
2003 it works and the size remains at 1.2MB, it's just when I change
the macro in 2007 and save it there does it mushroom.


Can anyone shed some light on this?

Is there anything I can do to prevent the file getting so big.

TIA

 

[Don Guillett Excel MVP]

As ALWAYS, post your code................

 

[GS]

After serious thinking Rob wrote :

I have an application built in Excel 2003 with user forms and numerous
macros. For years this worked fine and maintained a file size of
1.2MB. Then our company shifted to Excel 2007, and after I tweaked
some of the macros while in Excel 2007, and when I saved the file the
size exploded to 10 times the original size...11.2MB !!!

I think I narrowed it down to changing a Sort macro. I recorded this
Sort macro in Excel 2007 and saved it and that's when the file size
grew so much.

Again, when I tweaked the Sort macro on a machine running with Excel
2003 it works and the size remains at 1.2MB, it's just when I change
the macro in 2007 and save it there does it mushroom.


Can anyone shed some light on this?

Is there anything I can do to prevent the file getting so big.

TIA

I have never experienced size changes after saving XLAs (as xla) in
Excel v12. I've never updated files to XLAM because I want one project
only to maintain but runs in all versions of Excel.

You could try Rob Bovey's CodeCleaner utility to see if it restores the
size closer to normal.

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[Martin Brown]

I have an application built in Excel 2003 with user forms and numerous
macros. For years this worked fine and maintained a file size of
1.2MB. Then our company shifted to Excel 2007, and after I tweaked
some of the macros while in Excel 2007, and when I saved the file the
size exploded to 10 times the original size...11.2MB !!!

What else is in the file? The only times I have seen these sorts of
insane filesize growth has been for corporate spreadsheets that have
been edited and passed between multiple dateline versions of XL and
contain embedded images that have been dragged and dropped into it.

The record was a file containing 6 1MB images that occupied over 100MB -
mostly consisting of orphanned image resources. Exporting the sheets to
HTML format webpage is often quite illuminating when filesize goes
haywire. Same problem can also occur in Word.

There is something about it in the MSKB but you have to ask it exactly
the right question and the suggested fix is pretty much export
everything and rebuild again from scratch on the new platform.

I think I narrowed it down to changing a Sort macro. I recorded this
Sort macro in Excel 2007 and saved it and that's when the file size
grew so much.

Again, when I tweaked the Sort macro on a machine running with Excel
2003 it works and the size remains at 1.2MB, it's just when I change
the macro in 2007 and save it there does it mushroom.

Something about the original file disagrees with XL2007.

Can anyone shed some light on this?

Is there anything I can do to prevent the file getting so big.

Avoid using XL2007 would my first suggestion.

Regards,
Martin Brown

 

[Jim Cone]

Another thing you can do is check the number of Styles in the workbook...
40 to 50 is normal for xl2007; several thousand is not.

My free "Formats & Styles" Excel add-in can list or delete unused styles.
(xl97 thru xl2010)

Download from...
http://www.mediafire.com/PrimitiveSoftware
-or-
http://excelusergroup.org/media/
'---
Jim Cone
Portland, Oregon USA




"Rob" <[email protected]>
wrote in message
news:71ca9964-8ff9-4c77-9977-4270ca5274ab@k14g2000pre.googlegroups.com...