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

shell_l_d

Board Regular
Joined
Jun 25, 2010
Messages
73
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:

Code:
'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
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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 ;)
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top