Editing properties in the VBA PageSetup object is notoriously slow because the object sends a call to the print drivers each time it wants to update a PageSetup property. Even if you disable events and screen updating, PageSetup is still slow. It’s not until you set Application.PrintCommunication = False before you start noticing considerable improvements in performance..

You don’t have to use the slow PageSetup method, though. There is another way to update your page setup properties without relying on any of the sluggish Worksheet.PageSetup statements. This alternate solution lies in a really old version of Excel…

The Excel 4.0 compatible macro method, Application.ExecuteExcel4Macro, has a PAGE.SETUP function that is much faster than changing properties using the slow Worksheet.PageSetup object. The speed differences aren’t as significant if you disable the time-wasting events, which we’ll talk about below, but it’s still a bit faster. This tutorial will help you if you’re searching for ways to speed up your VBA PageSetup statements and nothing you’ve tried has worked.

VBA PAGE.SETUP Routine

Private Sub PageSetupXL4M( _
        Optional LeftHead As String, Optional CenterHead As String, Optional RightHead As String, Optional LeftFoot As String, _
        Optional CenterFoot As String, Optional RightFoot As String, Optional LeftMarginInches As String, Optional RightMarginInches As String, _
        Optional TopMarginInches As String, Optional BottomMarginInches As String, Optional HeaderMarginInches As String, Optional FooterMarginInches As String, _
        Optional PrintHeadings As String, Optional PrintGridlines As String, Optional PrintComments As String, Optional PrintQuality As String, _
        Optional CenterHorizontally As String, Optional CenterVertically As String, Optional Orientation As String, Optional Draft As String, _
        Optional PaperSize As String, Optional FirstPageNumber As String, Optional Order As String, Optional BlackAndWhite As String, _
        Optional Zoom As String)
     Const c As String = ","
     Dim pgSetup As String
     Dim head As String
     Dim foot As String

     If LeftHead <> "" Then head = "&L" & LeftHead
     If CenterHead <> "" Then head = head & "&C" & CenterHead
     If RightHead <> "" Then head = head & "&R" & RightHead
     If Not head = "" Then head = """" & head & """"
     If LeftFoot <> "" Then foot = "&L" & LeftFoot
     If CenterFoot <> "" Then foot = foot & "&C" & CenterFoot
     If RightFoot <> "" Then foot = foot & "&R" & RightFoot
     If Not foot = "" Then foot = """" & foot & """"
       
     pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
       LeftMarginInches & c & RightMarginInches & c & _
       TopMarginInches & c & BottomMarginInches & c & _
       PrintHeadings & c & PrintGridlines & c & _
       CenterHorizontally & c & CenterVertically & c & _
       Orientation & c & PaperSize & c & Zoom & c & _
       FirstPageNumber & c & Order & c & BlackAndWhite & c & _
       PrintQuality & c & HeaderMarginInches & c & _
       FooterMarginInches & c & PrintComments & c & Draft & ")"

     Application.ExecuteExcel4Macro pgSetup
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.

Sure, I'll take a free VBA Developer Kit

How to Call PageSetupXL4M

I first came across this subroutine in an article from 2004. It’s a great VBA routine but without an explanation for how to use it, it can be a bit intimidating. The routine organizes each argument into the old Excel 4.0 PAGE.SETUP function style. The PAGE.SETUP function in Excel 4.0 is complicated and requires the arguments be entered as one massive string organized in a specific manner. The syntax varies based on whether you’re adjusting a worksheet or a chart sheet, but we’re going to focus on worksheets in this tutorial.

In short, you have to call the PageSetupXL4M routine with a really long call statement, like this:

Sub FasterPageSetup()
Call PageSetupXL4M(Orientation:="2", _
    LeftMarginInches:="0.25", _
    RightMarginInches:="0.25", _
    TopMarginInches:="0.5", _
    BottomMarginInches:="0.5", _
    HeaderMarginInches:="0.3", _
    FooterMarginInches:="0.3", _
    Zoom:="{2,1}", _
    CenterVertically:="False", _
    CenterHorizontally:="True")
