McGimpsey & Associates : Excel : Bypassing Slow VBA PageSetup

This site will look better if you upgrade to a browser that supports web standards.

Avoiding VBA's lethargic approach to Page Setup

Accessing a worksheet's PageSetup object is painfully slow in VBA. Seemingly unable to maintain a connection with the object, instead, VBA appears to go out to the printer driver for each operation, making

    
   With ActiveSheet.PageSetup
      .LeftHeader = "TopLeft"
      .RightFooter = "BottomRight"
   End With

take twice as long as if only the LeftHeader property were set. Fortunately, you can still use XL4 macros, which can set all the parameters at once, from VBA. This macro, regardless of how many arguments you use, will take about the same amount of time as setting one parameter via VBA. It's based on a post by John Green in the excel.programming newsgroup.

To use it to duplicate the above code, in about half the time, call the macro like this:

   PageSetupXL4M LeftHead:="TopLeft", RightFoot:="BottomRight"

Note that all the arguments are optional. The macro won't overwrite any existing properties unless you specify a value for those properties, except for the Header/Footer. In that case, setting any combination of the three arguments (e.g., LeftHead, CenterHead or RightHead) will replace the entire header or footer. Note also that this will only work on the active sheet.

   Public 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)
     'based on a post by John Green in
     'microsoft.public.excel.programming
     'on 21 January 2001:
     'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
     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

This page last updated Wednesday, 16 June 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()

MultiCat()

PageSetupXL4M()

PrevSheet()

RandInt()

SampleNoReplace()

SumBold()

UnixToXLTime()

XL VBA Macros

Mac XL

Avoiding VBA's lethargic approach to Page Setup

Quick Links