About PageSetup..

Apple

unread,

Jan 22, 2001, 3:34:37 PM1/22/01

to

Every time when I do something with pagesetup I need to wait for a long
time~~ (a few seconds may be, per worksheet), the problem came when there
are hundreds of worksheets..I am not quite sure if the computer hang or it
is working... I just wonder..if it is normal to wait for a long time or I
can set something to make it faster..thanks for any help

John Green

unread,

Jan 22, 2001, 3:52:42 PM1/22/01

to

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

Please post all replies to NewsGroups

In article <#9U76MLhAHA.1404@tkmsftngp05>, Apple wrote:
> From: "Apple" <ap...@hello.com>
> Subject: About PageSetup..
> Date: Tue, 23 Jan 2001 04:34:37 +0800
> Newsgroups: microsoft.public.excel.programming

Ctrl-Alt-Dlt-Hlp

unread,

Jan 22, 2001, 4:07:46 PM1/22/01

to

Suggest you post your code. If you recorded it, the PageSetUp contains
a lot of lines which are not needed because they are defalt settings.
FWIW
In article <#9U76MLhAHA.1404@tkmsftngp05>,

--
Ctrl-Alt-Dlt-Hlp ô)ô

Sent via Deja.com
http://www.deja.com/

Ctrl-Alt-Dlt-Hlp

unread,

Jan 22, 2001, 4:07:45 PM1/22/01

to

Suggest you post your code. If you recorded it, the PageSetUp contains
a lot of lines which are not needed because they are defalt settings.
FWIW
In article <#9U76MLhAHA.1404@tkmsftngp05>,
"Apple" <ap...@hello.com> wrote:

--

Ctrl-Alt-Dlt-Hlp

unread,

Jan 22, 2001, 4:07:44 PM1/22/01

to

Suggest you post your code. If you recorded it, the PageSetUp contains
a lot of lines which are not needed because they are defalt settings.
FWIW
In article <#9U76MLhAHA.1404@tkmsftngp05>,
"Apple" <ap...@hello.com> wrote:

--

Apple

unread,

Jan 22, 2001, 5:58:47 PM1/22/01

to

Thanks for your reply..but I am still not sure how to use the Excel 4 macro
function..may be I post my code here...

With ActiveWorksheet.PageSetup
.CenterHorizontally = True
.PaperSize = xlPaperLetter
End With

another one :

With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.CenterHorizontally = True
.Orientation = xlLandscape
End With

thanks for your help..really..:)

Jeff McAhren

unread,

Jan 22, 2001, 5:58:24 PM1/22/01

to

Hi Apple,

The amount of time it takes to set pagesetup properties varies with
different printer drivers. If possible, try running the same code with
different printers set as the default printer. There should be significant
time differences. At another company, I used a HP4si(?) driver with a
HP5si(?) printer, which ran pagesetup faster than the HP5si driver. The
output was fine.

Also, only some page setup properties execute slowly, most execute
instantly, so some experimentation might provide more information that will
allow you to judge which setup properties you might be able to live without.

HTH, JM

"Apple" <ap...@hello.com> wrote in message
news:#9U76MLhAHA.1404@tkmsftngp05...

Bob Flanagan

unread,

Jan 22, 2001, 6:49:59 PM1/22/01

to

Apple, there is an easy way to greatly speed up pagesetup. What you do is
only change only those setup properties that need changing. Use an IF
statement to check the setting and determine if a change is needed:

If .LeftHeader <> "My Company" then .LeftHeader = "My Company"

and so forth. The speed increase is 5-10x.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Apple" <ap...@hello.com> wrote in message
news:#9U76MLhAHA.1404@tkmsftngp05...

Jeff McAhren

unread,

Jan 22, 2001, 8:43:26 PM1/22/01

to

I performed a quick analysis comparing the speed in which it takes to set
the 27 print properties on three different printers networked to my machine.
You can use the code to test your network printers.

This is a link to the analysis summary...

http://www.geocities.com/jeff_mcahren/excel.htm

"Apple" <ap...@hello.com> wrote in message
news:#9U76MLhAHA.1404@tkmsftngp05...

John Green

unread,

Jan 23, 2001, 4:33:47 AM1/23/01

to

Here are your macros followed by the Excel 4 equivalents:

Sub test1()
With ActiveSheet.PageSetup

.CenterHorizontally = True
.PaperSize = xlPaperLetter
End With

End Sub

Sub PS4_1()
h_cntr = True
paper_size = 1

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

Sub test2()

With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.CenterHorizontally = True
.Orientation = xlLandscape
End With

End Sub

Sub PS4_2()
Top = 0.79
bot = 0.79
h_cntr = True
orient = 2

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

Please post all replies to NewsGroups

In article <#bTb8ZMhAHA.1624@tkmsftngp05>, Apple wrote:
> From: "Apple" <ap...@hello.com>

> Subject: Re: About PageSetup..
> Date: Tue, 23 Jan 2001 06:58:47 +0800
> Newsgroups: microsoft.public.excel.programming