Formatting and VBA Codes for Headers and Footers

Last modified: June 30, 2011

Applies to: Excel 2013 | Office 2013 | VBA

The following special formatting and Visual Basic for Applications (VBA) codes can be included as a part of the header and footer properties (LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter).

Format code

Description

&L

Left aligns the characters that follow.

&C

Centers the characters that follow.

&R

Right aligns the characters that follow.

&E

Turns double-underline printing on or off.

&X

Turns superscript printing on or off.

&Y

Turns subscript printing on or off.

&B

Turns bold printing on or off.

&I

Turns italic printing on or off.

&U

Turns underline printing on or off.

&S

Turns strikethrough printing on or off.

&"fontname"

Prints the characters that follow in the specified font. Be sure to include the double quotation marks.

&nn

Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.

&color

Prints the characters in the specified color. User supplies a hexidecimal color value.

&"+"

Prints the characters that follow in the Heading font of the current theme. Be sure to include the double quotation marks.

&"-"

Prints the characters that follow in the Body font of the current theme. Be sure to include the double quotation marks.

&Kxx.Syyy

Prints the characters that follow in the specified color from the current theme. xx is a two-digit number from 1 to 12 that specifies the theme color to use. Snnn specifies the shade (tint) of that theme color. Specify S as + to produce a lighter shade; specify S as - to produce a darker shade. nnn is a three-digit whole number that specifies a percentage from 0 to 100.
If the values that specify the theme color or shade are not within the described limits, Excel will use the nearest valid value.

VBA code

Description

&D

Prints the current date.

&T

Prints the current time.

&F

Prints the name of the document.

&A

Prints the name of the workbook tab.

&P

Prints the page number.

&P+number

Prints the page number plus the specified number.

&P-number

Prints the page number minus the specified number.

&&

Prints a single ampersand.

&N

Prints the total number of pages in the document.

&Z

Prints the file path.

&G

Inserts an image.

The following code shows how formatting and VBA codes can be used to modify the header information and appearance.

Sub Date_Time() 
 ActiveSheet.PageSetup.CenterHeader = "&D &B&ITime:&I&B&T" 
End Sub
Show:
© 2015 Microsoft