Check if named tables exist VBA Excel 2007

Question

I am trying to determine if a table exists, using VBA Excel 2007, and if it exists then delete it.

I am looping through an array of table names.

My code is below:

' Allocate
Dim lIndex                                      As Long

' Allocate table header values in array
Dim sTableNames(1 To Constants.lNumTables)      As String

' Populate array
sTableNames(1) = Constants.sTableNameKpiAllIncidents
sTableNames(2) = Constants.sTableNameSlaAllManualHelpdeskIncidents
sTableNames(3) = Constants.sTableNameSlaAllManualIncidents
sTableNames(4) = Constants.sTableNameKpiAllAutomaticIncidents

' Work in worksheet Statistics
With Worksheets(Constants.sSheetNameStatistics)

    ' Loop through all tables
    For lIndex = 1 To UBound(sTableNames)

        ' Check if table already exists
        If Not .ListObjects(sTableNames(lIndex)) Is Nothing Then

            ' Delete table
            .ListObjects(sTableNames(lIndex)).Delete

        End If

    Next

End With

My code works as long as these tables exist in my worksheet. I have also tried replacing the line

If Not .ListObjects(sTableNames(lIndex)) Is Nothing Then

with the line

 If .ListObjects(sTableNames(lIndex)).Count > 0 Then

but it still doesn't work.

Does anybody know a way to get this to work?

Any help would be appreciated.

What error do you get? The 'dirty' quick fix would be to add On Error Resume Next before the ....Delete line. — nicolaus-hee
– nicolaus-hee, Commented Apr 14, 2016 at 16:41
It makes Excel not interrupt code execution in case it encounters an error. This is 'dirty' because all errors appear for a reason (in your case: the macro is trying to delete something that doesn't exist) and by jumping over it you may overlook other bugs. — nicolaus-hee
– nicolaus-hee, Commented Apr 14, 2016 at 16:50
Thank you! I've inserted the line before If Not .ListObjects(sTableNames(lIndex)) Is Nothing Then and it worked! — lala_12
– lala_12, Commented Apr 14, 2016 at 16:51

brettdj · Accepted Answer · 2016-04-15 02:14:09Z

5

Error handling as nhee suggests is the right approach.

As a UDF the suggestion above would be quicker with:

Function TableExists(ws As Worksheet, tblNam As String) As Boolean
Dim oTbl As ListObject
On Error Resume Next
Set oTbl = ws.ListObjects(tblNam)
TableExists = Not oTbl Is Nothing
End Function

answered Apr 15, 2016 at 2:14

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

Comments

Jeanno · Accepted Answer · 2016-04-14 16:50:31Z

2

The following UDF will return a boolean if a table exists

  Function TableExists(ws As Worksheet, tblNam As String) As Boolean
    Dim oTbl As ListObject
      For Each oTbl In ws.ListObjects
        If oTbl.Name = tblNam Then
            TableExists = True
            Exit Function
        End If
      Next oTbl
      TableExists = False
    End Function

answered Apr 14, 2016 at 16:50

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

Check if named tables exist VBA Excel 2007

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related