The Styles Collection and the Style Object

A Style object represents a set of formatting options for a range. Each workbook has a Styles collection containing all Style objects for the workbook.

Tea207 ly®

To apply a style to a range, we simply write:

RangeObject.Style = StyleName where StyleName is the name of a style.

To create a Style object, use the Add method, whose syntax is:

WorkbookObject.Add(Name, BasedOn)

Note that the Add method returns the newly created Style object.

The Name parameter specifies the name of the style, and the optional BasedOn parameter specifies a Range object that refers to a cell whose style is used as a basis for the new style. If this argument is omitted, the newly created style is based on the Normal style.

Note that, according to the documentation, if a style with the specified name already exists, the Add method will redefine the existing style based on the cell specified in BasedOn. (However, on my system, Excel issues an error message instead, so you should check this carefully.)

The properties of the Style object reflect the various formatting features, such as font name, font size, number format, alignment, and so on. There are also several built-in styles, such as Normal, Currency, and Percent. These built-in styles can be found in the Style name box of the Style dialog box (under the Format menu).

To illustrate, the following code creates a style and then applies it to an arbitrary range of the current worksheet:

Dim st As Style

' Delete style if it exists

For Each st In ActiveWorkbook.Styles

If st.Name = "Bordered" Then st.Delete Next

' Create style

With ActiveWorkbook.Styles.Add(Name:="Bordered") .Borders(xlTop).LineStyle = xlDouble .Borders(xlBottom).LineStyle = xlDouble .Borders(xlLeft).LineStyle = xlDouble .Borders(xlRight).LineStyle = xlDouble .Font.Bold = True .Font.Name = "arial" .Font.Size = 36 End With

' Apply style

Application.ActiveSheet.Range("A1:B3").Style = "Bordered"

0 0


  • Mariam Teodros
    How do I assign a name to a "style object" in vba?
    7 years ago

Post a comment