Microsoft KB Archive/123684

= Workbook File Size Increases While Editing Procedure =

Article ID: 123684

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 95a
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q123684





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.



WORKAROUND
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.

Additional query words: 5.00a 5.00c 7.00a clean dirty XL

Keywords: kbprb kbprogramming KB123684

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.