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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top