Delete all macros in a Workbook/Document
2002-07-17 VBE 1 72
When you want to delete all macros from a workbook or document you can use the macro below. The procedure can be used in both Excel and Word without any editing. It also assumes that you in newer versions of Office has allowed code to access your VBProjects.
Sub RemoveAllMacros(objDocument As Object) ' deletes all VBProject components from objDocument ' removes the code from built-in components that can't be deleted ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel ' or like this: RemoveAllMacros ActiveDocument ' in Word ' requires a reference to the ' Microsoft Visual Basic for Applications Extensibility library Dim i As Long, l As Long If objDocument Is Nothing Then Exit Sub i = 0 On Error Resume Next i = objDocument.VBProject.VBComponents.Count On Error GoTo 0 If i < 1 Then ' no VBComponents or protected VBProject MsgBox "The VBProject in " & objDocument.Name & _ " is protected or has no components!", _ vbInformation, "Remove All Macros" Exit Sub End If With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 On Error Resume Next .VBComponents.Remove .VBComponents(i) ' delete the component On Error GoTo 0 Next i End With With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 l = 1 On Error Resume Next l = .VBComponents(i).CodeModule.CountOfLines .VBComponents(i).CodeModule.DeleteLines 1, l ' clear lines On Error GoTo 0 Next i End With End Sub
Ron McIntyre | 2011-03-16 19:20:57 (GMT)
Thanks for this one as well. Was exactly what I was looking for.