Excel VBA multiple error handling in the same procedure

Question

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!

Do the ... stand for Private Sub DoSomething() and End Sub respectively, or there's more to it? I think your code could be easier to follow if you extracted a method out of each "block", each with its own error handling; basically you need to untangle this GoTo Mess. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 23, 2015 at 17:45
Yes the ... stands for start and end of sub, but there's some more simple calculations which should not affect the error handling. Basically both of the error handling blocks works if I only use one of them, but to use both maybe I have to "hide" the code by making two short subs instead. — David
– David, Commented Nov 23, 2015 at 17:53

Mathieu Guindon · Accepted Answer · 2015-11-23 17:55:55Z

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.

Dino · Accepted Answer · 2019-07-31 08:21:23Z

1

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

edited Jul 31, 2019 at 8:21

Dino

8,37213 gold badges50 silver badges93 bronze badges

answered Jul 31, 2019 at 6:54

Gabi

111 bronze badge

Comments

Barett · Accepted Answer · 2015-11-23 21:26:26Z

0

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

edited Nov 23, 2015 at 21:26

Barett

5,9546 gold badges54 silver badges57 bronze badges

answered Nov 23, 2015 at 19:04

Richard

11 bronze badge

1 Comment

Barett Over a year ago

Surely you mean Exit Sub ?

Collectives™ on Stack Overflow

Excel VBA multiple error handling in the same procedure

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related