Error Handling

[MrRhodes2004]

I have been working with the following Sub to delete a module from the ThisWorkbook. When the code is there, it works fine. But if it is not there, I get an error 35 which is fine. I thought by placing error handling code and having the sub exit would work. But it still crashes the sub and throws the error. What am I doing wrong?
I want the seb deleted if it is there and do nothing if it is not.


[vba]Sub DeleteProcedure()
' Delete module
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
On Error GoTo ErrorHandler
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
StartLine = .ProcStartLine("Workbook_Open", vbext_pk_Proc)
HowManyLines = .ProcCountLines("Workbook_Open", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
ErrorHandler:

End Sub[/vba]

[johnske]

Just test for its existence first...[VBA]
Sub DeleteProcedure()

Dim StartLine As Long
Dim HowManyLines As Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
If .ProcOfLine(StartLine, vbext_pk_Proc) = "Workbook_Open" Then
HowManyLines = .ProcCountLines("Workbook_Open", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End If
Loop
End With
End Sub
[/VBA]

[MrRhodes2004]

John,
Your code works. Thank you. But I am still at a lack of understanding as to why the error handlers did not work. I have read the information on the CPearson website but do not understand why the error is still taking place.
When you ran my original code, did you get the error? If so, without checking for existance, how would handle the error? How would you handle an unexpected error?

Thanks for the help,

Michael

[Bob Phillips]

For me, StartLine throws an error and branches out.

[johnske]

John,
Your code works. Thank you. But I am still at a lack of understanding as to why the error handlers did not work. I have read the information on the CPearson website but do not understand why the error is still taking place.
When you ran my original code, did you get the error? If so, without checking for existance, how would handle the error? How would you handle an unexpected error?

Thanks for the help,

Michael

Michael,

No, I don't get an error with your original code.

I would probably handle an unexpected error in the same way you did, however, this is not really an unexpected error, it's an easily anticipated one that can (and should) be catered for...

Another way to cater for it - seeing the code is intended for a 'once-only' use - would be to have the procedure delete itself after deleting the Workbook_Open procedure.

Regards,
John