Checking if an Object Exists in a Collection

[vba_corner]

Excel uses collections of objects a lot, and sooner or later you'll be in the need of checking if a certain object exists in a collection. For example, to check if a workbook was already opened, check if it's in the collection of open workbooks (Application.Workbooks), to check if a certain worksheet exists, check if it's in the collection of worksheets (e.g. ActiveWorkbook.Worksheets) and so on...

So how to check this? The obvious approach would be to loop over all objects of the collection with a For Each loop and compare the object's name with the one you want to check until you find a match or reach the end without finding one.

But a quicker and easier solution is the following: Assuming your object exists in the collection, try to address it. If the object indeed exists, this will work. If the object doesn't exist, this will cause an error that can be caught, and then you know it doesn't exist.

So for the above mentioned example, a function that would check if a workbook with a certain name is open would look like this:

'returns True if the workbook with the given name
'is open and returns False if not
Function WorkbookIsOpen(i_Name As String) As Boolean
On Error Resume Next
WorkbookIsOpen = Not Workbooks(i_Name) Is Nothing
End Function
The same for the second example, to check if a worksheet exists:

'returns True if the sheet exists in the given workbook
'(or the active workbook if no name is given)
'and False if the sheet doesn't exist
Function SheetExists(i_SheetName As String, _
Optional i_WorkbookName As String) As Boolean
Dim myWorkbook As Workbook

On Error Resume Next
'set workbook that gets checked
If i_WorkbookName = "" Then
Set myWorkbook = ActiveWorkbook
Else
Set myWorkbook = Workbooks(i_WorkbookName)
If myWorkbook Is Nothing Then Exit Function
End If
'now check if sheet exists
SheetExists = Not myWorkbook.Sheets(i_SheetName) Is Nothing
End Function
And this is a simple example how to use the above functions:

Sub Test()
Dim myWorkbookName As String
Dim mySheetName As String

myWorkbookName = "Book1.xls"
If WorkbookIsOpen(myWorkbookName) = True Then
'workbook is open
Debug.Print myWorkbookName & " is open."
Else
'workbook isn't open
Debug.Print myWorkbookName & " is not open."
'try to open workbook
On Error Resume Next
Workbooks.Open Filename:=myWorkbookName
On Error GoTo 0
'now check if the opening was succesful
If WorkbookIsOpen(myWorkbookName) = True Then
Debug.Print myWorkbookName & " was opened."
Else
'workbook couldn't be opened, so create a new one
With Workbooks.Add
.SaveAs Filename:=myWorkbookName
Debug.Print "Workbook was created and saved as " & .FullName
End With
End If
End If

'now check for the worksheet
mySheetName = "Sheet4"
If SheetExists(mySheetName, myWorkbookName) = True Then
'sheet exists
Debug.Print mySheetName & " in " & myWorkbookName & " exists."
Else
'sheet doesn't exist
Debug.Print mySheetName & " in " & myWorkbookName & " doesn't exist."
'create sheet
With Workbooks(myWorkbookName).Worksheets.Add
.Name = mySheetName
End With
Debug.Print mySheetName & " was created in " & myWorkbookName
End If
End Sub(Debug.Print writes messages to the immediate window. The advantage to a MsgBox is that you don't have to press OK after each message. If you still prefer message boxes, just replace Debug.Print by MsgBox)

And this approach works for all collections of objects that Excel uses, not only workbooks and worksheets.