CODEWITHSUNDEEP
vbaexcel
Bezurn
Bezurn

Reputation: 18

Check if worksheet exists by name

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

Upvotes: 0

Views: 833

Answers (2)

Bill Roberts
Bill Roberts

Reputation: 1171

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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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)

Upvotes: 2

Related Questions