Workbook File Size Increases While Editing Procedure

In Microsoft Excel, when you edit a Microsoft Visual Basic for Applications procedure in a workbook and then save the workbook, the workbook file size increases even if you delete code from the procedure.

Similarly, if you save a Visual Basic module sheet to the Basic Code (Text) file format, and you delete the original module sheet and then import the text file into a new module in the same workbook, the workbook decreases in file size when you save it.

This behavior occurs because the variables that you use in a procedure are stored in the module that contains the procedure. When you enter a variable name in a Visual Basic module, whether you use the Dim statement to dimension the variable or not, the name is stored in a name table. The disk space used for the variable in the name table is not freed until the module that contains the procedure is deleted. Because of this behavior, a workbook increases in file size when you add new variables to a procedure in the workbook, and does not decrease in file size when you delete variables from a procedure in the workbook.

A workbook also increases in file size when a procedure contained in the workbook is compiled.

To work around this behavior, you can save your Visual Basic module to the Basic Code (Text) file format, delete the original module, and then insert the text file into a new module in your workbook by doing the following:

1. Select the module sheet that contains the procedure that you have been editing.
2. From the File Menu, choose Save As. In the File Name box, type Test. From the Save File As Type list, select Basic Code (Text) and choose OK.
3. Choose OK in the dialog box that appears with the message "Selected file type will save only the active sheet."
4. From the Edit menu, choose Delete Sheet. Choose Yes in the dialog box that appears telling you that the selected sheet will be permanently deleted.
5. From the Insert menu, choose Macro, and then choose Module to create a new module sheet.
6. From the Insert menu, choose File. From the File Name list, select the file that you saved in Step 2 above, TEST.TXT.