VBA、Excel 如何在不使用名称的情况下设置特定样式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22659675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA, Excel how to set particular styles without using their names?
提问by przemo_li
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 UGLYsolution 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.
但一定有更简单的方法，对吧？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"
采纳答案by Gary's Student
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和相关索引：
回答by Dave Maff
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
