This site will look better if you upgrade to a browser that supports web standards.
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
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Don't know how to use User Defined Functions? See David McRitchie's Getting Started with Macros and User Defined Functions