Removing macros?

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,641
I'm wondering if it is possible to get rid of all the macros in a workbook with some kind of a code?

I know it's quite easy to copy things to a new workbook and achieve the same results that way but I'd rather have a piece of code that removes all the macros from the orginal file. Any ideas?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
You can try below...

Code:
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 ActiveWorkbookDocument ' 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

</PRE>
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,641
Thanks!

I'll try that one. Does it remove the modules & User Forms as well?

What I'm really after is getting rid of the whole "Enable Macros" dialog when the file is opened: Usually the macros have already done their job on the first run and are not needed afterwards.

Misca
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,774
Members
412,617
Latest member
mlharris
Top