Use of ActiveSheet.PageSetup in print macro slows process.

ExcelAvenger

New Member
Joined
Oct 11, 2006
Messages
3
I am using Excel 2003 SP2 to create a series of macros, one of which is to allow the user to print a portion of a worksheet created through another macro.

The macro detects the print area then uses ActiveSheet.PageSetup commance to set the various page setup options - margins, layout, etc.

I am on a network and have noticed through debug.print statements that each ActiveSheet.PageSetup commands takes about 5 seconds and since there are roughly 30 of them, it takes quite awhile before the printer dialogue box appears.

Here is the macro:

Sub PrintPricingDocument()
Sheets("Pricing Document").Select
Range("A1").Select
Selection.End(xlDown).Select
strLoc = Selection.Address
numLoc = Mid$(strLoc, 4, 2) 'gives the row number for the bottom of the print range
StrLoc2 = "$A$1:$G$" + CStr(numLoc)
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = StrLoc2
Range("A1").Select
ActiveSheet.PageSetup.PrintTitleRows = ""
ActiveSheet.PageSetup.PrintTitleColumns = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = ""
ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.TopMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.BottomMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.PrintHeadings = False
ActiveSheet.PageSetup.PrintGridlines = False
ActiveSheet.PageSetup.PrintComments = xlPrintNoComments
ActiveSheet.PageSetup.PrintQuality = 600
ActiveSheet.PageSetup.CenterHorizontally = True
ActiveSheet.PageSetup.CenterVertically = False
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Draft = False
ActiveSheet.PageSetup.PaperSize = xlPaperLetter
ActiveSheet.PageSetup.FirstPageNumber = xlAutomatic
ActiveSheet.PageSetup.Order = xlDownThenOver
ActiveSheet.PageSetup.BlackAndWhite = False
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.PrintErrors = xlPrintErrorsDisplayed
Application.Dialogs(xlDialogPrint).Show
End Sub

I have tried the above statements in a With statement but no difference. I suspect this has something to do with the printer being on the company network?! Finally, I noticed that, in one instance after running my print macro, my other macros (invoked separately - not called by this macro) also slowed down - but this only happened once in several tests. Any ideas? Thanks in advance.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Print setup can be slow depending on a number of things.
especially if is is a network printer.
I won't go into all of them but

A couple of things to try and speed things up
1) Don't display pagebreaks
.DisplayPageBreaks = False
2) Page setup code faster using
this method.

Uses old XL 4 macros function

Code:
Code:
'// NB: If any of the Variables are set wrong you will
'// Get NO error BUT it won't setup as you want.
ExecuteExcel4Macro ( _
    "Page.Setup(""Hello"",""Testing"",0.75,0.75,1,1,FALSE" & _
    ",FALSE,FALSE,FALSE,2,9,TRUE,100,1,FALSE,360,0.5,0.5,FALSE,FALSE)")

Where:
head =Hello
foot =Testing
left =0.75
right =0.75
top =1
bot =1
hdng =FALSE
grid =FALSE
h_cntr =FALSE
v_cntr =FALSE
orient =2
paper_size =9
scale =TRUE
pg_num =100
pg_order =1
bw_cells =FALSE
quality =300
head_margin =0.5
foot_margin =0.5
notes =FALSE
draft =FALSE
 

ExcelAvenger

New Member
Joined
Oct 11, 2006
Messages
3
Many thanks. That did the trick...however...

while my print macro now runs very fast...my other macros go really slow once this macro has been run.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
So your other macros use to run faster before this update ??
Unsure why, can you post the code that is running slower ?
 

ExcelAvenger

New Member
Joined
Oct 11, 2006
Messages
3

ADVERTISEMENT

Wish I could....it's about 4500 lines.

I believe it has something to do with the network and not the code you sent me. I was having this problem intermittently using the orginal print macro I had. I searched on that topic and discovered that others have found printing to network printers has caused slowdowns running Excel. Unfortunately, none of these posts were particular to using macros and the suggestions to correct the issue in Excel didn't really apply.

Using the original print macro, I tried running it on another's PC and while it ran slow on their PC too, it didn't seem to cause the slow down with the other macros afterward like it did on my PC sometimes.

I am going to take the new code and try running on another PC and see if I have similar problems. Maybe it's environmental.

Again, many thanks for your help.
 

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
307
Re: Speeding up Page Setup

I have tried this code and can not get it to work for me!

The only think I can think would be that I set the print area before the page set up code.

ActiveSheet.PageSetup.PrintArea = "$A:$M"
ExecuteExcel4Macro ( _
"Page.Setup("","",0.75,0.75, 0.75, 0.75,FALSE" & _
",FALSE,TRUE,FALSE,2,1,TRUE,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")


I would also like for it to print 1 pg wide by as many tall as it needs... can that TRUE be something else? I read somewhere about an array? What is the syntax for that?

Thank you for any help you can gimme!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Speeding up Page Setup

I have tried this code and can not get it to work for me!

The only think I can think would be that I set the print area before the page set up code.

ActiveSheet.PageSetup.PrintArea = "$A:$M"
ExecuteExcel4Macro ( _
"Page.Setup("","",0.75,0.75, 0.75, 0.75,FALSE" & _
",FALSE,TRUE,FALSE,2,1,TRUE,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")


I would also like for it to print 1 pg wide by as many tall as it needs... can that TRUE be something else? I read somewhere about an array? What is the syntax for that?

Thank you for any help you can gimme!!
I'm not sure about the Excel4Macro code, but setting your print area to whole columns may slow navigation in your actual sheet. Just something to watch out for.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,090
Messages
5,545,899
Members
410,711
Latest member
Josh324
Top