Excel VBA PageSetup

Topic

New Reply

Has anyone experienced very slow performance when using VBA PageSetup… both XL 95 and 2000 seem to totally bog down when processing one of these commands. Example

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.3)
.BottomMargin = Application.InchesToPoints(0.3)
end with

Each Margin set takes forever.
Is there a fix?

Thanks !!!!

Reply | Quote

Replies

Yes, I’ve seen a reference to it on Microsoft’s support page – can’t remember the id number – but there is definitely a memory leak when setting PageSetup from within VBA.

Jeremy

Reply | Quote

Try this one:

Q199505 XL2000: Macro Performance Slow When Page Breaks Are Visible

Reply | Quote

Thank Lief
I should have mentioned that I did some homework on this before posting to the Lounge. I found and tried the Microsoft ‘solution’. Neither ActiveSheet.DisplayAutomaticPageBreaks = False in XL95 or ActiveSheet.DisplayPageBreaks = False in XL2000 solves the problem. Interesting that this bug has been around for so many years but remains unresolved. Thanks for your input… much appreciated.

Lorne

Reply | Quote

If the other things that have been suggested do not help your situation, there is one other “solution” that does seem to work in all cases where I know that it has been tried. Unfortunately, it is not a really great solution. What does seem to fix this is to put the PageSetup into an Excel 4 macro which current versions of Excell can still run.

Reply | Quote

do you have some instructions on how to do this?

I have run into the memory leak problem myself, but I started programming with Excel 5.0, so have never seen Excel 4 macros!

Jeremy

Reply | Quote

Sorry, I do not know Excel 4 macros or how to set them up in later versions. I have just seen this solution posted on the CompuServe MSOffice forum by Bill Manville (who seem to know much more about Excel than MS does). If someone else does not jump in with a suggestion, then you might try there if you have access.

Reply | Quote

The syntax is:
Application.ExecuteExcel4Macro (“thecommand”)

From the XL4 macro Helpfile:

Macro Sheets Only
Equivalent to choosing the Page Setup command from the File menu. Use PAGE.SETUP to control the printed appearance of your sheets.
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)
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)
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)
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 10×14
17 11×17
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 chararcter as subscript

Related Functions

DISPLAY Controls screen and Info Window display
GET.DOCUMENT Returns information about a workbook
PRINT Prints the active workbook
WORKSPACE Changes workspace settings

Reply | Quote

I’m intrigued with this now.
Running Excel 2K SR1 on W2K with the above code as the only macro.
Nothing I can do can make it perform at other than ‘very fast’.
I’ve played around with most of the options, added sheets, changed printers etc.
Have you tried the code on a new (blank) workbook?

Reply | Quote

Yes I did Lief. I do a fair amount of amateur programming both at home and at work. I’m used to banging my head on a wall until I get a solution. I’m out of ideas on this one. Unfortunately, my home computer is an antique Dell 133 Pentium running Win 98 (soon to be replaced). I test the 4 margin sets running in XL2000 (V9.0.2720) at 40 seconds… 10 seconds per margin set. Other VBA commands are fast. ActiveSheet.DisplayPageBreaks=False has no effect. I’ll run a few tests on some my work computers. I believe that the Page Setups are tied to the active printer… maybe it has something to do with my HP720C deskjet. By-the-way… I seem to recal having the similar problem in the VB5 stand alone compiler a couple of years ago. Thanks for your interest.

Lorne

Reply | Quote

I believe that the problem is also related to the brand/model of printer and how it is attached. When I had the problem, it was always an HP LaserJet and I think it was always network attached.

Reply | Quote

The memory leak problem associated with setting PageSetup properties from Excel 97/2000 VBA doesn’t exist in Windows 2000.

It IS a documented problem in Windows 95/98.

Regards

Jeremy

Reply | Quote

I thought the discussion was about how slow the PageSetup worked, not about memory leaks. I have had the slow performance on 97 and 2000. I have not had any memory leaks that I am aware of.

Reply | Quote

…but the Microsoft KB article that explains the poor performance when setting PageSetup via VBA explains that it is due to memory leaks caused by execution of an internal routine.

Sorry I don’t have the KB number to hand!!

Jeremy

Reply | Quote

Just ran a test on a work machine… its a newer Dell than my own and running Office 95. No problems here. Looks like it’s related to my home computer configuration.
Lorne

Reply | Quote

Wondering if you ever found a solution to this problem (other than going back to Excel 95). I have the problem with XL2000 in NT. I tried setting a PageSetup object and making changes to it, but this behaves the same way. I also have an HP printer printer set as the default. I did find that setting the default printer to a different printer (in this case, a Lexmark Optra Color 45) will make the macro run faster, so it seems that the HP printer drivers have something to do with the problem. Maybe the drivers ignore the DisplayPageBreaks setting and repaginate anyway. The updated version of the MS KB article lists this as an Excel 2002 problem as well, so we won’t get any relief there.

Reply | Quote

Not a fix… but if you have to do this a few times, set up one page with the settings you want, and then copy that page into other blank pages. It can be hugely faster than recreating the settings on each page. (Of course, you might not be able to do it that way at all).

If you can use this method, you can have your default page as a hidden worksheet which you don’t need to recreate each time.

Reply | Quote