Delete VB issue

[LiAD]

Hi,

I have this code to try to delete whatever code is inside a file but it
doesn't seem to work. Would some-one be able to tell me why please or what
code I should use to delete whatever VB is in a file?

Thanks
LiAD


Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
..DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

 

[Joel]

1) The activeworkbook cannot be the workbook where the macro is running. The
code cant delete itself.
2) Check you security seeting on the worksheet. Enable the option Trust
Access to the VBA Project Object model.

in 2003 it is on the menu Tools - Macro - Security Setting

in 2007 Excel OPtions - Trust Center - Trust Center stting

 

[LiAD]

Thanks.

The full code I have is attached below where there is a save as before the
delete VB part. Is there still a problem?

Security is (and was) enabled.

Thanks for your help

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
..DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

 

[LiAD]

Got it sorted.

Thanks for taking a look

Thanks.

The full code I have is attached below where there is a save as before the
delete VB part. Is there still a problem?

Security is (and was) enabled.

Thanks for your help

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub