Slow down by page set up code

[Ben]

Hi all,

I am not sure why, but I have notice a substantial code slow down when my
application enters the page setup code. This has occurred in xl97/ 2000/
2002. My code runs through multiple workbook files and in each file, there
are multiple tabs each with a different print range etc.

Can you share with me what might be the possible culprit or perhaps there
are some other work arounds? The code below is basically what Excel's macro
recorder.

ActiveSheet.PageSetup.PrintArea = sPrintRange
With ActiveSheet.PageSetup
.CenterFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.FitToPagesWide = 1
.FitToPagesTall = 10
.Zoom = iZoom
End With

Thanks so much for your thoughts.

Ben

--

 

[Tom Ogilvy]

Each setting is executed as a separate command to the pagesetup code - so if
you are executing any commands that are not necessary (the are the default
values for instance), then remove them. Each command you remove will make it
faster.

Nonetheless, calls to page setup are time consuming.

The usual recommendation is to use the Excel 4 Macro command.

From an old post by John Green:

=================================
From: John Green ([email protected])
Subject: Re: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST




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

=====================

See if that is faster.

 

[rockhammer]

Hi Tom,

Thanks for your reply. I am a relative novice so please bear with me.

I have exactly this issue. In fact, even before I recently discovered &
implemented pagesetup code in/from this forum (see my posted questions
relating to subject "set header & footer automatically"), I have noticed that
my 3.8MB file of 12 tabs runs very slowly (page refresh when scrolling, etc.)
as soon as I print or print preview.

Are you suggesting that I can speed it up by simply removing all the headers
& footers that I put in via file->pagesetup (and simply do it via the
pagesetup macro on print)? I am guilty of putting in many formatting on my
worksheets - footers, number & date formats on just about every cell, hidden
rows/columns, etc.

What exactly is "Excel 4 macro command" that you mentioned?

Lastly, if it's not too much of a bother, if you have any pointers relating
to the questions relating to pagesetup macro I posted under subject "set
header & footer automatically" today it would be very much appreciated.

Thanks.