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

Dubois

just loop table/shape colection colection and check if any table/shape has desired name. – Microcline

How do I do that? How do I loop a ListObject? – Dubois

Why is this question voted down with "-2" ? – Dubois

@Dubois its common practice on SO to downvote questions that dont abide by the community guidelines for asking questions -stackoverflow.com/help/how-to-ask. In your case, the lack of attempted code has resulted in some people downvoting your question. I still chose to answer because the solution was quite short, but that is the cause of the downvotes – Slaphappy

Answer 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

Slaphappy

For very large data sets, this method will be much faster than looping through every shape on the sheet and will return exactly the same result – Slaphappy

Getting run-time error for this. Its caused because i dont have shape Table123 on sheet, only table. So you need to change it to ListObjects and also when table isnt on sheet, you will get error. Not false – Microcline

It is erroring out in this line "If Not ActiveSheet.Shapes.Range(Array("Table123")) Is Nothing Then" saying "The item with the specified name wasn't found" – Dubois

It should not error out for me also that time that table was present. – Dubois

error is Run-time error 1004 The item with the specified name wasnt found. (and i updated my previous coment) – Microcline

@Dubois see the editted code. this should work as desired – Slaphappy

RGA: again in the IF statement, I get "Run-time error '9' Subscript out of range" – Dubois

@Dubois I have no idea why you got an error, as the errorhandler should avoid that, so I've adjusted the errorhandler. I've tested it in a variety of ways on my machine and it works perfectly, so if you are still having issues with this then it is the result of something else – Slaphappy

@RGA: Actually I tried the code in empty sheet to check if it returns false. I will test and let you know. So far it did not work for me. Thanks. – Dubois

@RGA: The code works fine, unless there are 0 ListObjects. If there is some object in the worksheet then it works fine. I added another If statement to check if there are any List Objects using "If ActiveSheet.ListObjects.Count > 3 Then". Then it worked fine. Thanks a lot. – Dubois

@Dubois glad it worked, though I'm not sure why it didn't work if there were 0 objects. I tested it on a completely blank sheet that was freshly opened and had no issues. Will do a little research to figure out what that cause would be – Slaphappy

@RGA: Thank you :) – Dubois

Answer 2

Here is an alternative function:

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

Naturalism

This will return true for Names listed in the Name Manager and any strings that are Cell references. Since not all Names are tables, it might be better to use structured reference. i.e. "ISREF(" & sTableName & "[[#All]])" should evaluate false on non-table references. **I'm not sure if there are any edge cases. – Garratt

Answer 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

Start

Answer 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

Softpedal

Answer 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

Rattlebrained

Answer 6

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.

Dellora