ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Delete all macros in a workbook/document

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.

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

 

 

Document last updated 2002-07-17 12:51:07      Printerfriendly version

User comments:
Ole P. from Norway wrote (2006-10-04 10:08:19 CET):
Re: How to pass this object as parameter
A few more examples than the example above shows:
Sub RemoveWorkbookMacros()
RemoveAllMacros ActiveWorkbook ' the active workbook
RemoveAllMacros Workbooks("WorkbookName.xls") ' using an object reference
RemoveAllMacros objWorkBook ' using an object variable
End Sub
SM wrote (2006-10-04 08:50:53 CET):
How to pass this object as parameter in the above function RemoveAllMacros
How to pass this object as parameter in the above function RemoveAllMacros

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address