PageSetup slow

[Tom Ogilvy]

The best way is to eliminate the setting you are not changing. Each setting
you make is a different call to the pagesetup and takes a measurable amount
of time. Instead of setting 30 or so settings, you probably only need to
make 2 or 3 changes. Remove the rest.

Note that these settings are complimentary

> .Zoom = False
> .FitToPagesWide = 2
> .FitToPagesTall = False

so if you are using fittopages you need zoom set to false.

You want to also set displaypagebreaks after you do the page setup as well -
although that will speed up code running later, not this code.

Regards,
Tom Ogilvy

"Oliver Chiu" <oc...@tmbioscience.com> wrote in message
news:3f0d5a7c$1...@post.usenet.com...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> I have done the following test code and found it very slow. Almost 7
seconds
> to complete. I know someone also did a similar test and found the same
> results. Is there any way to make the page setup run faster ? I have tried
> to disable the display of page break as suggested by Microsoft knowledg
base
> but no help.
>
> Thanks in advance.
> Oliver
>
>
> Sub Macro1()
>
> MsgBox ("Start ...")
>
> ActiveSheet.DisplayPageBreaks = False
> With ActiveSheet.PageSetup
> .PrintTitleRows = "$20:$20"
> .PrintTitleColumns = "$A:$B"
> End With
> ActiveSheet.PageSetup.PrintArea = ""
> With ActiveSheet.PageSetup
> .LeftHeader = "Test company"
> .CenterHeader = ""
> .RightHeader = "CFTR 30 SL"
> .LeftFooter = ""
> .CenterFooter = ""
> .RightFooter = ""
> .LeftMargin = Application.InchesToPoints(0.5)
> .RightMargin = Application.InchesToPoints(0.5)
> .TopMargin = Application.InchesToPoints(1)
> .BottomMargin = Application.InchesToPoints(1)
> .HeaderMargin = Application.InchesToPoints(0.5)
> .FooterMargin = Application.InchesToPoints(0.5)
> .PrintHeadings = False
> .PrintGridlines = True
> .PrintComments = xlPrintNoComments
> .PrintQuality = 600
> .CenterHorizontally = False
> .CenterVertically = False
> .Orientation = xlLandscape
> .Draft = False
> .PaperSize = xlPaperLetter
> .FirstPageNumber = xlAutomatic
> .Order = xlOverThenDown
> .BlackAndWhite = False
> .Zoom = False
> .FitToPagesWide = 2
> .FitToPagesTall = False
> .PrintErrors = xlPrintErrorsDisplayed
> End With
>
> MsgBox ("Done")
> End Sub
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Ron de Bruin]

Read this thread Oliver from John Green

********************************
PageSetup in VBA has always been a painfully slow process. If you can't avoid having
to set these parameters, you can use the Excel 4 macro function, PAGE.SETUP to carry
out most of the PageSetup operations much more quickly. The following two macros are
almost equivalent, and should give you the clues you need to start using PAGE.SETUP.
You can download a full description of all the Excel 4 macro functions from
Microsoft's web site:

Sub PS()

ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup

.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True

.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False
.Zoom = False

.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

Sub PS4()
head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight & ","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr & ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale & ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality & ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," & Draft & ")"

Application.ExecuteExcel4Macro pSetUp
End Sub

John Green (Excel MVP)
Sydney
Australia

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

[Tom Ogilvy]

That will be faster, but the big improvement will be eliminating making
settings that are not necessary.

Regards,
Tom Ogilvy

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:O423RPu...@TK2MSFTNGP11.phx.gbl...

[Ron de Bruin]

Indeed Tom

Like John also say

>If you can't avoid having to set these parameters, you can use the Excel 4 macro function

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"Tom Ogilvy" <twog...@msn.com> wrote in message news:%23AVoDbu...@TK2MSFTNGP11.phx.gbl...