VBA在代码执行期间更改错误处理方法 [英] VBA Change error handling method during code execution

问题描述

我有一个宏，部分代码与以下内容有关:1)检测一列是否包含空单元格-如果是的话用一些值填充它们2)检测列中是否包含包含错误(例如N/A)的单元格，如果有，则将其填充一些值

I have a macro, and part of the code is concerned with: 1) detecting if a column contains empty cells - if so filling them with some value 2) detecting if a column contains cells containing errors (such as N/A) and if so filling them with some value

现在，如果该列中没有错误/空单元格，则找到它们的行将显示运行时错误1004未找到任何单元格".

Now if there are no error/empty cells in the column, the line that finds them gives a "Run-time error 1004 no cells were found".

我使用错误处理功能通过GoTo跳过了这一步.

I use error handling to skip this with GoTo.

下面是代码-第一个错误处理GoTo可以正常工作，而第二个错误给出了预期的错误，尽管设置了Error Processing GoTo，但这似乎不起作用.带有注释的代码:

Below is the code - the first error handling GoTo works perfectly, while the second gives the expected error, although there is Error handling GoTo set, that does not seem to work. Code with comments:

On Error GoTo EErrorOne


'depending on file I get, below line will generate error and code successfully skips to ErrorOne label

Workbooks(nazwawb).Sheets(szitnr).Columns(ktorepole).SpecialCells (xlCellTypeBlanks)

' code to be skipped

    Workbooks(nazwawb).Sheets(szitnr).Columns(ktorepole).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = "Some Value"

' end of code to be skipped

EErrorOne:

' successfully skipped error line by now. Below line should set a new Error handling procedure.

On Error GoTo EErrorTwo



Workbooks(nazwawb).Sheets(szitnr).Columns(ktorepole).Select

' Below line generates an error but does not skip to EErrorTwo label as detailed in the current Error handling procedure

Selection.SpecialCells(xlCellTypeFormulas, 16).Select   

' code to be skipped


Selection.SpecialCells(xlCellTypeFormulas, 16).Select
    Selection.Value = "Some Value"

' end of code to be skipped


EErrorTwo:



' Below line should reset error handling procedure to normal for subsequent handling of other errors:
On Error GoTo 0

似乎忽略了错误处理过程(特定于GoTo的标签)，而是显示一条错误消息，好像错误处理已重置为GoTo0.如何跳过第二个错误?

It seems that error handling procedure (GoTo specific label) is ignored, and instead, an error message is shown as if the error handling was reset to GoTo 0. How do I skip the second error?

推荐答案

在内部错误处理例程中，除非您清除了先前设置的错误例程(

Inside error handling routines, it seems as though defining new error handling routines won't work, unless you clear the previously set error routine (https://excelmacromastery.com/vba-error-handling/):

'...
EErrorOne:
On Error GoTo -1 'Clears error trap flag
On Error GoTo EErrorTwo 'Reset error handling
'...

---

接受后
如评论中所述， On Error GoTo -1 清除错误陷阱标志，而 Err.Clear 仅清除错误.
下面的代码通过创建两个错误并尝试捕获它们来说明这一点.

---

Edit after accepted:
As was discussed in the comments, On Error GoTo -1 clears the error trap flag, while Err.Clear only clears the error.
The code below illustrates this by creating two errors and trying to trap them.

On Error Goto -1 允许第二个错误被 On Error GoTo NextLabel 行捕获，并在发生错误时跳转到标签.
Err.Clear 保留第一个错误，因此，当第二个错误发生时，将显示错误消息，而不是代码跳到标签上.

On Error Goto -1 allows the second error to be trapped by the On Error GoTo NextLabel line and the code jumps to the label when the error occurs.
Err.Clear keeps the first error live, so when the second error occurs the error message is displayed rather than the code jumping to the label.

Sub ClearErr()

    Dim x As Long
    Dim y As Worksheet

    'Jump to label when "Division by 0" occurs.
    On Error GoTo ErrorLabel

    x = 1 / 0
    Debug.Print x

ErrorLabel:

    'On Error GoTo -1 'Next error is trapped.
    Err.Clear   'Untrapped error on y=Worksheets(1)

    'Jump to label when "Object Variable not set" occurs.
    On Error GoTo NextLabel

    y = Worksheets(1)
    Debug.Print y.Name

NextLabel:

End Sub

这篇关于VBA在代码执行期间更改错误处理方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！