Improve PageSetup code

S

sbitaxi

Hi all:

I'm using the following code to format the page setup for a workbook,
but as soon as I introduced it, the process time increased from 15
seconds to 3.5 minutes. Can anyone tell me why my code is so slow?

DestWS is the worksheet in the workbook this code is acting on. There
are 2 workbooks generated by my previous code and multiple worksheets
that are created per workbook that the following code is applied to.
DestEntRng is the entire range of data in DestWS, anywhere from 5 rows
to 1000, columns A:Z. BookType is a simple string for the footer
field.

Public Sub RptPageSetup(ByVal DestWS As Worksheet, DestEntRng As
Range, BookType As String)
Dim TallPages As Long

TallPages = Round(DestEntRng.Rows.Count / 40, 0)

With DestWS.PageSetup
.PrintTitleRows = "$1:$4"
.PrintArea = DestEntRng.Address
.LeftFooter = "Kidney Foundation of Canada"
.CenterFooter = "Page &P of &N"
.RightFooter = "Online " & BookType & " Report"
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = 56
.FitToPagesWide = False
.FitToPagesTall = TallPages
End With
End Sub
 
J

Jim Thomlinson

Page setup functionallity it disturbingly slow via VBA. It is distinctly
faster via XL4 macros. Search this forum or google for XL4 macros and you
might find something to head you in the right direction...
 
S

sbitaxi

Page setup functionallity it disturbingly slow via VBA. It is distinctly
faster via XL4 macros. Search this forum or google for XL4 macros and you
might find something to head you in the right direction...
Click to expand...

Thanks Jim, good to know.


S
 
J

Jim Cone

FitToPagesTall would normally be used to squeeze data that slightly
exceeded one page in length onto one page.
A TallPages calculation of 1000/40, if it worked, would result in
something unreadable.
Also, zoom and fit to pages are mutually exclusive - use one or the other.

So... try commenting out the TallPages, FitToPages and the Zoom lines.
Then add this line just below your variable declarations...
Application.DisplayPageBreaks = False.

Also, as Jim Thomlinson pointed out, using XL4 pagesetup code can be
faster. However, unless you are considerable smarter than average, it will take
more time than it is worth to master it. (i had lots of spare time)
Furthermore, I recall reading that there is a high probably that MS will "improve"
the next release of Excel by eliminating the use of XL4 code.
--
Jim Cone
Portland, Oregon USA




(e-mail address removed)>
wrote in message
Hi all:

I'm using the following code to format the page setup for a workbook,
but as soon as I introduced it, the process time increased from 15
seconds to 3.5 minutes. Can anyone tell me why my code is so slow?

DestWS is the worksheet in the workbook this code is acting on. There
are 2 workbooks generated by my previous code and multiple worksheets
that are created per workbook that the following code is applied to.
DestEntRng is the entire range of data in DestWS, anywhere from 5 rows
to 1000, columns A:Z. BookType is a simple string for the footer
field.

Public Sub RptPageSetup(ByVal DestWS As Worksheet, DestEntRng As
Range, BookType As String)
Dim TallPages As Long

TallPages = Round(DestEntRng.Rows.Count / 40, 0)

With DestWS.PageSetup
.PrintTitleRows = "$1:$4"
.PrintArea = DestEntRng.Address
.LeftFooter = "Kidney Foundation of Canada"
.CenterFooter = "Page &P of &N"
.RightFooter = "Online " & BookType & " Report"
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = 56
.FitToPagesWide = False
.FitToPagesTall = TallPages
End With
End Sub
 
S

sbitaxi

Hi Jim Cone:

Conveniently enough, I found a webpage (thanks to Jim Thomlinson's
advice) that shared code to resolve exactly this problem.

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

Very minimal learning on this one - copy/paste and declare the options
when calling on the PubSub.

I appreciate the clarification on the page setup properties. I was
trying to limit the number of rows per page to 40, hence the TallPages
calculation. I guess I appropriated that improperly.

It'll be a bit before I can use the next iteration of Excel from MS.
Limited by company budget - what they get is what I get. Right now
that is still 2002.

S
 
J

Jim Thomlinson

Thanks for responding back and posting the web link. I knew I had seen that
code somewhere before, but since I could not remember where I just told you
to go looking for it. I will now keep a copy of that for future reference...
 
S

sbitaxi

Jim,

There seems to be a problem, at least in 2002, with calling on the
Page.Setup in XL4.

Run-time error '1004': The formula you typed contains an error.

It is probably something simple, but I'm just not sure what, having no
experience with XL4

This is where it chokes

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup

This is how I am calling on the Sub

Call PageSetupXL4M("", "", "", "Kidney Foundation of Canada",
_
"Page &P of &N", "Online " & Data & "
Report", _
"", "", "", "", "", "", "$1:$4", "", "",
"", _
"", "", "xlLandscape", "", "xlPaperLegal",
"", _
"", "True", "")

It's probably a syntax in there somewhere. Thoughts?


Steven
 
J

Jim Thomlinson

Without looking too closely at what you have you are specifying all of the
optional arguments. You really only want to specify the ones that you need.
So for instance if all I want to do is to specify the right header then I
would call the procedure like this...

call PageSetupXL4M(RightHead:="Tada")

If any of your arguments are out of place then you will run into problems...
 
S

sbitaxi

Without looking too closely at what you have you are specifying all of the
optional arguments. You really only want to specify the ones that you need.
So for instance if all I want to do is to specify the right header then I
would call the procedure like this...

call PageSetupXL4M(RightHead:="Tada")

If any of your arguments are out of place then you will run into problems....
Click to expand...

That's what I did initially, but when it read RightHead:="Tada", it
called RightHead = True and that's what I ended up with, and FALSE on
the rest of the header and footer where I had not declared anything.
Although, it "worked".
 
S

sbitaxi

Correction - I had left out the ":" for some reason. Put it in and it
reads those fine. However, after paring things down, it still fails.
Perhaps I have defined something wrong. Possibly the PrintHeadings

Call PageSetupXL4M(LeftFoot:="Kidney Foundation of Canada", _
CenterFoot:="Page &P of &N", _
RightFoot:="Online " & Data & " Report", _
PrintHeadings:="$1:$4", _
Orientation:="xlLandscape", _
PaperSize:="xlPaperLegal")
 
S

sbitaxi

Correction - I had left out the ":" for some reason. Put it in and it
reads those fine. However, after paring things down, it still fails.
Perhaps I have defined something wrong. Possibly the PrintHeadings

        Call PageSetupXL4M(LeftFoot:="Kidney Foundation of Canada", _
                            CenterFoot:="Page &P of &N", _
                            RightFoot:="Online " & Data & " Report", _
                            PrintHeadings:="$1:$4", _
                            Orientation:="xlLandscape", _
                            PaperSize:="xlPaperLegal")
Click to expand...

Ok, so I isolated the following lines, and the code executed without
error, but it did not actually set anything in PageSetup. I suspect it
is the CentreFoot that is giving me grief, but the actually Page.Setup
statement doesn't seem to be doing anything except wasting time.

Very frustrating.

PrintHeadings:="$1:$4", _
CenterFoot:="Page &P of &N", _
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print area - selection 4
Define a Variable 1
Print sub returns 4
PageSetup Performance Question 1
Printing-don't need half this code but which half?:-) 5
Strange behavior with old macro now throws error with "Application 2
Printing Setup help 9
Page setup error after moving sheet tabs 3

Top