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

Yes.

Sub

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

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.

Mike

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.