Excel VBA“出现错误"捕获一些但不是全部错误 [英] Excel VBA "On Error" trapping some but not all errors

问题描述

我在工作簿中有许多嵌入式图表.一些图表有一个称为矩形1"的矩形，我想修改这些矩形.下面的代码应该可以做到这一点.对于某些图表(带有和不带有矩形的图表)都可以正常运行，但随后落入设置矩形"行，并显示运行时错误-2147024809(80070057)找不到具有指定名称的项目".

您知道错误处理程序为什么不选择它的原因吗?

  Sub RepositionExtrapolationRectangles()暗淡作为ChartObject昏暗作为工作表形状暗淡轴昏暗轴对于ThisWorkbook.Sheets中的每个内容对于sht.ChartObjects中的每个cht与cht.Chart出错时跳转设置rect = .Shapes(矩形1")出错时转到0设置axs = .Axes(xlCategory)rect.Left = .PlotArea.InsideLeft +([CurrentDate]-axs.MinimumScale)/(axs.MaximumScale-axs.MinimumScale)* .PlotArea.InsideWidthrect.Width = .PlotArea.InsideLeft + .PlotArea.InsideWidth-rect.Leftrect.Top = .PlotArea.InsideToprect.Height = .PlotArea.InsideHeight结束于跳过:错误清除下一个下一个结束子 

解决方案

我已经进行了一些快速测试，但无法确认为什么可以正常工作，但确实可以:

使用 On Error GoTo -1 (错误发生时转到-1)而不是 Err.Clear (清除错误处理对象)来清除您的错误处理对象.

似乎仅使用 Err.Clear 不能正确重置它，因此它无法捕获第二次发生的错误.

起初，我认为这可能是由于 For Each 循环引起的，但是重写测试代码而没有循环是行不通的，也没有将其重写为也不使用和.

I have a number of embedded charts in a workbook. Some of the charts have a rectangle called "rectangle 1" and I want to modify those rectangles. The following code is supposed to do that. It works ok for some charts (both those with and without a rectangle) but then falls over on the "Set rect" line with "run time error -2147024809 (80070057) The item with the specified name wasn't found".

Any idea why the error handler isn't picking this up?

Sub RepositionExtrapolationRectangles()
Dim cht As ChartObject
Dim sht As Worksheet
Dim rect As Shape
Dim axs As Axis

For Each sht In ThisWorkbook.Sheets
  For Each cht In sht.ChartObjects

    With cht.Chart
      On Error GoTo skip
      Set rect = .Shapes("Rectangle 1")
      On Error GoTo 0

      Set axs = .Axes(xlCategory)
      rect.Left = .PlotArea.InsideLeft + ([CurrentDate] - axs.MinimumScale) / (axs.MaximumScale - axs.MinimumScale) * .PlotArea.InsideWidth
      rect.Width = .PlotArea.InsideLeft + .PlotArea.InsideWidth - rect.Left
      rect.Top = .PlotArea.InsideTop
      rect.Height = .PlotArea.InsideHeight
    End With
skip:


 Err.Clear

  Next
Next
End Sub

解决方案

I have done some quick testing, and I can't confirm why the following works, but it does:

Use On Error GoTo -1 instead of Err.Clear, to clear your Error Handling Object.

It seems that just using Err.Clear is not resetting it properly, so it cannot trap the error the second time it occurs.

At first, I thought that this might be because of the For Each loop, but rewriting the test code without a loop did not work, nor did rewriting it to not use with either.

这篇关于Excel VBA“出现错误"捕获一些但不是全部错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！