CODEWITHSUNDEEP
vbaexcel-2010
Boris Karl Schlein
Boris Karl Schlein

Reputation: 23

Excel 2010 VBA: find out if formatting style exists

I want to check if an formatting style exists in an Excel2010 document.

On Error Resume Next

Private Function checkStyleName(ByVal strStyleName As String) As Boolean
    Dim objGivenStyle As Excel.Style

    Set objGivenStyle = ActiveDocument.Styles(strStyleName) ' try to get formatting style out of list
    If objGivenStyle Is Nothing Then
        ' do something
        checkStyleName = False
    Else
        checkStyleName = True
    End If
End Function

Problem is, Set objGivenStlye = ActiveDocument.Styles(strStyleName) doesn't work at all. What is the best solution - maybe a loop over all existing styles?

THX BKS

Upvotes: 2

Views: 4816

Answers (2)

ChrisB
ChrisB

Reputation: 3205

The existing answer didn't work for me. Here's a function that does work. It checks if the style name being tested exists and returns either TRUE or FALSE.

Public Function StyleExists(ByVal styleName As String, ByVal target As Workbook) As Boolean
' Returns TRUE if the named style exists in the target workbook.
    On Error Resume Next
    StyleExists = Len(target.Styles(styleName).Name) > 0
    On Error GoTo 0
End Function

Here's an example use of the function:

Sub test()
    MsgBox StyleExists("Normal", Activeworkbook) ' Should be TRUE.
    MsgBox StyleExists("Unusual", Activeworkbook) ' Should be FALSE unless custom style 'Unusual' is in the active workbook.
End Sub

Upvotes: 3

Janthelme
Janthelme

Reputation: 999

This should work :

'case sensitive

Private Function checkStyleName(ByVal strStyleName As String) As Boolean
    Dim sCheck As String
    Dim wkb As Workbook

    checkStyleName = False
    Set wkb = ActiveWorkbook

    sCheck = "x" & strStyleName ' makes the two strings different
    On Error Resume Next
        sCheck = wkb.Styles(strStyleName).Name
    On Error GoTo 0

    If sCheck = strStyleName Then checkStyleName = True
End Function

Upvotes: 1

Related Questions