VBA Excel check if a particular table exist using

问题:

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

回答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

回答2:

Here is an alternative function:

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

回答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

回答4:

Another option, using a bit lazy approach with error catching:

Public Sub TestMe()

    If TableExists("Table1243", ActiveSheet) Then
        MsgBox "Table Exists"
    Else
        MsgBox "Nope!"
    End If

End Sub    

Public Function TableExists(tableName As String, ws As Worksheet) As Boolean

    On Error GoTo TableExists_Error
    If ws.ListObjects(tableName).Name = vbNullString Then
    End If
    TableExists = True

    On Error GoTo 0
    Exit Function

TableExists_Error:    
    TableExists = False    

End Function