Excel VBA error handling not working for second error

Question

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

Use Resume Retry instead of GoTo Retry

chris neilsen
– chris neilsen

2016-03-11 00:23:40 +00:00
Commented Mar 11, 2016 at 0:23 — chris neilsen
– chris neilsen, Commented Mar 11, 2016 at 0:23

Community · Accepted Answer · 2018-05-08 21:49:10Z

14

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.

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

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

See here for more info: Error Handling in Excel

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Mar 11, 2016 at 0:35

CodeJockey

1,9811 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA error handling not working for second error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related