On 24/01/2011 23:23, 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 !!!

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