Results 1 to 15 of 15
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Trapping (XL XP)

    We are putting together an error trapping routine that reports errors that the code hasn't already handled. I wanted to report on the module, procedure and line number where the error occured. I know that I can use ERL to give me the line number (if I have line numbers in the first place) but how do I get informtion on the module and procedure where the error occured? Do I need to look at the stack?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Error Trapping (XL XP)

    Hopefully one of the gurus will weigh in definitively, but I'm 98% sure the answer is that you have to feed the routine the module and procedure names explicitly as arguments in each procedure that calls the routine -- which, unfortunately, prevents you from having a totally generic line of routine-calling code that you can just insert in each procedure's error-handler without having to customize it by typing in the module and procedure names. In other words, you need something like:

    Call ErrorReporter("NameOfThisModule", "NameOfThisProcedure")

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    what is the erl function?
    i do not find it in the object browser.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error Trapping (XL XP)

    ERL returns the line number of the last line to complete successfully. It is, I believe, an undocumented throwback to the days when everyone used line numbers.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    i do recall erl, but there has to be some documentation of how to use it, e.g. to what object does it apply

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    I saved an IDL file for each of the Excel 11 object library and the VBA for Extensibility 5.3 library.

    I then searched for erl using a whole word natch,
    No match was found.

    If erl is not in those libraries, where is it?

    Anybody got a code example that works?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    <pre>Sub erldemo()
    10 On Error GoTo LocErr
    20 MsgBox 1 / 0
    30 Exit Sub
    LocErr:
    40 MsgBox Err.Number & ", " & Err.Description & ", " & Erl
    50 Resume Next
    End Sub
    </pre>


    The line numbers are mandatory, otherwise Erl returns zero.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    Thanx.

    Found ERL.

    It is part of the VBA6 library.
    Tho there is no Help via Excel or in VB.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    Hi there, just wondering - did any of the "gurus" confirm whether there is any way of returning the module and procedure names without having to explicitly declare them? Only I'd like to do something similar ...
    Beryl M


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Trapping (XL XP)

    As far as I know, st3333ve is correct - you must pass the module and procedure names explicitly in each call to the error handling routine.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Oh, well. About time MS started making life a bit easier for us poor programmers, don't you think?!

    Or maybe it would be possible to write an add-in to do it? (Don't look at me, by the way!)
    Beryl M


  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Trapping (XL XP)

    For that, you'll need to move to .Net and managed code, Beryl. In the meanwhile, declare a private constant at the top of each module and set it to the name of the module as a string. In each routine, add a local constant with the name of the routine. Then you can easily pass those two values into your error handler.
    Charlotte

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Error Trapping (XL XP)

    I just did a quick Google search on "VB ERL add-in". There is a FREE add-in (MZ-Tools 3.0) for VB and VBA that handles error trapping, and adding line numbers:
    http://www.mztools.com/v3/mztools3.htm

    They also have MZ-Tools 4.0 for VB.NET, but it costs $40.
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    That is an excellent utility every VB(A) developer can use. I have had it on my system for years and I cannot do without.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error Trapping (XL XP)

    Love to - if downloading wasn't completely and 100% blocked and finding a way round that a sackable offence.

    If I really wanted it I'd have to put forward a business case, prove it would be worth having and it wouldn't have any nasties attached, sign the document in blood and swear on my immortal soul it would cause a problem to anyone anywhere and even then they might just say no ...

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •