XL: Workbook File Size Increases While Editing Procedure

ID: Q123684

The information in this article applies to:

SYMPTOMS

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.

CAUSE

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.

For additional information, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q116392
   TITLE     : Cannot Modify a Visual Basic Procedure in Break Mode

WORKAROUNDS

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" 
   (without the quotation marks). 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.
Additional query words: 5.00 5.00a 5.00c 7.00 7.00a clean dirty 
Keywords          : kbprg OptPrfm OptMemMgt PgmVbl 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: September 10, 1998