Article ID: 260993
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Excel 2000 Standard Edition
This article was previously published under Q260993
SUMMARY
This article contains a sample Microsoft Visual Basic for Applications macro to loop through all the objects in a VBProject, and remove the objects contained in a specific collection. For example, you can remove all standard module sheets in either the active workbook or a specific workbook.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.
Sub DelModules()
Dim m As Object
Dim mCtr As Integer
Dim oCtr As Variant
Dim vbP As Object
Set vbP = Workbooks("Book1.xls").VBProject.VBComponents
' To specify the project of the active workbook, use
' the following line instead:
'
' Set vbP = Application.VBE.ActiveVBProject.VBComponents
mCtr = 0
For oCtr = 1 To vbP.Count
mCtr = mCtr + 1
If vbP(mCtr).Type = 1 Then
' Use type 2 for class module or 3 for a form.
' You can also use the extensibility constants instead of their
' values. See the reference table in the article for the constants.
Set m = vbP
m.Remove VBComponent:=m.Item(m(mCtr).Name)
mCtr = mCtr - 1
End If
Next
End Sub
The following table lists the different types of VBComponents and their associated constants and values. If you refer to their values, then you must create a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 object library (Vbe6ext.olb).
Constant Value Description
-------- ----- -----------
vbext_ct_StdModule 1 Standard module
vbext_ct_ClassModule 2 Class module
vbext_ct_MSForm 3 Microsoft Form
The Remove method of the VBComponents collection requires an index that specifies the position of the object within the collection. This position can be either a value or string, but must exactly match the object as it is referred to within the collection.
REFERENCES
For more information about VBE objects and properties, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type VBE Property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Additional query words: XL2000
Keywords: kbdtacode kbhowto KB260993
