Problems with function

Question

I am trying to get the alert for sheet already exist to work, however, I am lost. Is the function not defined correct or?

 Public Sub CopySheets()

    Do

        shName = InputBox("Please enter name of new project", "New Project")

        If shName <> "" Then

        shExists = SheetExists(shName) 'Check for existing sheet name
        If Not shExists Then
        Worksheets(Array(1, 2)).Copy After:=Sheets(Sheets.Count)


        Else

        MsgBox "Project Name:" & Space(1) & shName & " already exists", vbOKOnly + vbCritical, "Deter"
        End If
        End If
    Loop Until Not shExists Or shName = ""
End Sub

Private Function SheetExists(ByVal sheetName As String, _
        Optional ByVal wb As Workbook)

        If wb Is Nothing Then Set wb = ActiveWorkbook
        On Error Resume Next
        SheetExists = Not wb.Worksheets(sheetName) Is Nothing

End Function

Answer 1

You forgot to add the type of the function. It must be boolean

    Private Function 

    SheetExists(ByVal sheetName As String, _
        Optional ByVal wb As Workbook) As Boolean

        If wb Is Nothing Then Set wb = ActiveWorkbook
        On Error Resume Next
        SheetExists = Not wb.Worksheets(sheetName) Is Nothing

    End Function

The issue with your code is that in case the sheet does not exist the line SheetExists = Not wb.Worksheets(sheetName) Is Nothing will raise an error and SheetExists will keep its default value which is empty as the default data type of a function without defining the data type is variant. If you define the data type as boolean the default value will be False.

IMHO this code is clearer though longer

Private Function SheetExists(ByVal sheetName As String, _
    Optional ByVal wb As Workbook) As Boolean

    Dim ws As Worksheet

    If wb Is Nothing Then Set wb = ActiveWorkbook

    On Error GoTo EH
    Set ws = wb.Worksheets(sheetName)
    SheetExists = True

    Exit Function

EH:
    SheetExists = False

End Function