Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Trap Doesn't (Excel 2000)

    The error trap in one of my macros was not working but I could see nothing wrong with it, so I created a small macro to test the basic trap and it did not work - but it works fine on another computer. Both computers are using the same version (SP-3) of Excel.
    Here's the test macro:
    Sub xxx()

    On Error GoTo BugCheck
    Windows("xfhxfh.xls").Activate
    GoTo AllsWell

    BugCheck:

    MsgBox ("There's no such file.")

    AllsWell:
    On Error GoTo 0

    End Sub

    Any thoughts?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Error Trap Doesn't (Excel 2000)

    I see nothing wrong with it. What is not working about it exactly?

    Steve

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trap Doesn't (Excel 2000)

    Steve,
    I get the error message "Out of range" In other words, it can't find the file - which of course it's not supposed to find.
    Yes, the code looks fine and works fine, but not on my computer!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Error Trap Doesn't (Excel 2000)

    Does it act this way in all workbooks or only in this one?

    Steve

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

    Re: Error Trap Doesn't (Excel 2000)

    In the VBE, check Tools, Options, General tab. Make sure you have not set "Break on All Errors", but "Break on unhandled Errors".

    BTW: you have not created a proper error handler. A proper error handler needs to come as the last statements in a sub or function and *has* to have a Resume Statement:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> Foo()
    <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>GoTo</font color=blue> LocErr
    MsgBox 1 / 0
    TidyUp:
    <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    LocErr:
    MsgBox "Error!!!" & vbNewLine & Err.Description
    <font color=blue>Resume</font color=blue> TidyUp
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Trap Doesn't (Excel 2000)

    Jan,
    I'm not sitting at the affected computer at the moment, but I'm sure you're absolutely right about the setting.
    Thank you also for pointing out the sloppy error handling. Your advice is much appreciated.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Trap Doesn't (Excel 2000)

    Hi Jan Karel,

    Without pirating the thread, could you tell me if vbNewLine is the same as vbCrLf? The help files says: Platform-specific new line character; whichever is appropriate for current platform. What does this mean exactly?
    Tx
    Regards,
    Rudi

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

    Re: Error Trap Doesn't (Excel 2000)

    To answer for Jan Karel who is not online at the moment: vbCrLf = Chr(13) & Chr(10) is used in most MS-DOS/Windows applications to start a new line. On some other systems, such as Apple Macintosh, vbCr = Chr(13) is used to start a new line. The symbolic constant vbNewLine will be resolved to the value appropriate for the operating system: it equals vbCrLf on Windows, and vbCr on Macintosh. So if you need code to run on both platforms, use vbNewLine.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Trap Doesn't (Excel 2000)

    Wow...interesting. In retrospect, this may be better to use then for two reasons:
    1. Its more flexible to run over different platforms
    2. Its easier to remember and type than vbCrLf which is very abbreviated...esp. understandable for novices in code!

    Tx for the advice
    Regards,
    Rudi

Posting Permissions

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