What is the VBA code to remove all modules in a workbook?.
Thanks,
Marcster.
What is the VBA code to remove all modules in a workbook?.
Thanks,
Marcster.
Just having a look around in the object browser
Added Microsoft Visual Basic for Applications Extensibilty 5.
Marcster.
[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]
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.
[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]
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]
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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.Originally Posted by firefytr
Thanks guys,
Marcster.