MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting a Macro

Posted by TomC on June 14, 2000 12:19 PM

I have a VB application that opens an Excel file, loads the raw data sheet with data, and runs a macro that builds a pivot table. I then create a new file with the pivot table data. Unfortunately, the macro is also saved in the new file. I need to delete that macro - can this be done from VB?

Posted by Mike Faulkner on June 16, 0100 8:30 AM



Dim WBCodeMod As Object
Dim StartLine As Integer, LineCount As Long, CodeLine As Long

' Create an object reference for the Code Module
Set WBCodeMod = [Workbook].VBProject.VBComponents("ThisWorkbook").CodeModule

End Sub

DeleteProcedure WBCodeMod, "procedure name"

Private Sub DeleteProcedure(CodeMod As Object, ProcDec As String)
' Deletes a specified procedure in a module
Dim StartLine As Long, NumLines As Long

' Determine the first line of the procedure
StartLine = CodeMod.ProcStartLine(ProcDec, 0)

' Determine the number of lines in the procedure
NumLines = CodeMod.ProcCountLines(ProcDec, 0)

' Delete the procedure's lines
CodeMod.DeleteLines StartLine, NumLines
End Sub

You'll have to modify this to your particular situation, but this is the general idea.


Posted by Ryan on June 17, 0100 11:01 AM

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

You cannot delete the ThisWorkbook code module, or a sheet code module, or a chart code module.