Results 1 to 7 of 7

Thread: 'Trapping Errors' advice.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Posts
    305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 'Trapping Errors' advice.

    Hi All,

    Here is an example of a procedure I have set-up:-

    Code:
    Sub Start()
    
    OnError GOTO End_
    
    Code Blah Blah Blah
    Code Blah Blah Blah
     
    OnError GOTO Continue
    Selection.SpecialCells(xlCellTypeBlanks).Select
        	Selection.FormulaR1C1 = "=R[-1]C"
        	Columns("A:A").Select
        	Selection.Copy
       	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           	 :=False, Transpose:=False
        	Application.CutCopyMode = False
    
    Continue:
    Code Blah Blah Blah
    Code Blah Blah Blah
    
    Exiit Sub
    
    End_:
    Msgbox Err.Number & Err.Description
    End Sub

    I know that on a particular occasion, the line Selection.SpecialCells(xlCellTypeBlanks).Select could error out so I need the code to resume at label Continue.

    However, at the beginning of the procedure I have a general ONERROR statement which will exit the sub Ė detailing the error in a message box.

    I canít figure out how to retain the original ONERROR trap if the code should falter at Selection.SpecialCells(xlCellTypeBlanks).Select.

    I have placed ONERROR GOTO 0 just after the Continue label but this seems to disable all error traps.

    Thanks in advance,
    Paul.

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Only one On Error statement can be in operation at one time in a single subroutine. So OnError GOTO End_ should come after the Continue:

    As in army regulations Excel will "obey the last order".
    Regards
    BrianB (using XL2003 & 2010)
    www.cycleofgrowth.com
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    Board Regular
    Join Date
    Jun 2004
    Posts
    305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply BrianB.

    So in theory, I could do this:-

    Code:
    Sub Start() 
    
    OnError GOTO End_ 
    
    Code Blah Blah Blah 
    Code Blah Blah Blah 
      
    OnError GOTO Continue 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
           Selection.FormulaR1C1 = "=R[-1]C" 
           Columns("A:A").Select 
           Selection.Copy 
           Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
               :=False, Transpose:=False 
           Application.CutCopyMode = False 
    
    Continue: 
    OnError GOTO End_
    Code Blah Blah Blah 
    Code Blah Blah Blah 
    
    Exiit Sub 
    
    End_: 
    Msgbox Err.Number & Err.Description 
    End Sub
    ...and add an ONERROR trap just after the Continue label?

    I need an error handler when the routine starts just in case there is a problem before the OnError GOTO Continue.

    Regards,
    Paul.

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Paul,

    One of the key points to remember with custom error-handling is that you must reset the error-handling (using some form of the Resume instruction) once an error has arisen.

    You may find some of the info in this post to be helpful.

    HTH
    Richie

  5. #5
    Board Regular
    Join Date
    Jun 2004
    Posts
    305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Richie and thanks for replying,

    I have read the linked thread that you posted several times, but am still having issues.

    If the line Selection.SpecialCells(xlCellTypeBlanks).Select errors out, the code does go to the label CONTINUE. However, any other subsequent errors do not go to error handler END_, but Excel just shows the 'normal' error message.

    Thanks again,
    Paul.

  6. #6
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Paul,

    Well, both my post above and the content of the provided link stress the importance of a key element - the Resume instruction. Where is this instruction in the code that you are using?

    Multiple error-handlers within one routine can be problematic, as you have found. It may be better to re-consider the structure of the routine.

    Anyway, in terms of what you have - what about something like:
    Code:
    Sub Start()
    
        On Error GoTo Continue
        Selection.SpecialCells(xlCellTypeBlanks).Select
        MsgBox "OK"
        
    Continue:
        MsgBox Err.Number & Err.Description
        Resume NextBit
        
    NextBit:
        On Error GoTo End_
        MsgBox "Continue"
        MsgBox 1 / 0
        Exit Sub
        
    End_:
        MsgBox Err.Number & Err.Description
    End Sub
    HTH
    Richie

  7. #7
    Board Regular
    Join Date
    Jun 2004
    Posts
    305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Richie,

    The penny has finally dropped!!

    I was just using Resume Next but that just executes the next line of code directly after the error has occured - which would have been of no use to me.

    And the use of your Resume NEXTBIT, followed by the OnError statement will reset the error handler.

    Thanks very much for your patience.

    rgds,
    Paul.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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