Reducing the Size of an Add-In

While developing an add-in, you can create and delete many variables and procedures. For example, you could create many temporary variables during debugging and then later delete them, or you could create procedures that you eventually delete as your code evolves. Visual Basic might not delete the variable and procedure names from its symbol table; this could cause the add-in to use more memory than it would if the symbol table were clean.

If you suspect that a heavily edited workbook is using an unusually large amount of memory, you can reduce its size by cleaning its symbol table.

Note

The following technique does more than just clean the symbol table; it also removes all the information in the Macro Options dialog box, such as the shortcut key, function category, status bar text, and so on. The information is removed for every procedure in every module. If you've used the Macro Options dialog box to add any information to the procedures, you'll have to restore it after cleaning the symbol table.

To clean an add-in's symbol table

1. Open the original source workbook.

2. Save each module in the workbook to a separate text file.

3. Delete every module from the workbook.

4. Save the workbook.

5. Create new modules, giving them the same names as the ones you deleted.

6. On the Insert menu, click File to add the code back into the new modules. Be sure to copy the code to the correct modules.

Although cleaning the symbol table may be tedious, it can reduce the memory requirements of an extensively edited add-in. You can compare the values returned by the MemoryUsed property (which returns the amount of memory that's currently being used by Microsoft Excel) before and after cleaning to measure the saved memory.