Check if worksheet exists by name

Question

I am wanting to validate if a worksheet exists, and if not then ask the user to enter select a sheet from those that exist. Looking over the information on this previous post i came up with a function that returns a boolean and then I will prompt if the result is false.

I am having an issue where the custom sheet names I have entered return false, but the default "Sheet1"... return true. Looking over the object model I do not see one listed for Worksheets.Name.Value and looking at the project explorer I see that the sheets are listed as `Sheet 1 (Macro Variables).

How do I reference the name of the sheet in parenthesis so that my function will work, or if not possible using sheet names, is there a better solution?

Here is my code

    Sub TestBed()
    Dim wb As Workbook, test As Boolean, debugStr As String, wsNames() As String
    Set wb = ThisWorkbook
    Debug.Print "List of sheets in this workbook"

    For i = 1 To wb.Worksheets.count
        ReDim Preserve wsNames(i - 1)
        wsNames(i - 1) = wb.Worksheets(i).Name
        debugStr = debugStr & wsNames(i - 1) & " | "
    Next i

    Debug.Print debugStr
    debugStr = ""

    For i = LBound(wsNames) To UBound(wsNames)
        test = ValidateWorksheetExists(wsNames(i), wb)
        debugStr = debugStr & wsNames(i) & " = " & test & " | "
    Next i

    Debug.Print debugStr
End Sub

Function ValidateWorksheetExists(sName As String, Optional wb As Workbook) As Boolean

If wb Is Nothing Then Set wb = ThisWorkbook

    With wb
        For i = 1 To .Worksheets.count
            If wb.Worksheets(i).Name = sName Then
                ValidateWorksheetExists = True
            Else
                ValidateWorksheetExists = False
            End If
        Next i
    End With
End Function

I'm confused, the bit in brackets is the sheet name. The bit in front is the Code Name. — SJR, Apr 19 '18 at 22:57

score 2 · Answer 1 · answered Apr 19 '18 at 23:17

2

You need to exit the function right after:

ValidateWorksheetExists = True

Otherwise the next iteration of i will set it to False again.

(there may be other errors)

answered Apr 19 '18 at 23:17

Gary's Student

95,722
10
59
99

score 0 · Accepted Answer · answered Apr 19 '18 at 23:24

Although a Boolean variable (err function in this case) defaults to False when declared, it's good practice to be explicit... and since ValidateWorksheetExists starts out as False, there's no need to again set it to False. Doing so reverts a possible True state that was set on a prior iteration of 'i'

Function ValidateWorksheetExists(sName As String, Optional wb As Workbook) As Boolean

If wb Is Nothing Then Set wb = ThisWorkbook

ValidateWorksheetExists = False

    With wb
        For i = 1 To .Worksheets.count
            If wb.Worksheets(i).Name = sName Then
                ValidateWorksheetExists = True
            End If
        Next i
    End With
End Function

Thanks for the point of the default value of being set to false. I was overwriting true values to false. I was under the assumption that once I provided a return value for the function that the function would stop processing iterations and exit. Both a logical and lack of understanding of how VBA processes escapes from functions on my part — Bezurn, Apr 20 '18 at 00:33

Check if worksheet exists by name

2 Answers2