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.
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.
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.
Click the Check Box under Developer Macro Settings
The following Exception will get thrown when Visual Basic is not "trusted"
Save the file with a different name, such as Book1Copy.xls. Run the 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.