CODEWITHSUNDEEP
excelvbaerror-handling
David
David

Reputation: 177

Excel VBA multiple error handling in the same procedure

I have previously used error handling in VBA successfully, but when trying to use several error handling blocks I can't figure out how to do it.

The code I've written looks like this:

...

  On Error GoTo ErrorHandler1
  shpArrow1.Left = shpLine.Left + shpLine.Width * Min(Sqr(Calculations.Range("cVolProduct").value / Calculations.Range("cVolRefIndices").value), 2) / 2 - shpArrow1.Width / 2
  shpTag1.Left = shpLine.Left + shpLine.Width * Min(Sqr(Calculations.Range("cVolProduct").value / Calculations.Range("cVolRefIndices").value), 2) / 2 - shpTag1.Width / 2
  shpArrow2.Left = shpLine.Left + shpLine.Width * Min(Sqr(Calculations.Range("cVolUnderlyings").value / Calculations.Range("cVolRefIndices").value), 2) / 2 - shpArrow2.Width / 2
  shpTag2.Left = shpLine.Left + shpLine.Width * Min(Sqr(Calculations.Range("cVolUnderlyings").value / Calculations.Range("cVolRefIndices").value), 2) / 2 - shpTag2.Width / 2
  shpIndexLine.Left = shpLine.Left + shpLine.Width / 2 - shpIndexLine.Width / 2
  GoTo NoError1
ErrorHandler1:
  shpArrow1.Left = shpLine.Left - shpArrow1.Width / 2
  shpTag1.Left = shpLine.Left - shpTag1.Width / 2
  shpArrow2.Left = shpLine.Left - shpArrow2.Width / 2
  shpTag2.Left = shpLine.Left - shpTag2.Width / 2
  shpIndexLine.Left = shpLine.Left + shpLine.Width / 2 - shpIndexLine.Width / 2
  errorRelativeRisk = 1
NoError1:
  On Error GoTo 0

  On Error GoTo ErrorHandler2
  Output.ChartObjects("ChartHistoryUnderlyings").Activate
  ActiveChart.Axes(xlValue).CrossesAt = ActiveChart.Axes(xlValue).MinimumScale
  ActiveChart.Axes(xlCategory).CrossesAt = ActiveChart.Axes(xlCategory).MinimumScale
  GoTo NoError2
ErrorHandler2:
  errorHistUnderl = 1
NoError2:
  On Error GoTo 0

...

The second error handling block does not work. I'm guessing that I don't quit the first error handling block correctly. Have tried to find an answer that works for me but without success.

Greatful for any help!

Upvotes: 3

Views: 24048

Answers (3)

Gabi
Gabi

Reputation: 11

The solution would be to apply On Error GoTo -1. It resets the error handling in VBA so that enables you to use multiple On Error GoTo statements.

>   Sub Asd()
    Range(Cells(7, 1), Cells(LastRow, 2)).Select
    On Error GoTo hello
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
hello: 
Cells(1, 1).Value = "Error 1"
On Error GoTo -1
 Range(Cells(7, 4), Cells(LastRow, LastColumn)).Select
On Error GoTo Hell
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "0"
Hell: 
Cells(1, 1).Value = "Error 2"

End Sub

Upvotes: 1

Richard
Richard

Reputation: 1

i think you should put in End Sub after each error handling code

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

Having two or more error-handling subroutines in a procedure is definitely a design smell; that's not how VBA error-handling works.

Basically you have this:

Sub Foo()
    On Error GoTo ErrHandler1
    '(code)

ErrHandler1:
    '(error handling code)

    On Error GoTo ErrHandler2
    '(code)

ErrHandler2:
    '(error handling code)

End Sub

When an error occurs in the first block, VBA jumps to ErrHandler1 and still thinks it's in an error-handling subroutine when it gets to the 2nd block.

You need to Resume somewhere, to tell VBA "I've handled everything I had to handle".

So instead of "falling-through" into the NoError1 subroutine, your ErrorHandler1 subroutine should end with a Resume jump:

Resume NoError1

And the ErrorHandler2 should also end with a Resume jump:

Resume NoError2

That way VBA knows it's out of "error handling mode" and back into "normal execution".

But I'd strongly recommend considering separate methods/procedures instead of labelled subroutines.

Upvotes: 7

Related Questions