Find the line of code that caused the error?

[Webtechie]

Hello,

I am trying to write a errandling routine. I have it working fine, but I
would like add one thing. It would be nice to know where in the procedure
the error occurred. Is there a way to get the procedure line number that
caused the error?

Thanks,

Tony

 

[Dave Peterson]

Saved from a post by Chip Pearson:

....A far
better solution would be first to download MZTools (www.mztools.com) or a
similar add-in that will automatically add line numbers to code. Then use On
Error Goto <label> to display the error. E.g.,

Sub AAA()
Dim WS As Worksheet
On Error GoTo ErrH:
10 Set WS = Worksheets("AAA")
20 Debug.Print "Error line skipped"
30 Exit Sub
ErrH:
40 If MsgBox("Error on line: " & Erl & vbCrLf & _
"Error: " & CStr(Err.Number) & ": " & Err.Description & vbCrLf & _
"Do you want to continue?", vbYesNo) = vbYes Then
50 Resume Next ' optional. do you really want to
60 End If
End Sub

This will tell you exactly where the error occurred and you can change the
code to prompt for a sheet name or workbook name or whatever is necessary to
remedy the problem. You have the option of continuing the code execution if
desired.