End Sub

This lengthy statement adjusts the page setup properties on the active sheet, and is equivalent to this slower (but newer) method:

Sub SlowPageSetup()
    With ActiveSheet.PageSetup
        .Zoom = False
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 2
        .FitToPagesTall = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With
End Sub

The first thing you’ll notice is that each property is entered as a string in the Call statement of the FasterPageSetup routine. You’ll also notice I didn’t use all of the available properties in my Call statement. All the arguments are optional and the PageSetupXL4M macro organizes them in the proper format for the PAGE.SETUP function. This makes your job a lot easier, because the PAGE.SETUP function is just as finicky as the Worksheet.PageSetup method is slow.

The syntax for some of the PAGE.SETUP properties is unusual, especially for the Zoom and Orientation properties. If you’re unfamiliar with Excel 4.0 macro statements, it’s hard to know the different ways to enter each string. To make it easier, I’ve compiled a description of all the accepted properties, examples of how to use them, and their corresponding Worksheet.PageSetup equivalent methods:

Detailed PageSetupXL4M Argument Explanations

PageSetupXL4M Argument Description .PageSetup Equivalent Example(s)
LeftHead Text you want displayed in the upper left of your page .LeftHeader LeftHead:="Header Text"
CenterHead Text you want displayed in the top center of your page .CenterHeader CenterHead:="Header Text"
RightHead Text you want displayed in the upper right of your page .RightHeader RightHead:="Header Text"
LeftFoot Text you want displayed in the lower left of your page .LeftFooter LeftFoot:="Footer Text"
CenterFoot Text you want displayed in the bottom center of your page .CenterFooter CenterFoot:="Footer Text"
RightFoot Text you want displayed in the lower right of your page .RightFooter RightFoot:="Footer Text"
LeftMarginInches Left page margin in inches .LeftMargin (with InchesToPoints) LeftMarginInches:="0.25"
RightMarginInches Right page margin in inches .RightMargin (with InchesToPoints) RightMarginInches:="0.25"
TopMarginInches Top page margin in inches .TopMargin (with InchesToPoints) TopMarginInches:="0.5"
BottomMarginInches Bottom page margin in inches .BottomMargin (with InchesToPoints) BottomMarginInches:="0.5"
HeaderMarginInches Header margin in inches .HeaderMargin (with InchesToPoints) HeaderMarginInches:="0.75"
FooterMarginInches Footer margin in inches .FooterMargin (with InchesToPoints) FooterMarginInches:="0.75"
PrintHeadings Toggle to print Row and Column letters/numbers .PrintHeadings PrintHeadings:="TRUE"
PrintGridlines Toggle to print gridlines .PrintGridlines PrintGridlines:="FALSE"
PrintComments Toggle to print comments .PrintComments PrintComments:="FALSE"
PrintQuality Print quality in dots per inch. Horizontal and vertical values can be different and must be entered as an array, like "{90,72}" .PrintQuality PrintQuality:="{72,90}"
PrintQuality:="90"
CenterHorizontally Center on page horizontally .CenterHorizontally CenterHorizontally:="TRUE"
CenterVertically Center on page vertically .CenterVertically CenterVertically:="FALSE"
Orientation Numeric value representing page orientation
"1" is portrait, "2" is landscape
.Orientation Orientation:="2"
Draft Toggle for draft quality printing .Draft Draft:="TRUE"
PaperSize Numeric value representing the desired page size.
Typically an integer from 1 through 26.

