Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Excel Macro Removal Code

Using VBA Code to Remove Macros, Modules from a File

Automated reports using Macros are fine until you send them out and the people reviewing them complain about the "Macro Warning" they get upon opening the file. This tutorial shows how to create an Excel Macro to remove all code behind an Excel file including itself. It will use a "main" Excel file to run a report and a "copy" of the same file with the finished report to run the self destruct code to get rid of the macros.

Create the Code Module in Excel

Open an existing Excel file with a Macro in it. Click on Alt + F11 to get to the code behind the file.

Excel Macro File

Right click on Modules and select Insert, Module and name the Module basSelfDestructSeq.

Type the following code into the module to use VBE to remove the modules, an extra page called Header, the code in the ThisWorkbook object and itself.

Sub SelfDestruct()
  Dim CodeMod As VBIDE.CodeModule
  Dim StartLine As Long
  Dim ProcLen As Long
  Dim vbCom As Object
  'sub to remove Macro Code from file for users

    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
    Excel.Application.DisplayAlerts = False
    Application.ScreenUpdating = False

     vbCom.Remove VBComponent:=vbCom.Item("basPopWkshts")

    Workbooks(ThisWorkbook.Name).Worksheets("Header").Delete
    Set CodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

    With CodeMod
      StartLine = .ProcStartLine("GenRept", vbext_pk_Proc)
      ProcLen = .ProcCountLines("GenRept", vbext_pk_Proc)
      .DeleteLines StartLine, ProcLen
      StartLine = .ProcStartLine("SelfDestructCall", vbext_pk_Proc)
      ProcLen = .ProcCountLines("SelfDestructCall", vbext_pk_Proc)
      .DeleteLines StartLine, ProcLen
    End With

    vbCom.Remove VBComponent:=vbCom.Item("basSelfDestructSeq")

    Excel.Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Add this code to the ThisWorkbook object.

Public Sub SelfDestructCall()
    SelfDestruct
End Sub

Add the VBIDE Reference to Excel

Select Tools, References and add the Microsoft Visual Basic for Applications Extensibility 5.3 to the project and click OK.

Excel References

Edit the Excel Trust Settings

Edit the Trust Settings in the Excel Options to Trust Access to the VBA project model. If this is not checked an exception will be thrown.

Excel Options

Click the Check Box under Developer Macro Settings

Excel Trust Center

The following Exception will get thrown when Visual Basic is not "trusted"

Excel Error

Save the file with a different name, such as Book1Copy.xls. Run the Macro.

Excel Run Macro

The code in the file will disappear from the file and your main file will be able to be reused each time you run the report.

Removing the macro code from an Excel file is easily accomplished using VBA, adding a reference and updating the Trust Settings in Excel. To further automate the process, see the article on using C# to run the macros from a console application here.