Microsoft KB Archive/264907

= OFF2000: Template Size Increases When You Add or Remove Visual Basic Code =

Article ID: 264907

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft PowerPoint 2000 Standard Edition
 * Microsoft FrontPage 2000 Standard Edition
 * Microsoft Word 2000 Standard Edition

-



This article was previously published under Q264907





SYMPTOMS
When you edit a Microsoft Visual Basic for Applications macro using the Visual Basic Editor, the file size of the project being edited (document, template, workbook, or presentation) may increase. The more the code is edited, the larger the file size becomes.



WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

To work around this issue, use the following sample code. This macro exports all Visual Basic components from the specified project to files, and then saves and closes the project. The project is then opened again and the Visual Basic components are re-imported before the project is saved again.

NOTE: In some cases, this technique may not reduce your project's file size.

To use this sample macro in a Microsoft Office application, you need to place the following code into a standard module and add a reference to the Visual Basic for Applications Extensibility library for your project. (In the Visual Basic Editor, click References on the Tools menu.) Option Explicit

Sub Caller ' This example assumes you are working in ' Word, and the project you want to work with ' is the first file in the collection.

' For Word use: FixProject Documents(1)

' For Excel use: ' FixProject Workbooks(1)

' For PowerPoint use: ' FixProject Presentations(1)

End Sub

Sub FixProject(Doc As Document) ' Word

'Sub FixProject(Doc As WorkBook) ' Excel

' Sub FixProject(Doc As Presentation) ' PowerPoint

Dim iStdMod As Long, iClsMod As Long Dim iFrm As Long, iDesn As Long Dim iDoc As Long, n As VBComponent Dim i As Long, fname As String

Debug.Print &quot;before=&quot; & FileLen(Doc.FullName)

For Each n In Doc.VBProject.VBComponents

' The vbext_ct_StdModule type is  ' only one of several VBComponent ' clause for each component type: ' (for example: module, form, class, etc) Select Case n.Type

Case vbext_ct_StdModule Debug.Print &quot;exporting &quot; & n.Name n.export &quot;C:\&quot; & iStdMod & &quot;.bas&quot; Doc.VBProject.VBComponents.Remove n        iStdMod = iStdMod + 1

Case vbext_ct_ClassModule Debug.Print &quot;exporting &quot; & n.Name n.export &quot;C:\&quot; & iClsMod & &quot;.cls&quot; Doc.VBProject.VBComponents.Remove n        iClsMod = iClsMod + 1

Case vbext_ct_ActiveXDesigner Debug.Print &quot;exporting &quot; & n.Name n.export &quot;C:\&quot; & iDesn & &quot;.dsr&quot; Doc.VBProject.VBComponents.Remove n        iDesn = iDesn + 1

Case vbext_ct_MSForm Debug.Print &quot;exporting &quot; & n.Name n.export &quot;C:\&quot; & iFrm & &quot;.frm&quot; Doc.VBProject.VBComponents.Remove n        iFrm = iFrm + 1

Case vbext_ct_Document ' This type of VBComponent will ' always re-import as a Class module. ' The original object association is        ' removed when importing/exporting. Debug.Print &quot;exporting &quot; & n.Name n.export &quot;C:\&quot; & iDoc & &quot;.cld&quot; iDoc = iDoc + 1

End Select

Next

' Save document now that all ' VBComponents have been exported. Doc.Save fname = Doc.FullName

'Close the file Doc.Close

Set Doc = Documents.Open(fname)

If iStdMod Then

For i = 0 To iStdMod - 1 Doc.VBProject.VBComponents.Import &quot;C:\&quot; & i & &quot;.bas&quot; Kill &quot;C:\&quot; & i & &quot;.bas&quot; Next

End If

If iClsMod Then

For i = 0 To iClsMod - 1 Doc.VBProject.VBComponents.Import &quot;C:\&quot; & i & &quot;.cls&quot; Kill &quot;C:\&quot; & i & &quot;.cls&quot; Next

End If

If iDoc Then

For i = 0 To iDoc - 1 ' This VBComponent is imported ' as a Class module. You will need ' to copy its code into the ' appropriate ThisDocument, Workbook, etc.     Doc.VBProject.VBComponents.Import &quot;C:\&quot; & i & &quot;.cld&quot; Kill &quot;C:\&quot; & i & &quot;.cld&quot; Next

End If

If iDesn Then

For i = 0 To iDesn - 1 Doc.VBProject.VBComponents.Import &quot;C:\&quot; & i & &quot;.dsr&quot; Kill &quot;C:\&quot; & i & &quot;.dsr&quot; Next

End If

If iFrm Then

For i = 0 To iFrm - 1 Doc.VBProject.VBComponents.Import &quot;C:\&quot; & i & &quot;.frm&quot; Kill &quot;C:\&quot; & i & &quot;.frm&quot; Kill &quot;C:\&quot; & i & &quot;.frx&quot; Next

End If

' Save document now that all ' VBComponents have been exported. Doc.Save Debug.Print &quot;after=&quot; & FileLen(Doc.FullName)

End Sub



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
When you copy and paste macro text into the Visual Basic Editor, the file size may continually increase, even when you copy and paste the same text over itself and save the template without any other changes.

When the ThisDocument (Word), ThisWorkBook (Excel), or ThisPresentation (PowerPoint) objects are imported from disk, they do not replace the existing document object types in the Office programs. Instead, they appear as class modules.

Additional query words: vba OFF2000

Keywords: kbbug kbmacroexample kbnofix KB264907

-

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

© Microsoft Corporation. All rights reserved.