CODEWITHSUNDEEP
vbaexcelms-office
Jeevan
Jeevan

Reputation: 477

VBA Excel check if a particular table exist using table name

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

Upvotes: 6

Views: 31232

Answers (6)

Farway
Farway

Reputation: 811

Without the use of GoTo, which is a lot more powerfull than appropriate.

Set TableExists = False

On Error Resume Next
If ActiveSheet.ListObjects("Table123").Name = "Table123" Then Set TableExists = True

Be aware that this applies to a single line, thus requiring the line continuation symbol _ to keep larger statements readable.

Upvotes: 0

Binghang Li
Binghang Li

Reputation: 5

Try this, use err to get data table status information also, consider testing the data table on an inactive sheet.

Sub Test_TableNameExists()
    TableNm = "Table00"
    MsgOutput = TableNm & vbTab & TableNameExists(TableNm)
End Sub


Private Function TableNameExists(nname) As Boolean '#Table #Exist
'Returns TRUE if the data table name exists
'Can test table on inactive sheet
Dim x As Object
On Error Resume Next
'use Range(nname).Parent to get data table sheet name. 
'So the function can test data table on inactive sheet.

Set x = Range(nname).Parent.ListObjects(nname)
If Err = 0 Then TableNameExists = True _
    Else TableNameExists = False

End Function

Upvotes: 0

Luboš Suk
Luboš Suk

Reputation: 1546

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

Upvotes: 4

Vityata
Vityata

Reputation: 43585

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

Upvotes: 0

RGA
RGA

Reputation: 2607

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

Upvotes: 13

Rory
Rory

Reputation: 34045

Here is an alternative function:

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

Upvotes: 5

Related Questions