Errors - Erl() always line#0 how to troubleshoot? fine source of error?

[shell_l_d]

I never ever get a value for Erl() , the error line number, & doesn't take me to debug mode to pinpoint the error... makes it very hard to troubleshoot...

I'm still learning VBA so there would be better error handling that I could probably do... but for now most of my Subs have this:

'Error Handler
DataExtract_Err:
    Application.ScreenUpdating = True
    MsgBox "SOURCE: " & Err.Source _
        & vbCrLf & "ERROR# " & Err.Number & ", LINE# " & Erl() & ", HelpContext: " & Err.HelpContext _
        & vbCrLf & vbCrLf & Err.Description _
        , vbCritical, "DataExtract Error!"
    Resume DataExtract_Exit
End Sub

The message box shows:

Data Extract Error!
SOURCE: Microsoft Office Excel
ERROR# 1004, LINE# 0, HelpContext: 1001004

Select method of Range class failed
[OK]

Have lots of code at moment, 5 modules & would like to be able to see exactly what line of code is causing the error in order to quickly/easily troubleshoot & at least for other users of the spreadsheet to report any errors that may occur in a msgbox easily.

Would like to be able to see:
* macro/sub/module of error
* line# of error

Any ideas why please? Or a better way to do it?

-----------------------
WinVista + Off2010
Win7 + Off2007

 

[xenou]

Generally speaking the best you can do is report an error number and description. During debugging, simply do not turn on your error handling so you will be thrown into debug mode at the very line that crashes (most of the time, anyway).

You can craft your message to be more specific - "Error in Sub GetReportData: ... " - then at least your users can clue you in to the subroutine with the problem (if they even write down the error message at all!). It's very important to not use error handling during development. Let it crash on you. Code defensively - if there's a chance a statement could error, then test for the condition first before executing the statement. It also helps to separate code into discrete functions or procedures rather than trying to do it all in one routine. Then over time (years) you will begin to have well-tested pieces of code that you can drop into your projects. Push out code that might fail into separate functions - then test the return value to see if it succeeded. Edit: BTW I've never heard of Erl() - from what you are saying it seems pretty useless

 

[shell_l_d]

Ahhh i c, thnx heaps Xenou.

I've been doing more googling & found 2 links with some great info in them.

They say Erl() will only work if you use line numbers in your code.
http://www.keysound.com/html/line_numbers.htm

http://www.brainbell.com/tutors/Visual_Basic/Tip_2_Use_line_numbers_in_your_source_code.htm