VBA Excel check if a particular table exist using table name

Question

I have several tables in an excel sheet. Each having unique table Name. I want to know if a table which has a name "Table123" exist or not in the current sheet.

Could some one help me on this?

Thanks Jeevan

Solution 1

TableExists = False
On Error GoTo Skip
If ActiveSheet.ListObjects("Table123").Name = "Table123" Then TableExists = True
Skip:
    On Error GoTo 0

This code will work and avoid loops and errors

Solution 2

Here is an alternative function:

Function TableExistsOnSheet(ws As Worksheet, sTableName As String) As Boolean
    TableExistsOnSheet = ws.Evaluate("ISREF(" & sTableName & ")")
End Function

Solution 3

You can list shape collection and compare names like this

Sub callTableExists()

    MsgBox tableExists("Table1", "Shapes")

End Sub

Function TableExists(tableName As String, sheetName As String) As Boolean

    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets(sheetName)

    Dim tbl As ListObject

    With targetSheet
        For Each tbl In .ListObjects
            If tbl.Name = tableName Then TableExists = True
        Next tbl
    End With

End Function