User Defined Function to check whether sheet name exists is not working

The OP problem is that the line
Sheets(SheetName) throws an error in the "no sheet" case

Try

Please Login or Register  to view this content.

Originally Posted by sundardrona

Please highlight what is the mistake in the code. It is able to return the value "Sheet already exists" when the sheet name exists. In else condition it throws #VALUE! error.

Function SundarSheetExists(SheetName As String) As String
For Each Sheet In Worksheets
If SheetName = Worksheets(SheetName).Name Then
SundarSheetExists = "Sheet already exists"
Else
SundarSheetExists = "Sheet is not available"
End If
Next
End Function

Mike is correct about where the error is coming from; however, the problem in your code's logic is what I highlighted in red... you are testing the SheetName argument against the wrong thing... your loop variable is Sheet, you should be testing againsts its name since you want to see if the SheetName argument equals the loop's currently iterated sheet's name. Try changing the If..Then statement it is in to this...

Please Login or Register  to view this content.

I would note that this test is case sensitive, so you might want to handle that by using this instead...

Please Login or Register  to view this content.

And one final point... once you find the match, you should stop the loop by using

Please Login or Register  to view this content.

otherwise if any sheets are iterated in the loop after the match is found, its name won't match and the function will be reset to "Sheet is not available".