Excel VBA错误处理不适用于第二个错误 [英] Excel VBA error handling not working for second error

问题描述

在下面的代码Errorhandler中，当我输入未打开的工作簿或任何随机字符串时，可以正确处理第一个错误.但是，当我单击重试并再次输入随机名称时，在Workbooks(workbookname)处出现"Subscript out of Range"错误.激活.

In the below code Errorhandler correctly takes care of first error when I enter a workbook that is not open or any random string. But when I click on retry and again enter a random name I get "Subscript out of Range" error @ Workbooks(workbookname). Activate.

任何人都可以帮助我为什么会发生这种情况以及如何使它起作用.我已经尝试了很多东西.但是没有任何效果.该代码是较大程序的一部分.

Can anyone help me why it is happening and how can I make it work. I have tried a lot of things. But nothing is working. This code is part of a larger program.

Sub test()

Dim workbkname As String
On Error GoTo Errorhandler
Retry:

workbookname = InputBox("Enter workbook name:", _
                        "Name Enrty")
If StrPtr(workbookname) = 0 Then
    MsgBox ("Aborting Program")
    End
End If

Workbooks(workbookname).Activate
Exit Sub

Errorhandler:
Response = MsgBox("Workbook " & workbookname & " not found", vbRetryCancel)
If Response = 4 Then
    GoTo Retry

End If

End Sub

推荐答案

此处的问题是，一旦给出了GoTo之类的指令，VBA错误处理程序就不会清除错误.结果，该代码认为它在您的错误处理例程中遇到了错误，从而将错误抛出堆栈.

The issue here is that the VBA Error Handler does not clear the error once given a directive like GoTo. As a result, the code thinks that it has encountered an error within your error handling routine and thus throws the error up the stack.

为了清除错误，您必须调用Resume并添加一个恢复位置(要么单独Resume再次运行错误代码，要么Resume Next继续执行错误后的代码行，或者<可以先调用c3>，然后调用标签，如下所示:

In order to clear the error, you must either call Resume with a place to resume (either Resume alone to run the erroneous code again, Resume Next to resume the line of code following the error, or Resume can be called followed by a label, as below:

Sub ErrTest
    On Error Goto ErrHndl
    For i = 0 to 5
        a = 1/i
nextiLabel:
    Next i

    Exit Sub
ErrHndl:
    Resume nextiLabel
End Sub

您也可以使用Err.Clear()消除错误，但建议不要这样做.

You can also use Err.Clear() to remove the error, but that is less recommended.

有关更多信息，请参见此处: Excel中的错误处理

See here for more info: Error Handling in Excel

这篇关于Excel VBA错误处理不适用于第二个错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！