Exit Function doesn't break out of For loop?

[odeno]

Am writing a simple function to test of the existence of a worksheet in the current workbook

The code below evaluates to false each time even though WsName exists in the workbook wb.

Private Function WorkSheetExists(ByRef WsName As String, wb As Workbook) As Boolean
    
    Dim i As Long  
    
    For i = 1 to i= wb.Sheets.Count
        If (StrComp(WsName, wb.Sheets(i).Name) = 0) Then          
            WorkSheetExists = True
            Exit Function
        End If
        
       Next i

    WorkSheetExists = False
    
End Function

A little investigation reveals this is because the Exit Function statement isn't causing an exit of the Function.

My rewrite is:-

Private Function WorkSheetExists(ByRef WsName As String, wb As Workbook) As Boolean
    
    Dim i As Long
    Dim Found As Boolean
    
    Found = False
    i = 1
    
    Do While Not Found And i <= wb.Sheets.Count
        If (StrComp(WsName , wb.Sheets(i).Name) = 0) Then           
            Found = True
            WorkSheetExists = Found
'            Exit Function  'not needed as it doesn't exit function...but why?
        End If       
        
        i = i + 1
    Loop
    
    WorkSheetExists = Found
    
End Function

The rewrite works, just wondered why the Exit Function doesn't work in scenario 1?

[dominicb]

Good afternoon odeno

just wondered why the Exit Function doesn't work

As you have discovered, the Exit Function isn't designed to break out of a For ... Next loop.

To do this use Exit For, which will jump to the next statement outside of the loop. Other uses for Exit are :
Exit Do
Exit Property
Exit Sub

HTH

DominicB

[antoka05]

In your first macro the for seems to be incorrect. You wrote:

For i = 1 to i= wb.Sheets.Count

but I think you wanted to write:

For i = 1 to wb.Sheets.Count

The second macro don't need the exit function because the 'do while.... loop' statement ends when found is true but if you use 'exit function' it doesn't create problems.

In your macro, if you write sheet name to look for in lower case it doesn' find it. It's better if you use lcase or ucase conversione, for example:

Private Function WorkSheetExists(ByRef WsName As String, wb As Workbook) As Boolean
    Dim i As Long
    
    WorkSheetExists = False
    For Each ws In wb.Sheets
        If LCase(WsName) = LCase(ws.Name) Then
            WorkSheetExists = True
            Exit Function
        End If
    Next
End Function

Regards,
Antonio

[odeno]

Thanks folks.

I spotted the for typo after posting. I like the second form of the function.........

thanks again.

[odeno]

Interesting point - setting

WorkSheetExists = False

Doesn't cause the function to return?