VBA : Remove All My Macros in Workbook (Excel)

[Susanto3311]

hi all..
i found the code & try this code but not work..i need the code to remove all my macros code in my workbook
the code not working in my workbook, i'm using ms office 2016
how to make this code running well.
here this code

Code: Select all

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

anyone help me out, greatly appreciated.
susant

[HansV]

Why not simply save the workbook as a standard Excel workbook (.xlsx)? That will remove all VBA code in one go.

[ChrisGreaves]

i found the code & try this code but not work..i need the code to remove all my macros code in my workbook

Hello susant.
I would like to suggest that you get a simple piece of code to work, and then surround it with any necessary complexity.
This code worked for me (on a COPY of a workbook!):-

Code: Select all

Function ClearVBACode(wbk As Workbook)
' Given a valid workbook, delete all VBA code (Modules, Forms, Class modules)
    Dim vbP As Object
    Set vbP = wbk.vbProject
    Dim lng As Long
    For lng = vbP.VBComponents.Count To 1 Step -1
        Dim strName As String
        strName = vbP.VBComponents(lng).Name
        On Error Resume Next
        vbP.VBComponents.Remove vbP.VBComponents(strName)
    Next lng
'Sub TESTClearVBACode()
'    Call ClearVBACode(ActiveWorkbook)
'End Sub
End Function

Drag the commented TEST macro outside the function, de-comment it, then run it with a spare copy of a workbook. The code removed all my code modules (BAS), user forms (FRM) and class modules (CLS).

If I were developing this further I would test for protected workbooks in a function outside the code of "Function ClearVBACode", so that "Function ClearVBACode" only need concern itself with workbooks that are unprotected.
If you can get this simple "Function ClearVBACode" to work on a workbook, then try adapting it to work in the same manner of a Word document.

I hope that this helps.
P.S. Note that I feel no need to test for count of projects, count of components; I let the FOR-loop deal with that issue.
Cheers, Chris

[Susanto3311]

hi chris....thank you but i have try use your code i don't see anything...
my problem:
1. where the code to copy paste, there 3 folder, Forms, Modules, and Class Modules?
2. show message "ambiguos nama detected:clearvbacode
here complete code

Code: Select all

Sub clearvbacode()
Function clearvbacode(wbk As Workbook)
' Given a valid workbook, delete all VBA code (Modules, Forms, Class modules)
    Dim vbP As Object
    Set vbP = wbk.VBProject
    Dim lng As Long
    For lng = vbP.VBComponents.Count To 1 Step -1
        Dim strName As String
        strName = vbP.VBComponents(lng).Name
        On Error Resume Next
        vbP.VBComponents.Remove vbP.VBComponents(strName)
    Next lng
'Sub TESTClearVBACode()
'    Call ClearVBACode(ActiveWorkbook)
'End Sub
End Function

[ChrisGreaves]

hi chris....thank you but i have try use your code i don't see anything...

Hello susant.
In your original post you had written:-

Code: Select all

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument

Here is a question: When you ran my example code on an unprotected workbook of yours, did the sample VBA code delete all VBProject components from your workbook?

That is, did the sample VBA code satisfy the first part of your requirements?
Thanks, Chris

[HansV]

@Susanto3311:

Remove that first line from

Sub clearvbacode()
Function clearvbacode(wbk As Workbook)

You can't have a function within a sub.

And a general remark:the code should NOT be stored in the workbook from which you want to remove all VBA code. It should be stored in, and run from, another workbook.

[Susanto3311]

hi guys...
i think i have a mistake..i forgot if my workbook is protected..
perhaps, this condition make the code can't running well.

[HansV]

Do you mean that the VBA project is password-protected, or that the workbook is password-protected?

[Susanto3311]

hi hans, the workbook is protected

[HansV]

If you know the password, you can unprotect the workbook at the beginning of the code:

wbk.Unprotect Password:="secret"

and protect it again at the end:

wbk.Protect Password:="secret", Structure:=True

[ChrisGreaves]

If you know the password, you can unprotect the workbook at the beginning of the code:

Agreed!
But I suspect that susant will be better off getting the core code working on an unprotected workbook, and then having the unprotect code in a separate cover function.
Let's make sure that we know how to remove modules in an easy situation before we tackle the more difficult situations?
Cheers, Chris