The following function was posted on this site to delete macros:

Const vbext_pk_Proc = 0


'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


When I try it out, I get the following error:

Method 'VBProject' of object '_Workbook' failed.

Anyone have a clue about what I'm doing wrong?
Many Thanks!

--
Ray

Hi Halray

See this site
http://www.cpearson.com/excel/vbe.htm

Don't forget to set a reference in the VBA to the
Microsoft Visual Basic For Applications Extensibility" library

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Halray" wrote in message ...
The following function was posted on this site to delete macros:

Const vbext_pk_Proc = 0


'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


When I try it out, I get the following error:

Method 'VBProject' of object '_Workbook' failed.

Anyone have a clue about what I'm doing wrong?
Many Thanks!

--
Ray

Ron,

Thanks for the info - I found what I needed in that link.

Many Thanks Again!!!
--
Ray


"Ron de Bruin" wrote:

Hi Halray

See this site
http://www.cpearson.com/excel/vbe.htm

Don't forget to set a reference in the VBA to the
Microsoft Visual Basic For Applications Extensibility" library

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Halray" wrote in message ...
The following function was posted on this site to delete macros:

Const vbext_pk_Proc = 0


'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub


When I try it out, I get the following error:

Method 'VBProject' of object '_Workbook' failed.

Anyone have a clue about what I'm doing wrong?
Many Thanks!

--
Ray