Class Style (Excel VBA)

The class Style represents a style description for a range.

The main procedures of class Style are Delete and Styles.Add

Set

To use a Style class variable it first needs to be instantiated, for example

Dim strName As String: strName = 
Dim wb As Workbook: Set wb = 
Dim sty As Style
Set sty = wb.Styles.Add(Name:=strName)

The following procedures can be used to set variables of type Style: DisplayFormat.Style, Range.Style, Slicer.Style, Styles.Item, Styles.Add and Workbook.Styles

For Each

Here is an example of processing the Style items in a collection.

Dim wb As Workbook: Set wb = 
Dim styStyle As Style
For Each styStyle In wb.Styles
	
Next styStyle

Methods

These are the main methods of the Style class

Delete - Deletes the object.

Styles.Add - Creates a new style and adds it to the list of styles that are available for the current workbook.

With ActiveWorkbook.Styles.Add("theNewStyle") 
 .IncludeNumber = False 
 .IncludeFont = True 
 .IncludeAlignment = False 
 .IncludeBorder = False 
 .IncludePatterns = False 
 .IncludeProtection = False 
 .Font.Name = "Arial" 
 .Font.Size = 18 
End With

Properties

AddIndent returns or sets a Boolean value that indicates if text is automatically indented when the text alignment in a cell is set to equal distribution (either horizontally or vertically).

Borders returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).

Sub SetRangeBorder() 
 
 With Worksheets("Sheet1").Range("B2").Borders(xlEdgeBottom) 
 .LineStyle = xlContinuous 
 .Weight = xlThin 
 .ColorIndex = 3 
 End With 
 
End Sub

BuiltIn true if the style is a built-in style.

Font returns a Font object that represents the font of the specified object.

FormulaHidden returns or sets a Boolean value that indicates if the formula will be hidden when the worksheet is protected.

HorizontalAlignment returns or sets an XlHAlign value that represents the horizontal alignment for the specified object.

IncludeAlignment true if the style includes the AddIndent, HorizontalAlignment, VerticalAlignment, WrapText, IndentLevel, and Orientation properties of the Style object.

Worksheets("Sheet1").Range("A1").Style.IncludeAlignment = True

IncludeBorder true if the style includes the Color, ColorIndex, LineStyle, and Weight properties of the Border object.

Worksheets("Sheet1").Range("A1").Style.IncludeBorder = True

IncludeFont true if the style includes the Background, Bold, Color, ColorIndex, FontStyle, Italic, Name, Size, Strikethrough, Subscript, Superscript, and Underline font properties.

Worksheets("Sheet1").Range("A1").Style.IncludeFont = True

IncludeNumber true if the style includes the NumberFormat property.

Worksheets("Sheet1").Range("A1").Style.IncludeNumber = True

IncludePatterns true if the style includes the Color, ColorIndex, InvertIfNegative, Pattern, PatternColor, and PatternColorIndex properties of the Interior object.

Worksheets("Sheet1").Range("A1").Style.IncludePatterns = True

IncludeProtection true if the style includes the FormulaHidden and Locked protection properties.

Worksheets("Sheet1").Range("A1").Style.IncludeProtection = True

IndentLevel returns or sets a Long value that represents the indent level for the style.

Interior returns an Interior object that represents the interior of the specified object.

Locked returns or sets a Boolean value that indicates if the object is locked.

MergeCells true if the style contains merged cells.

Name returns a String value that represents the name of the object.

With ActiveWorkbook.Styles(1) 
 MsgBox "The name of the style: " & .Name 
 MsgBox "The localized name of the style: " & .NameLocal 
End With

NameLocal returns or sets the name of the object, in the language of the user.

With ActiveWorkbook.Styles(1) 
 MsgBox "The name of the style is " & .Name 
 MsgBox "The localized name of the style is " & .NameLocal 
End With

NumberFormat returns or sets a String value that represents the format code for the object.

NumberFormatLocal returns or sets a String value that represents the format code for the object as a string in the language of the user.

Orientation returns or sets an XlOrientation value that represents the text orientation.

Parent returns the parent object for the specified object. Read-only.

ReadingOrder returns or sets the reading order for the specified object. Can be one of the following XlReadingOrder constants: xlRTL (right-to-left), xlLTR (left-to-right), or xlContext.

ShrinkToFit returns or sets a Boolean value that indicates if text automatically shrinks to fit in the available column width.

Value returns a String value that represents the name of the specified style.

VerticalAlignment returns or sets an XlVAlign value that represents the vertical alignment of the specified object.

WrapText returns or sets a Boolean value that indicates if Microsoft Excel wraps the text in the object.

Styles.Count returns a Long value that represents the number of objects in the collection.

Styles.Item returns a single Style object from the collection.

ActiveWorkbook.Styles.Item("Normal").Font.Bold = True

Styles.Parent returns the parent object for the specified object. Read-only.