The following code takes about 20 to 30 seconds to run in Excel 98. Why and
how can I speed it up?

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut


--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 07/24/2003

Try turning off the screen updating... for example:

Application.ScreenUpdating = False
'do stuff
Application.ScreenUpdating = True

"Carl Brehm" wrote in message
...
The following code takes about 20 to 30 seconds to run in Excel 98. Why
and
how can I speed it up?

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut


--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 07/24/2003

I checked it out and got a similar time as you. I guess
that's just the way it is.

Mike's suggestion of turning off the screen updating
produced a significant but modest improvement of about 2
seconds. In my trials, changing the range references from
named ranges to specified ranges surprisingly returned a
slightly slower time by about 1 second. What did produce
a dramatic improvement was reduction of code.

Can you not eliminate most of the code? It seems to me
that most of the code is unnecessary. It looks like
you're essentially quoting the macro recorder.


Regards,
Greg

-----Original Message-----
The following code takes about 20 to 30 seconds to run
in Excel 98. Why and
how can I speed it up?

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item
("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut


--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date:
07/24/2003


.

Screenupdating won't help this problem. AIR, the explanation is that for each property you set,
VBA opens the printer driver, then closes it. In your example, that's 19 open/closes of the
printer driver. (That's admittedly a lousy implementation on MS's part, but there's nothing we
can do about that...)

The best way to speed this up would be to call an XLM macro function instead of using the VBA
code. The next best is to only change those settings that must be changed. i.e. if all you want
is to set the margins, just do that and no more.

If you want to try the XLM route, I've copied the documentation you need below (it's long!).

The VBA line would look like

Application.ExecuteExcel4Macro("PAGE.SETUP(....... )")

with the argument list replacing the dots. Check VBA Help for more information on the
ExecuteExcel4Macro function if you need it. These arguments are positional. If you skip one, you
must include the comma that would have terminated it, so you have 2 commas in a row to indicate
the missing argument.

Here's the scoop on the PAGE.SETUP arguments:

~~~~~~~~~~~~~~~~~~~~~~~

There are three syntax forms of PAGE.SETUP. Syntax 1 applies if a sheet or macro sheet is
active; syntax 2 applies if a chart is active; syntax three applies to Visual Basic modules and
the info Window.

Arguments correspond to check boxes and text boxes in the Page Setup dialog box. Arguments that
correspond to check boxes are logical values. If an argument is TRUE, Microsoft Excel selects
the check box; if FALSE, Microsoft Excel clears the check box. Arguments for margins are always
in inches, regardless of your country setting.

Syntax 1
Worksheets and macro sheets

PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size,
scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)

Syntax 2
Charts

PAGE.SETUP(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale,
pg_num, bw_chart, quality, head_margin, foot_margin, draft)

Syntax 3
Visual Basic Modules and the Info Window

PAGE.SETUP(head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin,
foot_margin, pg_num)


Head specifies the text and formatting codes for the header for the current sheet. For
information about formatting codes, see "Remarks" later in this topic.

Foot specifies the text and formatting codes for the workbook footer.

Left corresponds to the Left box and is a number specifying the left margin.

Right corresponds to the Right box and is a number specifying the right margin.

Top corresponds to the Top box and is a number specifying the top margin.

Bot corresponds to the Bottom box and is a number specifying the bottom margin.

Hdng corresponds to the Row & Column Headings check box. Hdng is available only in the sheet
and macro sheet form of the function.

Grid corresponds to the Cell Gridlines check box. Grid is available only in the sheet and
macro sheet form of the function.

H_cntr corresponds to the Center Horizontally check box in the Margins panel of the Page
Setup dialog box.

V_cntr corresponds to the Center Vertically check box in the Margins panel of the Page Setup
dialog box.

Orient determines the direction in which your workbook is printed.

Orient Print format
1 Portrait
2 Landscape


Paper_size is a number from 1 to 26 that specifies the size of the paper.

Paper_size Paper type
1 Letter
2 Letter (small)
3 Tabloid
4 Ledger
5 Legal
6 Statement
7 Executive
8 A3
9 A4
10 A4 (small)
11 A5
12 B4
13 B5
14 Folio
15 Quarto
16 10x14
17 11x17
18 Note
19 ENV9
20 ENV10
21 ENV11
22 ENV12
23 ENV14
24 C Sheet
25 D Sheet
26 E Sheet


