vba clear all variables? File Bloat

[iainmartin100]

Hi I have a heavy spreadsheet which runs many macros and works perfectly, the issue I have is that when I run the macro the file size increases around 10mb.

I have looked at all the tabs within the document to ensure that no formating is being done outside of the standard ranges and the only other thing I can think of is that my VBA variables are holding lots of data?

If there a simple code which can set all variables to nothing in one go or just clear everything held within the memory which might be causing this size issue?

Any other suggestions on where this bloat may lie or how to solve this would also be handy.
Many thanks

 

[Andrew Poulsom]

VBA variables aren't saved with the workbook as far as I know, so something else must be causing the bloat.

 

[iainmartin100]

Hi thanks for the reply,
I've searched everywhere I can think of now for where the bloat is coming from, do you know of anything availible which may pin-point where the MB's are coming from?
The spreadsheet has about 30 tabs, lots of VBA and images not a great combo I know.

Thanks

 

[Norie]

Iain

Could it be the formatting within the 'standard' ranges causing the problem?

Or perhaps there is some formatting in a dark far flung corner, outside the 'standard' ranges.

It could be something that happened some time a ago and was never cleaned up.

 

[Andrew Poulsom]

Are your macros inserting images?

 

[iainmartin100]

One of the Macro's shows and hides a triangle but thats about it for images other than the standard one's in the document.

The method im using to check the formating is within my standard range for each tab is Ctrl+End and that seems to always take me to the desired place. I have also been through and deleted any unused name ranges etc.

Any other thoughts? it's baffling me

Thanks

 

[Norie]

Iain

You might want to try some other method to check the formatting.

Or even try copying only the used range to a new worksheet.

Of course that might not be such a good idea if it would effect the code or formulas.

 

[shg]

You might also check to see how many hidden shapes/images are in the sheets.