Unexpected behaviour of 'On Error' 2nd time round

[sijpie]

Run the following code a few times, and then also supply a non existing sheet name. The second time you do that (does not have to be sequentially) the function fails, the on Error will not help out. Why is this happening?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetSheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsP <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> vInp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <br>    vInp = InputBox("Provide 1st SheetName. 0 to quit.", _<br>        Title:="Bug test Sheets")<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> vInp <> 0 And vInp <> vbNullString<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NotExist<br>        <SPAN style="color:#00007F">Set</SPAN> wsP = Sheets(vInp)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <br>        <SPAN style="color:#00007F">GoTo</SPAN> NextSheet<br>NotExist:<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        MsgBox "Invalid sheet name given. Sheet " & vInp & " does not exist."<br>NextSheet:<br>        <SPAN style="color:#00007F">Set</SPAN> wsP = <SPAN style="color:#00007F">Nothing</SPAN><br>        vInp = InputBox("Enter next SheetName. 0 to quit.", _<br>            Title:="Bug test Sheets")<br>    <br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[sijpie]

It seems to me it has something to do that the On Error is happening inside a loop. When I add a separate function to do the checking, the problem goes away. Still don't understand why it fails in the first place. And so still I am open to comments on this.

Working code:

<font face=Courier New><br><SPAN style="color:#007F00">'----------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(SheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Function to check existence of worksheet. Returns TRUE _<br>  if exists, else FALSE</SPAN><br><SPAN style="color:#007F00">'----------------------------------------------------------</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NoSheet<br>    <SPAN style="color:#00007F">Set</SPAN> ws = Sheets(SheetName)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#007F00">' sheet exists</SPAN><br>    SheetExists = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>NoSheet:<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> GetSheet2()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsP <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> sInp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <br>    sInp = InputBox("Provide 1st SheetName. 0 to quit.", _<br>        Title:="Bug test Sheets")<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> sInp <> "0" And sInp <> vbNullString<br>        <SPAN style="color:#00007F">If</SPAN> SheetExists(sInp) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wsP = Sheets(sInp)<br>        Else: <SPAN style="color:#00007F">GoTo</SPAN> NotExist<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">GoTo</SPAN> NextSheet<br>NotExist:<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        MsgBox "Invalid sheet name given. Sheet " & sInp & " does not exist."<br>NextSheet:<br>        <SPAN style="color:#00007F">Set</SPAN> wsP = <SPAN style="color:#00007F">Nothing</SPAN><br>        sInp = InputBox("Enter next SheetName. 0 to quit.", _<br>            Title:="Bug test Sheets")<br>    <br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Marcelo Branco]

Try this

Sub GetSheet()
    Dim wsP As Worksheet
    Dim vInp As Variant
    
     vInp = InputBox("Provide 1st SheetName. 0 to quit.", _
        Title:="Bug test Sheets")
    Do While vInp <> 0 And vInp <> vbNullString
        On Error GoTo NotExist
        Set wsP = Sheets(vInp)
        On Error GoTo 0
                
        GoTo NextSheet
NotExist:
        On Error GoTo 0
        MsgBox "Invalid sheet name given. Sheet " & vInp & " does not exist."
        [COLOR=#ff0000]Resume Next[/COLOR]
NextSheet:
        Set wsP = Nothing
        vInp = InputBox("Enter next SheetName. 0 to quit.", _
            Title:="Bug test Sheets")
    Loop
    
End Sub

To understand what was going on in your first code take a look at
Error Handling In VBA

See the topic
The Resume Statement

M.

 

[Marcelo Branco]

I think you can do something simpler like

Sub GetSheet3()
    Dim wsP As Worksheet
    Dim vInp As Variant
    
    Do
        vInp = InputBox("Provide 1st SheetName. Cancel or blank to quit.", _
        Title:="Bug test Sheets")
        
        If vInp = vbNullString Or vInp = False Then Exit Sub
        
        On Error Resume Next
        Set wsP = Sheets(vInp)
        
        If Err <> 0 Then
            On Error GoTo 0
            MsgBox "Invalid sheet name given. Sheet " & vInp & " does not exist."
        End If
        
    Loop
End Sub

M.

 

[sijpie]

Thanks Marcelo. Particularly for the pointer to Pearson's explanation. I read it before, but now I understand it!