Removing macros?

[Misca]

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?

 

[shemayisroel]

You can try below...

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]

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