How can I check a collection (e.g. worksheets) collection to be empty?

  • Thread starter Michiel via OfficeKB.com
  • Start date
M

Michiel via OfficeKB.com

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!
 
D

Dave Peterson

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
 
Top