Microsoft KB Archive/260993

= XL2000: How to Delete a Collection of Code Objects from a VBAProject =

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(&quot;Book1.xls&quot;).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.

