Excel VBA在同一过程中的多个错误处理 [英] Excel VBA multiple error handling in the same procedure

问题描述

我以前曾在VBA中成功使用过错误处理，但是当尝试使用多个错误处理块时，我不知道该怎么做。

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.

非常感谢您的帮助！

推荐答案

在一个过程中具有两个或更多个错误处理子例程绝对是一种设计异味；这不是VBA错误处理的工作原理。

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

基本上，您有：

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

ErrHandler1:
    '(error handling code)

    On Error GoTo ErrHandler2
    '(code)

ErrHandler2:
    '(error handling code)

End Sub

在第一个块中发生错误时，VBA跳至 ErrHandler1 并且仍然认为它到达第二个程序段时是在错误处理子程序中。

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.

您需要恢复告诉VBA我已经处理了必须处理的所有内容。

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

因此，不要掉队进入 NoError1 子例程，您的 ErrorHandler1 子例程应以 Resume 跳转结束：

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

Resume NoError1

ErrorHandler2 也应以a结尾恢复跳转：

Resume NoError2

这样，VBA知道它已经脱离了错误处理模式，又回到了正常执行状态。

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.

这篇关于Excel VBA在同一过程中的多个错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！