Test for membership in sheets collection
 

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

Test for membership in sheets collection
 

You could use a function from Chip Pearson to test the existence of a sheet.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

Tom wrote:

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

--

Dave Peterson

Test for membership in sheets collection
 

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

--
Gary''s Student - gsnu200845


"Tom" wrote:

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

Test for membership in sheets collection
 

Two points about the last statement in the code you posted...

1) The greater-than-zero comparison is unnecessary as the CBool function
call will automatically return False for a zero argument and True for any
other numeric argument.

2) The CBool function call is unnecessary as well. The fact that
WorksheetExist is declared as a Boolean function, assigning a numeric value
to it will cause it to perform a "behind the scenes" CBool call in order to
coerce the number to a True/False value. So, your last statement can be
reduced to this...

WorksheetExists = Len(WB.Worksheets(SheetName).Name)

I would also note, as an aside, that we can eliminate the WB variable
altogether by substituting the IIf function call for the WB reference in
that last statement. Hence, the function can be reduced to this...

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
On Error Resume Next
WorksheetExists = Len(IIf(WhichBook Is Nothing, ThisWorkbook, _
WhichBook).Worksheets(SheetName).Name)
End Function

I realize this is a little less readable than when the WB variable is used,
which is not a bad reason to decide against doing it this way, but I would
think not having to declare the variable and then perform a subsequent Set
operation on it should make the code just a little bit more efficient.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
You could use a function from Chip Pearson to test the existence of a
sheet.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
...
if worksheetexists("myname",activeworkbook) then

Tom wrote:

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

--

Dave Peterson

Test for membership in sheets collection
 

See my second point in the response I gave to Dave's posting.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

--
Gary''s Student - gsnu200845


"Tom" wrote:

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

Test for membership in sheets collection
 

I like to use both of these. (IIRC, I added the cBool() to Chip's
function--maybe even the 0, too.)

I think that they serve both a documentation and a pedagogical purpose. (I also
use the "Call" statement <vbg.)

And I should have included a "on error goto 0" line before the "end sub" line
for the same reasons.

And your changes may make the running code more efficient, but it may mean that
more time is lost by anyone reviewing the code or using it in other situations
(in general--not this particular short snippet).



Rick Rothstein wrote:

Two points about the last statement in the code you posted...

1) The greater-than-zero comparison is unnecessary as the CBool function
call will automatically return False for a zero argument and True for any
other numeric argument.

2) The CBool function call is unnecessary as well. The fact that
WorksheetExist is declared as a Boolean function, assigning a numeric value
to it will cause it to perform a "behind the scenes" CBool call in order to
coerce the number to a True/False value. So, your last statement can be
reduced to this...

WorksheetExists = Len(WB.Worksheets(SheetName).Name)

I would also note, as an aside, that we can eliminate the WB variable
altogether by substituting the IIf function call for the WB reference in
that last statement. Hence, the function can be reduced to this...

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
On Error Resume Next
WorksheetExists = Len(IIf(WhichBook Is Nothing, ThisWorkbook, _
WhichBook).Worksheets(SheetName).Name)
End Function

I realize this is a little less readable than when the WB variable is used,
which is not a bad reason to decide against doing it this way, but I would
think not having to declare the variable and then perform a subsequent Set
operation on it should make the code just a little bit more efficient.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
You could use a function from Chip Pearson to test the existence of a
sheet.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
...
if worksheetexists("myname",activeworkbook) then

Tom wrote:

I want to test the worksheets collection for a specific worksheet name.
True/False response is what I'm loking for.
Suggestions?

--

Dave Peterson

--

Dave Peterson