VBA, Excel how to set particular styles without using their names?

excel, formatting, user-interface, vba

Solution

For the purpose of establishing Styles in the workbooks you distribute, you can create your own Styles and assign names to them. For example przemo1, przemo2, etc.

For example:

Sub MakeAStyle()
    ActiveWorkbook.Styles.Add Name:="PrZemo1"
    With ActiveWorkbook.Styles("PrZemo1")
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
    End With
    With ActiveWorkbook.Styles("PrZemo1").Font
        .Name = "Arial Narrow"
        .Size = 11
        .Bold = False
        .Italic = False
        .Underline = xlUnderlineStyleNone
        .Strikethrough = False
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With ActiveWorkbook.Styles("PrZemo1")
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
    End With
End Sub

EDIT#1

Here are some COLORs and associated indexes:

Problem

VBA, Excel how to set particular styles without using their names? Names are localized and hence useless for my app which will be used by different language Excel version. One UGLY solution I can think off, is to keep list of those styles applied to some cells on hidden sheet, and then check their names and use them on the run time.... But there must be some easier way, right? MS could not botched so important aspect of Excel. PS Here are some exemplary styles from registering macros: ``` Selection.Style = "Akcent 6" Range("G4").Select Selection.Style = "60% — akcent 6" Range("G5").Select Selection.Style = "Akcent 5" ```

Original source

Related problems