Error trap

[garydp]

i have a macro that uses

On Error GoTo ErrorHandler

when an error occurs i have a variable that is populated with what section the code fails at. I was wandering is it possible to pin point the exact line of code that fails?

[Aflatoon]

If you add line numbers to your code, you can use Erl to return the error line number.

[garydp]

sorry how do i do that?

[Aflatoon]

The simplest way is to use the free MZ Tools add-in. Otherwise you have to manually add numbers to the start of all the lines (like old Basic code).

[garydp]

i didnt think vba allowed line numbers at the start. in what format should it be?

[garydp]

its ok found it thanks

[Aflatoon]

Simple example for others' benefit:


Sub foo() Dim n As Long

On Error GoTo err_handle

5 n = 10
10 Do
15 n = n - 1
20 Debug.Print 1 / n
25 Loop


err_handle:
MsgBox "Error " & Err.Number & ":" & vbLf & Err.Description & vbLf & "Error occurred on line # " & Erl
End Sub