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

Question

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"

something like [this](http://www.jkp-ads.com/Articles/styles06.asp)? — , Mar 26 '14 at 11:42
Yes. But I would rather avoid that. I have no idea why MS choosed to localize style names in VBA. It do no such thing for Excel functions... :| — przemo_li, Mar 26 '14 at 11:55

Gary's Student · Accepted Answer · 2014-03-26T12:48:13.087

4

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:

colors

edited Mar 26 '14 at 12:48

answered Mar 26 '14 at 11:54

Gary's Student

95,722
10
59
99

Do You know how to get all the properties of some style? (So I can copy paste instead of reinventing wheel?) – przemo_li Mar 26 '14 at 12:10
Well answered here: http://www.excelforum.com/excel-general/399706-style-definitions-in-vba.html – Gary's Student Mar 26 '14 at 12:18
1

@przemo_li [**here**](http://s3.postimg.org/5kofnbt4j/Untitled.png) all properties of class Style, or type in `Dim s as Style` then `s.` and you should get the intelli-sense for all properties – Mar 26 '14 at 12:27
That about adding brand new styles. I just want to "copy" built-in ones. – przemo_li Mar 26 '14 at 12:28
@przemo_li are you asking how to iterate over all Style properties? You can't because Style does not implement IEnumerable. What you could do instead is [*`re-write the Style class and add an Enumarable`*](http://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba/19379641#19379641) – Mar 26 '14 at 12:28
Oh, never mind. :D I just wanted to save my time in setting all those properties manually (by copying styles somehow). But that wont work without referring to names... – przemo_li Mar 26 '14 at 12:35
PS How COLOR is encoded? How can I get numbers corresponding to colors? – przemo_li Mar 26 '14 at 12:41
Yeah. Thx. I just found out that RGB() can be used. – przemo_li Mar 26 '14 at 12:57

Dave Maff · Answer 2 · 2014-07-01T13:07:02.680

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

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

2 Answers2