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.
===============================
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.