Article ID: 280157
Article Last Modified on 1/29/2007
APPLIES TO
- Microsoft Office 95 Standard Edition
- Microsoft Word 97 Standard Edition
- Microsoft Excel 97 Standard Edition
- Microsoft PowerPoint 97 Standard Edition
This article was previously published under Q280157
SYMPTOMS
When you edit a 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:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
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:
173707 OFF97: How to Run Sample Code from Knowledge Base Articles
The following code sample exports all Visual Basic components from the specified project to files, saves and closes the project. The project is then opened again and the Visual Basic components are re-imported before being saved again.
NOTE: In some cases, this technique may not cause a reduction in your project’s file size.
To use this example macro in a Microsoft Office application, you need to place the following code into a standard module and add a reference to the Microsoft Visual Basic for Applications Extensibility library to 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 "before=" & 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 "exporting " & n.Name
n.export "C:\" & iStdMod & ".bas"
Doc.VBProject.VBComponents.Remove n
iStdMod = iStdMod + 1
Case vbext_ct_ClassModule
Debug.Print "exporting " & n.Name
n.export "C:\" & iClsMod & ".cls"
Doc.VBProject.VBComponents.Remove n
iClsMod = iClsMod + 1
Case vbext_ct_ActiveXDesigner
Debug.Print "exporting " & n.Name
n.export "C:\" & iDesn & ".dsr"
Doc.VBProject.VBComponents.Remove n
iDesn = iDesn + 1
Case vbext_ct_MSForm
Debug.Print "exporting " & n.Name
n.export "C:\" & iFrm & ".frm"
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 "exporting " & n.Name
n.export "C:\" & iDoc & ".cld"
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 "C:\" & i & ".bas"
Kill "C:\" & i & ".bas"
Next
End If
If iClsMod Then
For i = 0 To iClsMod - 1
Doc.VBProject.VBComponents.Import "C:\" & i & ".cls"
Kill "C:\" & i & ".cls"
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 "C:\" & i & ".cld"
Kill "C:\" & i & ".cld"
Next
End If
If iDesn Then
For i = 0 To iDesn - 1
Doc.VBProject.VBComponents.Import "C:\" & i & ".dsr"
Kill "C:\" & i & ".dsr"
Next
End If
If iFrm Then
For i = 0 To iFrm - 1
Doc.VBProject.VBComponents.Import "C:\" & i & ".frm"
Kill "C:\" & i & ".frm"
Kill "C:\" & i & ".frx"
Next
End If
' Save document now that all
' VBComponents have been exported.
Doc.Save
Debug.Print "after=" & 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 will not replace the existing document object types in the Office applications. Instead they will appear as class modules.
Additional query words: vba
Keywords: kbbug kbmacroexample kbnofix KB280157
