Deleting TableStyle from excel

Question

I have added a TableStyle to excel workbook using the statement:

ActiveWorkbook.TableStyles.Add("PivotTable SS")

I can delete it using:

ActiveWorkbook.TableStyles("PivotTable SS").Delete

How can I programmatically check if it already exists before deciding whether to delete or not?

Currently I am looping through all the table styles and doing a selective delete:

    For Each ts In ActiveWorkbook.TableStyles
        If ts.Name = "PivotTable Style 1" Then
            ts.Delete
        End If
    Next ts

However, this is time-consuming. How can I just check for the pivot table existence and delete it without looping?

Thanks :)

Answer 1

You can try assigning the style to a variable. If the variable is Nothing, then the style does not exist. If the style does not exist and you try to assign the variable, you will get an error message, so you need to temporarily suspend the error handling.

Sub DeleteAStyle()

    Dim ts As TableStyle

    On Error Resume Next
    Set ts = ActiveWorkbook.TableStyles("PivotTable Style 1")
    On Error GoTo MyUsualErrorHandler

    If Not ts Is Nothing Then
        ts.Delete
    End If

End Sub