Solved: Remove all modules.

[Marcster]

What is the VBA code to remove all modules in a workbook?.

Thanks,

Marcster.

[Marcster]

Just having a look around in the object browser
Added Microsoft Visual Basic for Applications Extensibilty 5.

Marcster.

[Bob Phillips]

[vba]
Dim oVBComp As Object
Dim oVBComps As Object

Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
Select Case oVBComp.Type
Case 1, 3, 2
oVBComps.Remove oVBComp
Case Else
With oVBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next oVBComp
[/vba]

[Marcster]

How to amend the following so it deletes all VBA code in workbook
except Module3?.

[VBA]

Sub RemoveAllVBACode()
'Remove all VBA code in all modules:

Dim oVBComp As Object
Dim oVBComps As Object

Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
Select Case oVBComp.Type
Case 1, 3, 2
oVBComps.Remove oVBComp
Case Else
With oVBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next oVBComp


End Sub[/VBA]

Thanks,

Marcster.

[Bob Phillips]

[vba]
Sub RemoveAllVBACode()
'Remove all VBA code in all modules:

Dim oVBComp As Object
Dim oVBComps As Object

Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
Select Case oVBComp.Type
Case 1, 3, 2
If oVBComp.Name <> "Module3" Then
oVBComps.Remove oVBComp
End If
Case Else
With oVBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next oVBComp

End Sub
[/vba]

[Zack Barresse]

I thought it was the component Item(index).Name?? Well, here is another way ...

[vba]Sub DeleteAllModules()
Dim oVBComp As Object
Dim oVBComps As Object
Dim i As Long
Set oVBComps = ActiveWorkbook.VBProject.VBComponents
i = 1
For i = oVBComps.Count To 1 Step -1
Set oVBComp = oVBComps.Item(i)
Select Case oVBComp.Type
Case 1, 3, 2
If oVBComps.Item(i).Name = "Module3" Then GoTo SkipComp
oVBComps.Remove oVBComp
Case Else
With oVBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
SkipComp:
Next i
End Sub[/vba]

[Bob Phillips]

I thought it was the component Item(index).Name?? Well, here is another way ...

Iterating through the components as originally shown addresses the same component as indexing the item. So it is looking at the same item, just using a different addressing method.

[Marcster]

Thanks guys,

Marcster.