How can I tell if a worksheet exists?

[Robert Stober]

Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can I
do this using VBA?

Thank you,

Robert Stober

 

[Vasant Nanavati]

Hi Robert:

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
On Error Resume Next
WorksheetExists = CBool(Len(Workbooks(wbName) _
.Worksheets(wsName).Name))
End Function

?WorksheetExists("Charts")
False 'True

Regards,

Vasant.

 

[Bob Phillips]

Robert,

Here's some code to show the principle

Dim oWS As Worksheet

On Error Resume Next
Set oWS = Activeworkbook.Worksheets("Chart")
On Error GoTo 0
If oWS Is Nothing Then
MsgBox "Worksheet does not exist"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ross Presser]

Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How
can I do this using VBA?

Thank you,

Robert Stober

An alternate way which does not "abuse" the error mechanism:
(function header shamelessly stolen from V. Nanavati)

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
Dim x as Variant
WorksheetExists = false
For Each x In Workbooks(wbName).Worksheets
If x.Name = wsName Then
WorksheetExists = True
Exit Function
Next
End Function

(I know it's silly to call it abuse, but I always get caught by
forgetting to do On Error Goto 0, so I tend to avoid using it as an
exception mechanism)

 

[losmac]

Sub Test()
MsgBox SheetExist("A")
End Sub

Function SheetExist(shName) As Boolean
Dim sh As Worksheet

On Error GoTo Err_SheetExist

Set sh = Worksheets(shName) 'if worksheet exist, object sh is set
SheetExist = True
Set sh = Nothing 'we don't need this object

Exit Function

Err_SheetExist:
SheetExist = False 'worksheet not existing
Set sh = Nothing 'free memory
End Function

 

[Robert Stober]

Thank you all for your generous response. I thought of this one before I saw
your collective responses:

' Add the chart sheet if needed
On Error Resume Next
Sheets("Chart").Select
If Err.Number <> 0 Then
Sheets.Add
ActiveSheet.Name = "Chart"
End If

I've never used functions before - looks like I need to expand my
vocabulary!

Thank you,

Robert