'Trapping Errors' advice.

[LEXCERM]

Hi All,

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

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.

 

[BrianB]

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".

 

[LEXCERM]

Thanks for the reply BrianB.

So in theory, I could do this:-

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.

 

[Richie(UK)]

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

 

[LEXCERM]

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.

 

[Richie(UK)]

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:

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

 

[LEXCERM]

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.