Faster Alternatives to VBA PageSetup

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 and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

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

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.