Excel - Additive styles (Excel 2000)

[ChrisGreaves]

I have been playing around with Excel styles in the additive/cumulative sense, meaning that one can have a style that includes just Border attributes, another style that includes just Patterns attributes, a third that includes just Number attributes, and that these styles can be used independently, or can be combined, layered if you will, to produce a composite formatting effect.

I have found that Excel 2000 does not allow the end-user to specify a single atomic attribute; one cannot have a style "Bold" which sets just the Bold attribute without disturbing the Font, Font size and other attributes of the style. Indeed, Excel 2000 protests to the end-user if the end-user deletes the Font (name) and Font size from the text boxes.

So I turned to VBA, and have discovered that the restrictions appear to be insoluble.

The macro below, edited from a recorded macro, attempts to define a style that is solely a font size of 24 points.
Applying the style defined by the macro causes my cell contents, previously styled "Courier" to revert to "Arial", even though the Arial font (name) is disabled in the macro that defines style "Size24".

Code: Select all

Sub AddStyleSize24()
    ActiveWorkbook.Styles.Add Name:="Size24"
    With ActiveWorkbook.Styles("Size24")
        .IncludeNumber = False
        .IncludeFont = True
        .IncludeAlignment = False
        .IncludeBorder = False
        .IncludePatterns = False
        .IncludeProtection = False
    End With
    With ActiveWorkbook.Styles("Size24").Font
'        .Name = "Arial"
        .Size = 24
'        .Bold = True
'        .Italic = True
'        .Underline = xlUnderlineStyleNone
'        .Strikethrough = False
'        .ColorIndex = xlAutomatic
    End With
'    Selection.Style = "Size24"
End Sub

I seek confirmation that in Excel 2000 (and possibly in other versions), the independence of style characteristics is confined to each of the tabs (Number, Font, Patterns etc.) and is indeed always inclusive of every characteristic within that tab.

[HansV]

You are correct - it's the same in Excel 2002, 2003 and 2007.

From the Excel 2007 VBA help:

Style.IncludeFont Property

True if the style includes the Background, Bold, Color, ColorIndex, FontStyle, Italic, Name, Size, Strikethrough, Subscript, Superscript, and Underline font properties. Read/write Boolean.

Note the use of "and".

[ChrisGreaves]

... it's the same in Excel 2002, 2003 and 2007.

Hans, thanks for the quick confirmation.
It quite got my hopes up when I saw the recorded VBA (sniff!) .....

[ChrisGreaves]

I have been playing around with Excel styles ...

Furthermore, although one can apply several styles to a cell, e.g. apply a user-defined style "UserInput" with a Pattern "pale yellow " and Protection Locked=OFF, and then apply a user-defined style "Number3" with a number format set to 3 decimal places (and the Protection and Patterns tabs disabled), then a third style "BorderBlue", with just the Border tab active, and so on, the cell seems to retain the name of the last-applied style only.
That is, if you enquire in VBA "?selection.style.name" you would be told "BorderBlue" from the example above.
This implies that one cannot do a decent analysis of what styles have been used in a worksheet/workbook.

Furthermore, the last-used style takes precedence, thus, with TWO styles UI1 and UI2, both meant for user-input, the first with a pale-yellow Pattern and Protection Locked=OFF, and the second with a pale-blue Pattern and Protection Locked =ON, applying UI1 then UI2 to a cell (and then protecting the sheet) will result in a cell which is locked, whereas applying UI2 then UI1 will result in a cell which is UNlocked.
The latest attribute to be used dominates.

I can't believe it has taken me so long to stumble on this because I seem to have spent a great deal of time fixing legacy workbooks.
Maybe most users just aren't using user-defined styles as much as I had thought.
(They aren't using range names either, but I seem to recall that DOS Lotus 2.1 has range names back in '88 or thereabouts)

[Jan Karel Pieterse]

Styles are heavily under-used, which makes sense, because it isn't easy by a looong stretch.
I have written a series of articles on Excel styles maybe it helps?.

[HansV]

Furthermore, the last-used style takes precedence, thus, with TWO styles UI1 and UI2, both meant for user-input, the first with a pale-yellow Pattern and Protection Locked=OFF, and the second with a pale-blue Pattern and Protection Locked =ON, applying UI1 then UI2 to a cell (and then protecting the sheet) will result in a cell which is locked, whereas applying UI2 then UI1 will result in a cell which is UNlocked.
The latest attribute to be used dominates.

This seems reasonable to me - how could it be otherwise?

[ChrisGreaves]

... a series of articles on Excel styles... maybe it helps?.

Jan karel.
Just where do you think I get my inspiration? (grin!)
I am writing up my findings (Work In progress) at Excel Styles and the business of dealing with styles (and range names) is a significant portion of my STAIN utility.

[ChrisGreaves]

This seems reasonable to me - how could it be otherwise?

Well, Hans, you know me ... never satisfied :grumpy:
I would have liked a Collection of styles applied/de-applied as a chronological stack.
Then I could do a thorough analysis and roll-back of formatting applied via styles.
Just think of it - every cell with it's own set of time-stamped style names!

I think it a pity that one cannot apply an individual attribute, as one can in Word.
Word let's me define a character style ("cdBold") that has only the Bold attribute set, and affects no other characteristic of the selection. In Excel terms, I wouldn't be able to define a "csBold" style without affecting the Font, size, color etc. at the same time.

[HansV]

But even in Word, you can only apply one paragraph style and one character style to a range, and Selection.Style will return only one Style - the paragraph style if no (single) character style has been applied, and the character style otherwise. If the selection includes different paragraph styles, Selection.Style will throw an error.

[ChrisGreaves]

But even in Word, you can only apply one paragraph style and one character style to a range,

Hans you are correct.
I'm getting confused here between two needs.
(1) Ability to stack multiple style applications on a range and
(2) Ability to finesse attributes.

My comment on Word character styles was related to (2).
:INeedMoreSleep: