Excel VBA multiple error handling in the same procedure
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.
David
Updated on July 01, 2022Comments
-
David almost 2 years
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!