Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: How to Delete a Collection of Code Objects from a VBAProject

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

View products that this article applies to.

This article was previously published under Q260993

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB260993, kbhowto, kbdtacode

↑ Back to the top

Article Info
Article ID : 260993
Revision : 6
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 201