Is there a way to programmatically determine if error handling has been turned on in VBA?

Question

Background

Hello there! I'm stuck trying to decide between efficiency and robustness. This is a question of VBA in Excel.

I have a function, GetTable (below) that takes a table name and returns the corresponding ListObject within my Workbook. I prefer this method to explicitly calling the ThisWorkbook.Worksheet.ListObjects("strTableName") because it provides coding flexibility if tables get moved into different sheets.

Originally this was done by looping through each Worksheet and checking whether each ListObject had a matching name with the provided input. This is Option 1 of the code below. This method works well enough though it's not particularly efficient and can slow things down if you have a large workbook with many sheets and tables, and you're grabbing multiple tables in your macro.

To improve efficiency I changed to Option 2, which explicitly calls the table on each sheet. If the table doesn't exist then it throws an error, and error handling allows it to move directly to the next Worksheet. This works perfectly for normal usage of the workbook when error handling is turned on. However when error handling is turned off during debugging this becomes a pain because the code will always get stuck here.

Questions

1. Is there a way I can programmatically tell if error handling is turned on or off so the function can switch between these two methods depending on the case? I realize this can be risky because it'll run through different code during debugging, but I'd still like to know if this is possible.
2. If not, is there another way I can accomplish a similar method to Option #2 (or a more efficient one if you have an idea!) without throwing an error?

I know for this function the efficiency gains often won't matter unless you a ridiculous amount of tables, but I share this as part of a larger library of functions for less code savvy coworkers so they may not use it effectively and include it in large loops. Also finding the right approach may come in handy for applying to other functions.

Thanks in advance and good health to you!

Code

Function GetTable(strTableName As String) As ListObject
'This function searches the workbook for a table with the exact name strTableName
'Returns the table object
'If nothing found then display message
On Error Resume Next

Dim sht As Worksheet
Dim tbl As ListObject

'#Option 1: Slower but doesn't throw errors
'For Each sht In ThisWorkbook.Worksheets
'    For Each tbl In sht.ListObjects
'        'Debug.Print sht.Name & " " & tbl.Name      'uncomment to print all table names
'
'        If LCase(tbl.Name) = LCase(strTableName) Then
'            Set GetTable = tbl
'            Exit Function
'        End If
'    Next tbl
'Next sht

'#Option 2: More efficient but causes problems when debugging
For Each sht In ThisWorkbook.Worksheets
    
    Set GetTable = sht.ListObjects(strTableName) 'Generates runtime error 9 if table doesn't exist on sheet
    
    If Err.Number = 0 Then Exit Function  'No error means we've found the answer
    
    Err.Clear

Next sht

'If the code reaches this point it means the table wasn't found.
'This may have negative implications depending on where this function is called.
'This message gives the user an out
Dim ans As Byte
ans = MsgBox("Could not find table with name '" & strTableName & "'." & vbNewLine & vbNewLine & _
      "Would you like to abort code?", vbCritical + vbYesNo, "Table not found")
      
If ans = vbYes Then End

'Set GetTable = Nothing   '#This is redundant

End Function

Answer 1

This is what I mean by cache:

Function GetTable(ByVal strTableName As String, _
                         Optional reset As Boolean = False) As ListObject
    Static dict As Object 'Static, so persists between calls
    Dim sht As Worksheet
    Dim tbl As ListObject, nm
    If reset Then Set dict = Nothing  '<< clear the cache
    If dict Is Nothing Then
        Set dict = CreateObject("scripting.dictionary")
        For Each sht In ThisWorkbook.Worksheets
            For Each tbl In sht.ListObjects
                nm = LCase(tbl.Name)
                If Not dict.exists(nm) Then dict.Add nm, tbl
            Next tbl
        Next sht
    End If
    strTableName = LCase(strTableName)
    If dict.exists(strTableName) Then Set GetTable = dict(strTableName)
End Function

The first time you call it, it scans all the listobjects, but after that it will use the dictionary as a lookup.

You need to be aware of when you might need to clear the cache, to take into account added or deleted listobjects.

Answer 2

More as a comment to your question about a more efficient approach....

I personally don't like this approach because of the unqualified Range call and the Activate, but you may be able to use a function like the following, instead of looping through all the worksheets and tables:

Private Function GetTable(ByVal TableName As String, Optional ByVal wb as Workbook) As ListObject
    If wb Is Nothing Then Set wb = ThisWorkbook
    wb.Activate
    Set GetTable = Range(TableName).ListObject
End Function

Of course making this more robust in case no table with that name exists in the workbook... (which basically means I haven't answered the crux of your question).

Answer 3

Replace If ans = vbYes Then End with Replace If ans = vbYes Then Exit Function because End is the "self destrcution" button for the code. Look in the MS documentation for further reading.

The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Code you have placed in the Unload, QueryUnload, and Terminate events of forms and class modules is not executed. Objects created from class modules are destroyed, files opened by using the Open statement are closed, and memory used by your program is freed. Object references held by other programs are invalidated.

PS If you are talking about the error option which can be set via the VBE you might be out of luck, see Set the appropriate error handling level