1 Letter, 2 Letter (small), 3 Tabloid, 4 Ledger, 5 Legal, 6 Statement, 7 Executive, 8 A3, 9 A4, 10 A4 (small), 11 A5, 12 B4, 13 B5, 14 Folio, 15 Quarto, 16 10x14, 17 11x17, 18 Note, 19 ENV9, 20 ENV10, 21 ENV11, 22 ENV12, 23 ENV14, 24 C Sheet, 25 D Sheet, 26 E Sheet
.PaperSize PaperSize:="9"
FirstPageNumber Page number to start numbering pages from .FirstPageNumber FirstPageNumber:="12"
FirstPageNumber:="AUTO"
Order Specifies whether pages are printed top-to-bottom, then right ("1") or left-to-right, then down ("2") .Order Order:="1"
BlackAndWhite Toggle to print pages in black and white .BlackAndWhite BlackAndWhite:="FALSE"
Zoom Can be a logical toggle to fit the printed area on a page ("TRUE"), a percentage to scale the page ("75"), or an array defining how many pages you want to print ("{2,1}" for 2 pages wide, 1 page tall) .Zoom
.FitToPagesWide
.FitToPagesTall
Zoom:="{2,1}"
Zoom:="75"
Zoom:="TRUE"

Performance Comparison

To prove how much faster the ExecuteExcel4Macro version of PAGE.SETUP is, I ran the following macros on a workbook with 30 spreadsheets and recorded the timing differences.

Sub SlowPageSetup_Loop()
For Each sht In ActiveWorkbook.Sheets
    With sht.PageSetup
        .Zoom = False
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 2
        .FitToPagesTall = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With
Next sht
End Sub
Sub FasterPageSetup_Loop()
For Each sht In ActiveWorkbook.Sheets
    sht.Select
    Call PageSetupXL4M(Orientation:="2", _
        LeftMarginInches:="0.25", _
        RightMarginInches:="0.25", _
        TopMarginInches:="0.5", _
        BottomMarginInches:="0.5", _
        HeaderMarginInches:="0.3", _
        FooterMarginInches:="0.3", _
        Zoom:="{2,1}", _
        CenterVertically:="False", _
        CenterHorizontally:="True")
Next sht
End Sub

The FasterPageSetup_Loop macro uses the old Excel 4.0 PAGE.SETUP function and finished in 8.91 seconds. This still isn’t fast, but it’s much faster than the SlowPageSetup_Loop which took a whopping 37.29 seconds.

Disabling Application Flags

You can improve the performance even more by disabling certain Application flags. The difference between the PAGE.SETUP and Worksheet.PageSetup approaches isn’t nearly as dramatic once the following properties are disabled:

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .PrintCommunication = False
End With

I highly recommend you set these to False at the beginning of your macro. You can set them back to True right before the end. When I disabled these time-wasting events, the PAGE.SETUP method finished in 4.87 seconds and the .PageSetup method finished in 5.42 seconds. Both methods show considerable speed improvements, but the .PageSetup method improvement is downright staggering. The time reduced from 37.29 seconds down to 5.42 seconds.

Of the three disabled properties, the PrintCommunication property is the one that had the biggest impact on macro speed. This was especially true for the slow .PageSetup macro.

Between the two optimized codes, the PAGE.SETUP routine performed about 10% faster for me, but your results may vary. I actually had some test cases where the results were slower with PAGE.SETUP.

Although the timing results with these two macros are closer, you have to keep in mind the “fast” VBA PAGE.SETUP method wastes time by selecting the sheet you want to edit, whereas the slow VBA .PageSetup method doesn’t have to activate each new sheet before adjusting the properties. Recognizing this fact, it’s likely the actual page setup portion of the “fast” code is a bit faster, even with the the Application events above disabled.

Either way, this tutorial shows you should at least disable ScreenUpdating, EnableEvents and PrintCommunications before adjusting your page setup properties. The PrintCommunications property alone can easily cut your macro execution time in half. By playing with alternative methods, we were able to speed up the VBA PageSetup execution time from 37.29 seconds down to 4.87 seconds. That’s still not very fast, but it’s a big improvement!

I hope you enjoyed this little experiment with VBA page setup speeds. When you’re ready to take your VBA to the next level, subscribe using the form below.