VBA Excel error handling when checking if a particular table exists using table name

Question

There is a thread which almost gives me exactly what i need when checking a sheet for a table using the table name. This is here... VBA Excel check if a particular table exist using table name

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

If the table does not exist it goes straight to the error handler, this is fine however i have other code in the function that when errors would end up using the same error handler. Because of this i cannot display a specific msgbox stating that the table does not exist.

Is there a way to display a msgbox if the table does not exist, one that doesn't use the same error handler as other parts of the function.

Santosh · Accepted Answer · 2018-07-17 16:31:22Z

3

You can create a separate function which can check table exists and has nothing to do with a main routine error handler.

Function tableExist(Sht As Worksheet, tblName As String) As Boolean
    On Error Resume Next
    tableExist = Sht.ListObjects(tblName).Name = tblName
    On Error GoTo 0
End Function

Sub test()

    If tableExist(ActiveSheet, "Table1234") Then

    ' write your code here

    End If
End Sub

answered Jul 17, 2018 at 16:31

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geminiflipflop Over a year ago

Thank you. That has helped me

Vityata · Accepted Answer · 2018-07-17 16:31:20Z

0

The TableExists function returns a boolean value, indicating whether the table exists. Based on this, a MsgBox could be shown with some relevant info:

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

answered Jul 17, 2018 at 16:31

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

VBA Excel error handling when checking if a particular table exists using table name

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related