Check existence of item in collection [closed]

Question

Checking whether an item exist in a collection isn't straightforward in VBA, it requires error checking and during error handling we need to clear the error and also resume code to be able to correctly manage the next error.

In my codes I generally use this structure, is it a good practice?

    With MyWorkBook
        On Error GoTo NoItem
        With .Sheets(SheetName)
            ' do stuff here if sheet exists
        End With
NoItem:
        If False Then
            Err.Clear
            ' code to do if sheet doesn't exist (e.g. add a new one)
            Resume NoItem
        End If
        On Error GoTo 0 ' (or specify another label for error handling)
        ' continue macro ...
    End With

Answer 1

Don't make errors a part of your program flow

If an error doesn't cause you to prematurely end your Sub/Function, then it should be handled less severely, or your program needs restructuring. I also caution against using GOTOs. They are sometimes necessary, but they have the potential to cause far more problems than they solve.

This is the only situation in which I regularly use GOTOs in VBA:

    Public Sub DoThing()
    
        On Error GoTo CleanFail
        
        ...
        
        ...
        
        ...
        
CleanExit:
    
        '/ Clean Up
        Exit Sub
        
CleanFail:
        
        '/ Error Handling
        '/ Error handling
        '/ Error handling
        Resume CleanExit
        
    End Sub

---

Handling Conditions

If you are going to continue your macro anyway, but want to avoid running some code if a condition fails then why not just:

[Check Condition]
If [condition] Then
    DoThing
End If

---

In the specific case of the Sheets() Collection, you have a number of options.

---

Option #1: Write a utility function to do the checking for you

    Public Function GetSheet(ByRef targetBook As Workbook, ByVal sheetName As String) As Worksheet
        '/ Tries to return the sheet object.
        '/ If the sheet does not exist, return Nothing
        
        Dim targetSheet As Worksheet
        On Error GoTo CleanFail
            Set targetSheet = targetBook.Worksheets(sheetName)
        On Error GoTo 0
        
CleanExit:
        Set GetSheet = targetSheet
        Exit Function
        
CleanFail:
        On Error GoTo 0
        Err.Clear
        Set targetSheet = Nothing
        Resume CleanExit
        
    End Function

And now your code can be structured like so:

    Dim targetSheet As Worksheet
    Set targetSheet = GetSheet(ThisWorkbook, sheetName)
    If Not targetSheet Is Nothing Then
        ' do stuff here (don't do it if SheetName doesn't exist)
    End If

Much cleaner.

---

Option #2: Use a data structure that supports .Exists()

For VBA, this would be a Dictionary. You'll need to set a reference to the Microsoft Scripting Runtime library in Tools --> References. Then do the following:

    Dim sheetnames As Dictionary
    Set sheetnames = New Dictionary
    
    Dim sheetName As String
    Dim sheetObject As Worksheet
    For Each sheetObject In ThisWorkbook.Sheets()
        sheetName = sheetObject.Name
        sheetnames.Add sheetName, sheetName
    Next sheetObject
    
    If sheetnames.Exists("Some sheet name") Then
        ' do stuff here (don't do it if SheetName doesn't exist)
    End If

---

Option #3: Restructure your program to make the check redundant

I don't know if this is a good idea for your code. I'd have to know more about its' wider purpose, structure and context, but it's always worth thinking about.

Answer 2

IMO, you're better off with a function that either returns a boolean that confirms the sheet is present (before you try to do something with it), or returns a reference to the sheet or otherwise Nothing.

That way you can keep you error handling concise, and avoid the need for labels.

Here's an example of the reference approach:

Sub foo()
  Dim sht As Worksheet

  Set sht = GetSheet("Next Week's Lottery Results")
  'Check we got the sheet we wanted
  If Not sht Is Nothing Then
    'Do stuff
    Debug.Print sht.UsedRange.Address
  End If

End Sub

Function GetSheet(SheetName As String) As Worksheet

  On Error Resume Next
  Set GetSheet = ThisWorkbook.Sheets(SheetName)
  On Error GoTo 0

End Function