Macro to remove macro that removes macros

[jerry12302]

I have the below VBA code to remove all macros from a workbook, but of course this macro remains.

Is there a way to remove all macros and also remove this macro after it is done, so there are no macros left at all?

Sub remove_macros()

Dim m As Object
Dim mCtr As Integer
Dim oCtr As Variant
Dim vbP As Object
Dim strNames As String
strNames = ThisWorkbook.Name
mCtr = 0

Set vbP = Workbooks("" & strNames).VBProject.VBComponents

For oCtr = 1 To vbP.Count
mCtr = mCtr + 1
If vbP(mCtr).Type = 1 Then
Set m = vbP
m.Remove vbcomponent:=m.Item(m(mCtr).Name)
mCtr = mCtr - 1
End If
Next

End Sub

 

[6StringJazzer]

Wouldn't it be easier to just save the file as a .xlsx file?

 

[jerry12302]

Because an alert pops up, and if you press continue saving as a macro-free workbook it will save it as an .xlsx file, but macros are still there.

I have a button that runs other code that does a bunch of stuff, then runs the macro to remove macros, no alerts, no popups. But I have to go in afterward and remove that macro too and save the file again.

 

[Logit]

This will delete all macros in the active workbook :

Option Explicit

Sub DeleteAllMacros() 'Excel vba to delete all macros in new workbook.
Dim otmp As Object

With ActiveWorkbook.VBProject
    For Each otmp In .VBComponents
        If otmp.Type = 100 Then
            otmp.CodeModule.DeleteLines 1, otmp.CodeModule.CountOfLines
            otmp.CodeModule.CodePane.Window.Close
        Else: .VBComponents.Remove otmp
        End If
    Next otmp
End With
End Sub

 

[6StringJazzer]

Because an alert pops up, and if you press continue saving as a macro-free workbook it will save it as an .xlsx file, but macros are still there.

I have a button that runs other code that does a bunch of stuff, then runs the macro to remove macros, no alerts, no popups. But I have to go in afterward and remove that macro too and save the file again.

If you save it as .xlsx then close it, there will be no macros when you reopen it. Tested.

Private Sub savecopynomacros()

   ThisWorkbook.Save ' save any unsaved changes
   ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & " no macros", FileFormat:=xlOpenXMLWorkbook
   ThisWorkbook.Close
   
End Sub

 

[jerry12302]

If you save it as .xlsx then close it, there will be no macros when you reopen it. Tested.

Private Sub savecopynomacros()

   ThisWorkbook.Save ' save any unsaved changes
   ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & " no macros", FileFormat:=xlOpenXMLWorkbook
   ThisWorkbook.Close
  
End Sub

 

[jerry12302]

I still get the pop up alert, I want to avoid that. With my macro above I don't get any alerts, and it removes all the macros except the macro to remove the macros.

 

[jerry12302]

This will delete all macros in the active workbook :

Option Explicit

Sub DeleteAllMacros() 'Excel vba to delete all macros in new workbook.
Dim otmp As Object

With ActiveWorkbook.VBProject
    For Each otmp In .VBComponents
        If otmp.Type = 100 Then
            otmp.CodeModule.DeleteLines 1, otmp.CodeModule.CountOfLines
            otmp.CodeModule.CodePane.Window.Close
        Else: .VBComponents.Remove otmp
        End If
    Next otmp
End With
End Sub

That did the job exactly, thank you!

 

[Logit]

Great ! Glad to help.

 

[6StringJazzer]

I still get the pop up alert, I want to avoid that.

It sounds like you prefer the other solution, but just for completeness, to suppress the alert

Private Sub savecopynomacros()

   ThisWorkbook.Save ' save any unsaved changes
   Application.DisplayAlerts = False
   ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & " no macros", FileFormat:=xlOpenXMLWorkbook
   Application.DisplayAlerts = True
   ThisWorkbook.Close
   
End Sub