Excel Table Code | Get XL Table

'
'////////////////////////////////////////////////////////////////////////////////////////////////
'// Procedure:  Function XLTGetTable
'// Author:     Duncan Salmon, 365Enterprises Ltd
'// Date:       24-Feb-15
'// Dev stage:  Beta
'// Desc:       Takes an XLT name (and an optional workbook name containing the XLT), and sets
'//             it to the function as a LISTOBJECT so it can be used easily in code
'//             Requires the optional workbook to be OPEN
'//             If errors, or cannot find table, will be "Is Nothing"
'//             Reverts to ACTIVE workbook if no bookname given
'// Arguments:  XLTable name & optional workbook name, both strings
'//             (workbook name is name including .xlsx/m etc, but NOT with path)
'// Returns:    Either the XLT as a listobject, or nothing
'// Requires:   Relies on errors being generated, so be sure to keep On Error Resume Next
'////////////////////////////////////////////////////////////////////////////////////////////////

Function XLTGetTable(XLTName As String, Optional OpenXLBookName As String) As ListObject

Dim sht As Worksheet

'   MUST set this, both in case XLTName or OpenXLBook names are incorrect, but also
'   the test for the list object relies on errors beiong generated and tested for
    On Error Resume Next

'   Default of ACTIVE workbook, so no need for later loop, can simply set table directly
    If OpenXLBookName = "" Or OpenXLBookName = ActiveWorkbook.Name Then

        Set XLTGetTable = Range(XLTName).ListObject
    
    Else

'       Loop through sheets in workbook to see if they contain our tbale as a listobject
        For Each sht In Workbooks(OpenXLBookName).Worksheets
'           This line will error if XLTName not found, so MUST be under On Error Resume Next

            Set XLTGetTable = sht.ListObjects(XLTName)
            If Not XLTGetTable Is Nothing Then Exit For

    
        Next sht

    End If

On Error GoTo 0


End Function