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

Function SundarSheetExists(SheetName As String) As String

On Error Resume Next
If LCase(SheetName) <> LCase(Worksheets(SheetName).Name) Then
    SundarSheetExists = "Sheet is avaliable"
Else
    SundarSheetExists = "Sheet already exists"
End If
On Error Goto 0
End Function

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...

If SheetName = Sheet.Name Then

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

If UCase(SheetName) = UCase(Sheet.Name) Then

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

Exit For

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".