Controlling an addins file size

Excel workbooks (including add-ins) have always suffered from a serious problem: bloat. You might have noticed that the size of your files tends to increase over time, even if you don't add any new content. This is especially true if you delete a lot of code and then replace it with other code. Making lots of changes to worksheets also seems to add to file bloat.

If you want to make your add-in — or any workbook, for that matter— as small as possible, you need to re-create your workbook. Here's how.

1. Make a backup of your application and keep it in a safe place.

2. Activate the VBE and then export all the components for your project that contain VBA code (modules, code modules, UserForms, and possibly ThisWorkbook, worksheet, and chart modules). Make a note of the filenames and the location.

3. Create a new workbook.

4. Copy the contents of all the worksheets from your original application to worksheets in the new workbook. Be especially careful if you used named ranges in your workbook — they must be re-created.

5. Import the components that you exported in Step 2.

6. Compile the code.

7. If applicable, reattach any toolbars that were attached to your original workbook.

8. Save the new workbook.

9. Test the new workbook thoroughly to ensure that nothing was lost in the process.

There's an excellent chance that the newly created file will be much smaller than your original. The size reduction depends on many factors, but I've been able to reduce the size of my XLA files by as much as 55 percent with this process.

Another method that might reduce the size of your file is to save it in HyperText Markup Language (HTML) format. Then open the HTML file and save it back to the XLS format.I've had mixed success with this. Sometimes it reduces the size of the file, but in other cases, the new file size is actually larger than the original.

0 0

Post a comment