If WorkSheet("wsName") Exists

Question

I'm wondering if there is clean cut functionality that returns True or False if a worksheet inside a workbook exists?

It would be good, but not essential, if it's possible to do it without skipping error handling.

The only thing I've found doesn't really work:

On Error Resume Next
If (Worksheets("wsName").Name <> "") Then
    Debug.Print "Worksheet exists!"
Else
    Debug.Print "Worksheet doesn't exist!"
End If
On Error GoTo ErrHandler

See also http://stackoverflow.com/q/6838437/2707864 and http://stackoverflow.com/q/6688131/2707864 — sancho.s ReinstateMonicaCellio, Sep 06 '14 at 19:15
@chris-neilsen this question is marked as duplicate, yet it was asked first? — Matt Rowles, Feb 22 '15 at 11:12

score 94 · Accepted Answer · edited Jul 20 '16 at 20:58

94

A version without error-handling:

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

edited Jul 20 '16 at 20:58

TZubiri

886
11
30

answered May 18 '11 at 05:57

Dante May Code

11,177
9
49
81

3

@Dante is not Greek. Isn't it better if we dim 'ws'? – Feb 21 '14 at 19:16
I use this and sometimes (I can't consistently replicate the error) it says I have 1004 run-time error: Application-defined or object-defined error. When I debug it is on the 'For Each' line. Any reason for this? I am new to VBA Excel programming... – tmwoods Apr 25 '14 at 22:44
How Can we make this as a case sensitive search? – Ashwin Jun 29 '17 at 10:36
@Ashwin The presented solution *is* case sensitive. If the value of `sheetToFind` (e.g. "sheet1") is capitalized differently that `sheet.name` (e.g. "Sheet1"), the function will return `False`. [This answer](https://stackoverflow.com/a/11414255/7778672) provides a way to ignore case. – Inarion Dec 20 '18 at 13:22
The proposed solution has an implicit reference to `ActiveWorkbook` where the sole `Worksheets` is used. This can lead to unintended behavior when another workbook becomes active before the function is executed. [The answer by @TimWilliams](https://stackoverflow.com/a/6040390/7778672) does this better by allowing to specify a workbook to work on. (As such it's up to the caller to decide whether to provide a solid reference or to play Excel-Roulette.) – Inarion Dec 20 '18 at 13:28
Careful, the search must not be case sensitive: https://www.howtoexcel.org/how-to-check-if-a-worksheet-exists-using-vba/ You could write "sheetToFind = Lcase(sheetToFind)", then "If sheetToFind = Lcase(sheet.Name) Then" – LePatay Jun 14 '22 at 08:55

score 32 · Answer 2 · answered May 18 '11 at 05:47

32

There's no built-in function for this.

Function SheetExists(SheetName As String, Optional wb As Excel.Workbook)
   Dim s As Excel.Worksheet
   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set s = wb.Sheets(SheetName)
   On Error GoTo 0
   SheetExists = Not s Is Nothing
End Function

answered May 18 '11 at 05:47

Tim Williams

154,628
8
97
125

3

Thanks, looks good. I used this one first and it worked a charm also. – Matt Rowles May 18 '11 at 06:15
2

One should probably use `ActiveWorkbook` instead of `ThisWorkbook`. The latter refers to the workbook that contains the macro code, which might be different from the workbook than one wants to test. I guess `ActiveWorkbook` would be useful for most cases (contrived situations are always available, though). – sancho.s ReinstateMonicaCellio Sep 06 '14 at 18:47
8

I disagree that ActiveWorkbook makes for a better "default" case - in practice relying on any particular workbook (or worksheet) being active for your routine to operate on the "right" object makes for brittle code. Always better to pass a reference to the object instead of relying on ActiveXXXX. I use `ThisWorkbook` as the default because that is the same behavior you'd get if you just called `Sheets()` *without* any workbook qualifier - ie. the "simplest" case. – Tim Williams Sep 06 '14 at 23:18
Have had best results passing in Application.ActiveWorkbook as the optional wb. – bradbase Oct 31 '20 at 06:17

score 8 · Answer 3 · edited Apr 14 '16 at 12:04

also a slightly different version. i just did a appllication.sheets.count to know how many worksheets i have additionallyl. well and put a little rename in aswell

Sub insertworksheet()
    Dim worksh As Integer
    Dim worksheetexists As Boolean
    worksh = Application.Sheets.Count
    worksheetexists = False
    For x = 1 To worksh
        If Worksheets(x).Name = "ENTERWROKSHEETNAME" Then
            worksheetexists = True
            'Debug.Print worksheetexists
            Exit For
        End If
    Next x
    If worksheetexists = False Then
        Debug.Print "transformed exists"
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "ENTERNAMEUWANTTHENEWONE"
    End If
End Sub

Erik K. · Answer 4 · 2017-12-27T20:26:26.597

7

Another version of the function without error handling. This time it is not case sensitive and a little bit more efficient.

Function WorksheetExists(wsName As String) As Boolean
    Dim ws As Worksheet
    Dim ret As Boolean        
    wsName = UCase(wsName)
    For Each ws In ThisWorkbook.Sheets
        If UCase(ws.Name) = wsName Then
            ret = True
            Exit For
        End If
    Next
    WorksheetExists = ret
End Function

edited Dec 27 '17 at 20:26

answered Jul 10 '12 at 13:21

Erik K.

1,024
12
13

1

One should probably use `ActiveWorkbook` instead of `ThisWorkbook`. The latter refers to the workbook that contains the macro code, which might be different from the workbook than one wants to test. I guess `ActiveWorkbook` would be useful for most cases (contrived situations are always available, though). – sancho.s ReinstateMonicaCellio Sep 06 '14 at 18:46

score 4 · Answer 5 · answered Jun 06 '14 at 10:02

4

Slightly changed to David Murdoch's code for generic library

Function HasByName(cSheetName As String, _ 
                   Optional oWorkBook As Excel.Workbook) As Boolean

    HasByName = False
    Dim wb

    If oWorkBook Is Nothing Then
        Set oWorkBook = ThisWorkbook
    End If

    For Each wb In oWorkBook.Worksheets
        If wb.Name = cSheetName Then
            HasByName = True
            Exit Function
        End If
    Next wb
End Function

answered Jun 06 '14 at 10:02

ShamBhagwat

99
3

1

One should probably use `ActiveWorkbook` instead of `ThisWorkbook`. The latter refers to the workbook that contains the macro code, which might be different from the workbook than one wants to test. I guess `ActiveWorkbook` would be useful for most cases (contrived situations are always available, though). – sancho.s ReinstateMonicaCellio Sep 06 '14 at 18:46

If WorkSheet("wsName") Exists

5 Answers5

Linked

Related