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

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...
--
HTH...

Jim Thomlinson


" wrote:

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

On Sep 4, 11:30*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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...
--
HTH...

Jim Thomlinson

" wrote:
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

Thanks Jim, good to know.


S

Only change the items absolutely necessary for the particular print job

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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





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

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

On Sep 4, 12:36*pm, "Jim Cone" wrote:
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


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

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...
--
HTH...

Jim Thomlinson


" wrote:

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

On Sep 4, 12:36 pm, "Jim Cone" wrote:
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


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

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

On Sep 4, 2:41*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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....
--
HTH...

Jim Thomlinson

" wrote:
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

On Sep 4, 12:36 pm, "Jim Cone" wrote:
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


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

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...
--
HTH...

Jim Thomlinson


" wrote:

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

On Sep 4, 2:41 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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....
--
HTH...

Jim Thomlinson

" wrote:
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

On Sep 4, 12:36 pm, "Jim Cone" wrote:
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


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