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

Question

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

The names are localized and therefore useless for my application, which will be used by the version of Excel in different languages.

One UGLY solution that I can come up with is to keep a list of those styles applied to some cells in a hidden worksheet, and then check their names and use them for runtime ...

But there must be some simpler way, right? MS could not spoil such an important aspect of Excel.

PS Here are some examples of styles from registering macros:

Selection.Style = "Akcent 6"
Range("G4").Select
Selection.Style = "60% — akcent 6"
Range("G5").Select
Selection.Style = "Akcent 5"

Answer 1

, , . przemo1, przemo2 ..

:

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

# 1

COLOR s :

Answer 2

( ). , , , .

Public Sub Foo()
    localisedStyleName = FindLocalisedBuiltinStyleName("20% - Accent6")
End Sub

Public Function FindLocalisedBuiltinStyleName(EnglishStyleName) As String
    accentNumber = 0
    percentage = 0

    If Strings.Left(EnglishStyleName, 6) = "Accent" Then
        AccentNumber = CInt(Strings.Mid(EnglishStyleName, 7, 1))
    Else
       AccentNumber = CInt(Strings.Mid(EnglishStyleName, 13, 1))
       Percentage = CInt(Strings.Mid(EnglishStyleName, 1, 2))
    End If

    ThemeColorIndex = AccentNumber + 4
    FontToFind = 2

    Select Case Percentage
    Case 0
        FontToFind = 1
        TintAndShadeToFind = 0
    Case 20
        TintAndShadeToFind = 0.799981688894314
    Case 40
        TintAndShadeToFind = 0.599993896298105
    Case 60
        FontToFind = 1
        TintAndShadeToFind = 0.399975585192419
    End Select

    For i = 1 To ActiveWorkbook.Styles.Count
        ThemeColor = ActiveWorkbook.Styles.Item(i).Interior.ThemeColor
        TintAndShade = ActiveWorkbook.Styles.Item(i).Interior.TintAndShade
        Font = ActiveWorkbook.Styles.Item(i).Font.ThemeColor

        If ThemeColor = ThemeColorIndex And Abs(TintAndShade - TintAndShadeToFind) < 0.001 And Font = FontToFind Then
            FindLocalisedBuiltinStyleName = ActiveWorkbook.Styles.Item(i).NameLocal
            Exit Function
        End If
    Next

    FindLocalisedBuiltinStyleName = ""

End Function