Formatting Footers in VBA

In the UI, you have some formatting options when entering a custom footer (File > Page Setup > Header/Footer). You also have those options when creating a footer in VBA, you just have to know the codes to use.

Special codes like page numbers and dates can be found in the constant definition section of the code below. It’s an ampersand followed by a letter (A for the sheet name, who came up with that?).

For fonts and font sizes, the format is

ampersand + font name in double quotes + ampersand + font size

which is all optional, that is, you only need to include the name or the size if you want to change them. I know exactly two font names from memory, so if you’re like me the easiest way to set this up is to do it in the UI and check the LeftFooter, CenterFooter, and/or RightFooter properties of the PageSetup object. Of course, all this applies to headers as well.

Here’s an example that creates a formatted footer and uses some (all?) of the special codes.

Sub CreateFooters()
 
    Dim sLeft As String
    Dim sCenter As String
    Dim sRight As String
   
    Const sPAGE As String = “&P”
    Const sPAGES As String = “&N”
    Const sFILE As String = “&F”
    Const sSHEET As String = “&A”
    Const sDATE As String = “&D”
    Const sTIME As String = “&T”
   
    sLeft = sPAGE & ” of “ & sPAGES
   
    sCenter = “&”“Albertus Medium,Bold”“&11This&”“Arial,Regular”“&10 is formatted text”
   
    sRight = “[“ & sFILE & “]” & sSHEET & Chr$(10) & sDATE & ” “ & sTIME
   
    With Sheet1.PageSetup
        .LeftFooter = sLeft
        .CenterFooter = sCenter
        .RightFooter = sRight
    End With
   
End Sub

FormatFooter1

Posted in Uncategorized

10 thoughts on “Formatting Footers in VBA

  1. There are a few other codes for alignment, formatting etc., and a full list on the “Formatting codes for Headers and Footers” Help page.

  2. Be careful, though. Spot the bug in the following, which sets a 10pt footer:

    Public Sub SetLeftFooter(ByVal sFooter As String)

    ActiveSheet.PageSetup.LeftFooter = “&10? & sFooter

    End Sub

  3. If sFooter starts with a number, Excel thinks it’s part of the font size formatting, so you end up with a missing number and the rest of the footer in a very large font! The solution, of course, is to include space after the &10:

    ActiveSheet.PageSetup.LeftFooter = “&10 ? & sFooter

  4. You gave the answer before I read the question:) Cool, though, thanks for the pointing this out. I’ve never run into it before, but I can’t imagine how I would have debugged it if I did. That seems like a tricky one.

  5. First of all I’m not at all knowledgable about VBA code other than what I found on the internet today so please forgive me if I’m not explaining my question very well. I would like to format the date in the footer. If I write the following command &[Date] I get a date in the following format 4/23/2008. I would like the date to appear like this instead – 23APR08. Is this possible? Or am I asking too much?
    Thanks in advance.

  6. Is it possible to get the content of a cell in the footer?
    Each sheet has a title. We would like to print this title in the footer.

  7. Steve: You are able to get the contents of a cell in the footer. Here is an example of some code to take the contects of cell(1,1) and putting it in the footer:

    Sub CreateFooters()
    Sheet1.PageSetup.RightFooter = Sheet1.Cells(1, 1)
    End Sub

  8. Hi Jaime –

    It’s quite doable. In Dick’s code, replace the line

    Const sDATE As String = “&D”

    With

    Dim sDATE As String = Format(Date, “ddmmmyy”)

    …Michael


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *