Controlling the disk space my VBA project uses (Excel) 1

[GVF]

Sometimes when I save the project, it is 6 megs. Other times it drops down to as small as 3.3 megs. (I only make incremental changes to the code - no massive additions and rarely any deletions of code.) Does anyone know why this is and is there a way to control the file size of the VBA project? It affects email distribution.

 

[ettienne]

The size of the file will fluctuate while you are editing it. Click Debug, Compile Project and then Save and close the project/file. This will bring the file size down to its minimum. 6MG sounds very large, normally VBA projects are a couple hundred K's. VBA does have a problem where the file sizes can balloon and you need to rebuild the project to get the size down again. Start with a new project and copy the relevant code and forms across.
You can also look at this link:

http://orlando.mvps.org/VBADecompilerMore.asp

 

[ettienne]

Correction: start with a new Excel spreadsheet and project and then copy the code and forms from the old spreadsheet.

 

[PHV]

Have a look here:

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GVF]

Thanks to both of you for your responses. I have downloaded the code cleaner PHV pointed to and gave it a quick try. 6 megs before cleaning, 3.4 after cleaning. When I saved the file as an addin, it bloated back up to 6 megs. Saving the xls project pushed it back up to 6 megs. I will give it a better test tomorrow.

Thanks again,
GVF