Clean Up Your Macros

If you have macros, nowyou need to address the modules that the macro code resides in. This is a fairly quick, painless, and straightforward process that entails exporting all modules (this functionality is not available on Mac OS X) and UserForms to your hard drive and then deleting the existing modules and UserForms, pressing Save, and importing the modules you exported.

To do this, go into the Visual Basic Editor and, from within the Project Explorer, right-click each module and select Remove Module1 (or whatever the name of the module happens to be). When you are asked whether you want to export your module before removing it, say Yes, taking note of the path.

Do this for each module in turn, as well as for any UserForms you might have. Don't forget the private modules of your workbook and worksheets if they house code as well. Once you have done all this, save the workbook. Then, select File → Import File and import each module and UserForm back into your workbook. Following this process will create a text file of each module and that, in turn, removes all extra baggage that the modules might be holding.

The Web contains some free utilities that will automate this task to some degree, but we have heard cases of these utilities making a mess of code or even increasing file sizes. If you do use one of them, always save a backup copy of your file first, as the developers will take no responsibility for any loss of data.

Honing Data Sources

If, after performing the previous steps, you still believe your file size is unrealistically large, another possible suspect is referencing unused cells in Pivot Tables and PivotCharts. This is true particularly of PivotTables, as people frequently reference all rows in order to avoid manually updating ranges as newdata is added. If this is your modus operandi, use dynamic named ranges for your data sources instead.