Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code Module in Sheets Collection | Excel Programming | |||
Loading Sheets into a Customer Collection | Excel Programming | |||
VSTO: Sheets collection a 1-based array(?!?!?) | Excel Programming | |||
creating a collection of Sheets references | Excel Programming | |||
collection of sheets | Excel Programming |