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屋!