PDA

Click to See Complete Forum and Search --> : Excel: How To: Simple "SheetExists" NATIVE function ???


Webtest
May 26th, 2006, 01:31 PM
Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA

Does anyone have a neat trick for identifying if a sheet with a particular name exists without having to go through an error exception branch? What I really want is a native Excel function that returns a boolean if the sheet exists ... like:

If ShtExists(aBook.aSheet("SheetName")) then

This has been bothering me for a long time. Here is what I presently use that DOES us an error branch:Option Explicit
'Create a specific sheet if it does not already exist
Sub MakeSheet()
Dim x As Long
Dim shtName As String
Dim shtNum As Long
Dim aBook As Workbook

'Set a handle for the book that we want to add the sheets into
Set aBook = ActiveWorkbook

'Set the name of the sheet that we want to add to the book
shtName = "MY FIRST SHEET"
'Set the position of the sheet in the book
shtNum = 1
On Error GoTo ERR_NO_SHEET
'See if the sheet exists by forcing an error
x = aBook.Sheets(shtName).Index
On Error GoTo 0

'Set the name of the sheet that we want to add to the book
shtName = "MY SECOND SHEET"
'Set the position of the sheet in the book
shtNum = 2
On Error GoTo ERR_NO_SHEET
'See if the sheet exists by forcing an error
x = aBook.Sheets(shtName).Index
On Error GoTo 0

Set aBook = Nothing
Exit Sub

ERR_NO_SHEET:
'Here is where we create the sheet if it doesn't exist
aBook.Sheets.Add Before:=Sheets(shtNum)
ActiveSheet.Name = shtName
Resume Next

End Sub
Yes, I know, I could easily write a new function with just a couple of lines. I just want to know if I am missing something obvious.

DKenny
May 26th, 2006, 01:37 PM
Here's my stab at a function to check sheet existance

http://www.vbforums.com/showthread.php?t=400514

Webtest
May 26th, 2006, 02:36 PM
I bit the bullet and wrote a simple Function:Option Explicit
'==================================================================
'Test to see if a named sheet exists
'Returns TRUE if the sheet exists
'Returns FALSE if the sheet does NOT exist
'
Function SheetExists(aBook As Workbook, aName As String) As Boolean
Dim x As Long

On Error GoTo ERR_NO_SHEET
'See if the sheet exists by forcing an error
x = aBook.Sheets(aName).Index
On Error GoTo 0

'If the sheet does NOT exist, you'll NEVER get here!
'The Sheet DOES exist ...
SheetExists = True
Exit Function

ERR_NO_SHEET:
'We got here with a FAILED attempt to identify a sheet with the given name
'The sheet does NOT exist
SheetExists = False
Exit Function

End Function
... but I'm still wondering if there is a NATIVE Excel function???