How to remove VBA Project From Excel

Excel

Remove All VBA Code Programmatically

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

A method to use VBA code to remove all VBA code in a workbook. 

Discussion:

You have a lot of modules, userforms, and other code in a workbook and you want to delete it all at once. You could manually delete each one, one at a time, or you can use VBA to do all the work for you. 

Code:

instructions for use

			
Option Explicit 
 
Sub DeleteAllCode() 
     
     
     
     
    Dim x               As Integer 
    Dim Proceed         As VbMsgBoxResult 
    Dim Prompt          As String 
    Dim Title           As String 
     
    Prompt = "Are you certain that you want to delete all the VBA Code from " & _ 
    ActiveWorkbook.Name & "?" 
    Title = "Verify Procedure" 
     
    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title) 
    If Proceed = vbNo Then 
        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted" 
        Exit Sub 
    End If 
     
    On Error Resume Next 
    With ActiveWorkbook.VBProject 
        For x = .VBComponents.Count To 1 Step -1 
            .VBComponents.Remove .VBComponents(x) 
        Next x 
        For x = .VBComponents.Count To 1 Step -1 
            .VBComponents(x).CodeModule.DeleteLines _ 
            1, .VBComponents(x).CodeModule.CountOfLines 
        Next x 
    End With 
    On Error GoTo 0 
     
End Sub 


			

How to use:

1. Open Excel.
2. Press Alt + F11 to open VBE.
3. Paste the code there.
4. Close VBE (Alt + Q or press the X in the top right hand corner).
5. Open the workbook that you want to remove all the code from.
6. The VBA Project for that workbook must be unlocked (if it is protected).
7. Trust Access To Visual Basics Project must be enabled.
8. From Excel: Tools | Macro | Security | Trusted Sources.
9. Tools | Macro | Macros (Select "DeleteAllCode" and press Run).

Test the code:

1. Refer to the "How To Use" Section.
2. Download the attachment for a working example.

Sample File:

DeleteAllCode.zip 7.81KB 

Approved by mdmackillop

This entry has been viewed 368 times.