MS Excel file size

When writing Excel applications (with the use of VBA) the Excel file size may increase unexpectedly. Appearantly a lot of garbage gets included in the VBA code over time. The entire application may also become unstable. I have found that it is possible to minimize the file size and also make the application more stable by doing the following:

• Unhide all hidden worksheets and save the workbook
• Save the workbook when opened with macros disabled. Do like this:
  1. Close the workbook if it is open.
  2. Reopen the workbook
  3. When the dialog box appears asking whether to disable or enable macros, select the "Disable Macros" button. If no such dialog appears, select "Tools", "Macro" and "Security" in the Excel menu, and set the "Security Level" to "Medium". Then reopen the workbook and select the "Disable Macros" button.
  4. Save the workbook. Stay calm, the macros still exists.
• Install and run "VBA Code Cleaner", downloadable from for example http://archive.baarns.com/excel/free/Exceluts.asp

Sometimes one of the tricks is enough, sometimes two and sometimes it takes all three of them. In a recent project I periodially performed the "unhide" and the "Code Cleaner" tricks, but the file was still bigger than expected and somewhat unstable. I then performed the "Disable Macros" trick and the file size shrunk from about 2.5 MB to about 800 kB and the application regained it's stability.