Scale is a number representing the percentage to increase or decrease the size of the sheet.
All scaling retains the aspect ratio of the original.

To specify a percentage of reduction or enlargement, set scale to the percentage.

For worksheets and macros, you can specify the number of pages that the printout should be
scaled to fit. Set scale to a two-item horizontal array, with the first item equal to the width
and the second item equal to the height. If no constraint is necessary in one direction, you can
set the corresponding value to #N/A.

Scale can also be a logical value. To fit the print area on a single page, set scale to TRUE.

Pg_num specifies the number of the first page. If zero, sets first page to zero. If "Auto" is
used, then the page numbering is set to automatic. If omitted, PAGE.SETUP retains the existing
pg_num.

Pg_order specifies whether pagination is left-to-right and then down, or top-to-bottom and
then right.

Pg_order Pagination
1 Top-to-bottom, then right
2 Left-to-right, then down

Bw_cells is a logical value that specifies whether to print cells and all graphic objects,
such as text boxes and buttons, in color.

If bw_cells is TRUE, Microsoft Excel prints cell text and borders in black and cell backgrounds
in white.

If bw_cells is FALSE , Microsoft Excel prints cell text, borders, and background patterns in
color (or in gray scale).

Bw_chart is a logical value that specifies whether to print chart in color.

Size is a number corresponding to the options in the Chart Size box, and determines how you
want the chart printed on the page within the margins. Size is available only in the chart form
of the function.

Size Size to print the chart
1 Screen size
2 Fit to page
3 Full page

Quality specifies the print quality in dots-per-inch. To specify both horizontal and vertical
print quality, use an array of two values.

Head_margin is the placement, in inches, of the running head margin from the edge of the
page.

Foot_margin is the placement, in inches, of the running foot margin from the edge of the
page.

Draft corresponds to the Draft Quality checkbox in the Sheet tab and in the Chart tab of the
Page Setup dialog box. If FALSE or omitted, graphics are printed with the sheet. If TRUE, no
graphics are printed.

Notes specifies whether to print cell notes with the sheet. If TRUE, both the sheet and the
cell notes are printed. If FALSE or omitted, just the sheet is printed.

Remarks

Microsoft Excel no longer requires you to enter formatting codes to format headers and footers,
but the codes are still supported and recorded by the macro recorder. You can include these
codes as part of the head and foot text strings to align portions of the header or footer to the
left, right, or center; to include the page number, date, time, or workbook name; and to print
the header or footer in bold or italic.

Formatting code Result
&L Left-aligns the characters that follow.
&C Centers the characters that follow.
&R Right-aligns the characters that follow.
&B Turns bold printing on or off (now obsolete).
&I Turns italic printing on or off.
&U Turns single underlining printing on or off.
&S Turns strikethrough printing on or off.
&O Turns outline printing on or off (Macintosh only).
&H Turns shadow printing on or off (Macintosh only).
&D Prints the current date.
&T Prints the current time.
&A Prints the name of the sheet
&F Prints the name of the workbook.
&P Prints the page number.
&P+number Prints the page number plus number.
&P-number Prints the page number minus number.
&& Prints a single ampersand.
& "fontname, fontstyle" Prints the characters that follow in the specified font and style. Be
sure to include a comma immediately following the fontname, and double quotation marks around
fontname and fontstyle.
&nn Prints the characters that follow in the specified font size. Use a two-digit number to
specify a size in points.
&N Prints the total number of pages in the workbook.
&E Prints a double underline
&X Prints the character as superscript
&Y Prints the character as subscript

~~~~~~~~~~~~~~~~~~~~~~~


On Sat, 9 Aug 2003 22:46:12 -0700, "Greg Wilson" wrote:

I checked it out and got a similar time as you. I guess
that's just the way it is.

Mike's suggestion of turning off the screen updating
produced a significant but modest improvement of about 2
seconds. In my trials, changing the range references from
named ranges to specified ranges surprisingly returned a
slightly slower time by about 1 second. What did produce
a dramatic improvement was reduction of code.

Can you not eliminate most of the code? It seems to me
that most of the code is unnecessary. It looks like
you're essentially quoting the macro recorder.


Regards,
Greg

-----Original Message-----
The following code takes about 20 to 30 seconds to run
in Excel 98. Why and
how can I speed it up?

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut


--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date:
07/24/2003


.