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() 'Trust Access To Visual Basics Project must be enabled. 'From Excel: Tools | Macro | Security | Trusted Sources 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 317 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express