How can I check a collection (e.g. worksheets) collection to be empty?
in [Programming]
|
Prev: Importing text file into Excel 2003 - C# - QueryTables.Add throws System.ArgumentException
Next: Hiding menu options
From: Michiel via OfficeKB.com on 25 Mar 2010 09:31 I have a function that checks if a worksheet exists by For Next-ing through the Worksheets collection. Function fncSheetExist(SheetName As String) As Boolean Dim sh As Worksheet fncSheetExist = False For Each sh In Worksheets If sh.Name = SheetName Then fncSheetExist = True Next sh End Function In the case there is no workbook open, this code will raise an error (1004) since the Worksheets collection does not exist or is empty. How can I test for that upfront? Without implementing error handling. These do NOT work: - If worksheets.count = 0 .... - If worksheets = Nothing ..... Any ideas? Thanks! -- Message posted via http://www.officekb.com
From: Dave Peterson on 25 Mar 2010 10:15
It looks like you're using the activeworkbook. Maybe you could check before you use the function: if activeworkbook is nothing then 'don't do it else 'do the function end if "Michiel via OfficeKB.com" wrote: > > I have a function that checks if a worksheet exists by For Next-ing through > the Worksheets collection. > > Function fncSheetExist(SheetName As String) As Boolean > Dim sh As Worksheet > > fncSheetExist = False > For Each sh In Worksheets > If sh.Name = SheetName Then fncSheetExist = True > Next sh > End Function > > In the case there is no workbook open, this code will raise an error (1004) > since the Worksheets collection does not exist or is empty. > > How can I test for that upfront? Without implementing error handling. > These do NOT work: > - If worksheets.count = 0 .... > - If worksheets = Nothing ..... > > Any ideas? > > Thanks! > > -- > Message posted via http://www.officekb.com -- Dave Peterson |