PDA

View Full Version : Solved: Remove all modules.



Marcster
02-02-2006, 07:25 AM
What is the VBA code to remove all modules in a workbook?.

Thanks,

Marcster.

Marcster
02-02-2006, 07:28 AM
Just having a look around in the object browser :reading:
Added Microsoft Visual Basic for Applications Extensibilty 5.

Marcster.

Bob Phillips
02-02-2006, 07:33 AM
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

Marcster
02-07-2006, 06:07 AM
How to amend the following so it deletes all VBA code in workbook
except Module3?.



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

Thanks,

Marcster.

Bob Phillips
02-07-2006, 10:34 AM
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

Zack Barresse
02-07-2006, 06:56 PM
I thought it was the component Item(index).Name?? Well, here is another way ...

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

Bob Phillips
02-08-2006, 01:27 AM
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
02-08-2006, 03:32 AM
Thanks guys,

Marcster.