Styles in Excel

VBA examples and tools

The little VBA routines shown below will greatly easy your work with styles. As an important side effect, these also show you how the style object works in VBA.

Find cells with a certain style

This routine find cells with a style containing "demo" in its name:

Sub FindaStyle()
    Dim oSh As Worksheet
    Dim oCell As Range
    For Each oSh In ThisWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like "*demo*" Then
                Application.GoTo oCell
                Stop
            End If
        Next
    Next
End Sub

As soon as a cell is encountered with a style that matches that name filter, the code stops (Stop) and you can check out the cell in detail.

Creating a list of styles

This sub adds a table of your styles on a worksheet named "Config - Styles":

Sub ListStyles()
    Dim oSt As Style
    Dim oCell As Range
    Dim lCount As Long
    Dim oStylesh As Worksheet
    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")
    With oStylesh
        lCount = oStylesh.UsedRange.Rows.Count + 1
        For Each oSt In ThisWorkbook.Styles
            On Error Resume Next
            Set oCell = Nothing
            Set oCell = Intersect(oStylesh.UsedRange, oStylesh.Range("A:A")).Find(oSt.Name, _
                oStylesh.Range("A1"), xlValues, xlWhole, , , False)
            If oCell Is Nothing Then
            lCount = lCount + 1
            .Cells(lCount, 1).Style = oSt.Name
            .Cells(lCount, 1).Value = oSt.NameLocal
            .Cells(lCount, 2).Style = oSt.Name
            End If
        Next
    End With
End Sub

Clear all formatting of cells and re-apply their styles

The code below removes all formatting of all cells and subsequently re-applies their style to them.

Watch out: if you have not adhered to using styles strictly, you will loose all formatting in your file!!!

Sub ReApplyStyles()
'Resets styles of cells to their original style (resets all formatting done on top of ANY style)
    Dim oCell As Range
    Dim oSh As Worksheet
    If MsgBox("Proceed with care:" & vbNewLine & vbNewLine & _
              "This routine will erase all formatting done on top of the existing cell styles." & vbNewLine & _
              "Continue?", vbCritical + vbOKCancel + vbDefaultButton2, GSAPPNAME) = vbOK Then
        For Each oSh In ActiveWindow.SelectedSheets
            For Each oCell In oSh.UsedRange.Cells
                If oCell.MergeArea.Cells.Count = 1 Then
                    oCell.Style = CStr(oCell.Style)
                End If
            Next
        Next
    End If
End Sub

Replace one style with another

The code below uses a list with two columns. The column on the left contains the names of existing styles. The column to its immediate right contains the names of the style you want to replace them with.

The code will run through the selected cells in the left column and check if the style name in the column to its right differs. If so, it will prompt you with the alternative name. Clicking OK will cause the code to update ALL cells to which the old style was applied to the new style. Before running this sub you need to select the cells in the left hand column.

Sub FixStyles()
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Replaces styles with the replacement style as defined by a two column list.
'             column 1 should contain the existing style, col 2 the replacing style
'-------------------------------------------------------------------------
    Dim sOldSt As String
    Dim sNewSt As String
    Dim oSh As Worksheet
    Dim oCell As Range
    Dim oSourceCell As Range
    Set oSourceCell = ActiveCell
    While oSourceCell.Value <> ""
        sOldSt = oSourceCell.Value
        sNewSt = InputBox("Please enter replacement style for:" & sOldSt, "Style changer", oSourceCell.Offset(, 1).Value)
        If sNewSt = "" Then Exit Sub
        If sNewSt <> "" And sNewSt <> sOldSt Then
            For Each oSh In ThisWorkbook.Worksheets
                For Each oCell In oSh.UsedRange
                    If oCell.Style = sOldSt Then
                        Application.GoTo oCell
                        On Error Resume Next
                        oCell.Style = sNewSt
                    End If
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

Comments

Showing last 8 comments of 32 in total (Show All Comments):

Comment by: Jan Karel Pieterse (5/1/2011 9:45:03 PM)

Hi Siddhant,

Not sure what you mean by serialise? In any case, you'll have to do each of the Style object's properties one-by-one, listing everyone in your code. You can find a tool called ObjectLister on my download page which helps getting all properties of an object.

Comment by: Siddhant Chothe (5/6/2011 6:13:03 AM)

By serialise I mean representing the entire object in form of string. Just like JSON. For example assume a style object as:-

oStyle.IncludeFont=True
oStyle.Font.Size=15 ' don't recall exact attribute
oStyle.Font.Face="Times New Roman" ' here again don't recall exact attribute.
But, the main point is that I want to be able to read such oStyle object at run-time and formulate something like:-

strStyle="{IncludeFont:True,Font{Size:15,Face:Times New Roman}}"
and so on.

Comment by: Jan Karel Pieterse (5/6/2011 7:26:11 AM)

Hi Siddhant,

It can be done, but you'll have to have each property you want to serialize in a list somewhere.

You can write generic code by using the VBA CallByName function, which accepts the Object in question as an object pointer and can use a string argument (and the value in question) to set which property/method to set/get/let.

Comment by: Alfred Vachris (5/9/2011 6:56:08 AM)

I have run across many workbooks with styles that can't be deleted. Have you explored a VBA process to remove them by editing styles.xml?
Regards
Alfred

Comment by: Jan Karel Pieterse (5/9/2011 11:45:33 PM)

Hi Alfred,

Not directly, but you might use the code offered here to open the styles xml inside an xlsx/xlsm file:

http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp

Comment by: XLGeek (5/16/2011 11:01:03 PM)

Alfred, you are correct: if you are running into a situation where you can't delete custom styles via VBA or Excel OM then the only way to delete them is by altering the contents of styles.xml. Although it can be done VBA is not the best option for this. Here is a link to the thread on this subject that might help: http://social.msdn.microsoft.com/Forums/en-ZA/exceldev/thread/446f388f-5eae-4799-ac8e-84330a6a5d4d

Comment by: Alfred Vachris (8/11/2011 5:44:43 AM)

Jan Thank you very much for your code:
http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp
Using that as a base I have been able to build a process that rapidly reduces all cellStyles and cellStyleXfs collection to a single Normal style. I have expanded the process into a batch process can be applied to a collection of 2007 or 2010 workbooks.
Thanks Again
Alfred

Comment by: Alfred Vachris (8/12/2011 7:03:11 AM)

FYI - Workbooks saved as XLSB have .bin elements and not .xlm. So the neat tools here don't apply.
Regards
Alfred

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.