VBA，Excel如何在不使用其名称的情况下设置特定样式？

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

VBA，Excel如何在不使用其名称的情况下设置特定样式？

Names are localized and hence useless for my app which will be used by different language Excel version.

名称是本地化的，因此我的应用程序无用，将由不同语言的Excel版本使用。

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....

我能想到的一个UGLY解决方案是将这些样式的列表保存到隐藏表单上的某些单元格中，然后检查它们的名称并在运行时使用它们....

But there must be some easier way, right? MS could not botched so important aspect of Excel.

但必须有一些更简单的方法，对吗？ MS不能破坏Excel这么重要的方面。

PS Here are some exemplary styles from registering macros:

PS以下是注册宏的一些示例性样式：

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

2 个解决方案

#1

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.

为了在您分发的工作簿中建立样式，您可以创建自己的样式并为其指定名称。例如przemo1，przemo2等。

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

编辑＃1

Here are some COLORs and associated indexes:

以下是一些COLOR和相关索引：

#2

The code here can be used to find the localised version of a built-in style name (using its English version name). It isn't efficient but it probably doesn't need to be.

此处的代码可用于查找内置样式名称的本地化版本（使用其英文版本名称）。它效率不高但可能不需要。

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