PageSetup is very slow
 

Using the code below, one can see that the slow parts ar the settting of the
margins and
the settings of the Headers/Footers.

(5/17/2008 03:51:02) Start PageSetup
(5/17/2008 03:51:02) Start Orientation
(5/17/2008 03:51:03) Start Margins
(5/17/2008 03:51:06) Start HeaderFooter
(5/17/2008 03:51:09) Start Gridlines
(5/17/2008 03:51:10) Start ZoomFit
(5/17/2008 03:51:10) End PageSetup


LogMessage "Start PageSetup"
With .PageSetup
LogMessage "Start Orientation"
.Orientation = xlLandscape
LogMessage "Start Margins"
.LeftMargin = HeaderFooterLeftRightMargin
.RightMargin = HeaderFooterLeftRightMargin
.TopMargin = BottomTopMargin
.BottomMargin = BottomTopMargin
.HeaderMargin = HeaderFooterLeftRightMargin
.FooterMargin = HeaderFooterLeftRightMargin
LogMessage "Start HeaderFooter"
.LeftHeader = "&F"
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = "&D(&T)"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
LogMessage "Start Gridlines"
.PrintGridlines = True
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
LogMessage "Start ZoomFit"
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
LogMessage "End PageSetup"

Since the program will be creating N +3 worksheets, I can push the overhead
to the Form's Load event by
setting the properties in the Sheet1 created by Excel when creating a New
Workbook, and then,
instead of adding a worksheet, as needed, I can COPY Sheet1.

PageSetup is very slow
 

You can greatly speed up the process by changing only values that need
changing. A check on values is far faster. For example:

If .Orientation < xlLandscape then .Orientation = xlLandscape

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel



"Howard Kaikow" wrote in message
...
Using the code below, one can see that the slow parts ar the settting of
the
margins and
the settings of the Headers/Footers.

(5/17/2008 03:51:02) Start PageSetup
(5/17/2008 03:51:02) Start Orientation
(5/17/2008 03:51:03) Start Margins
(5/17/2008 03:51:06) Start HeaderFooter
(5/17/2008 03:51:09) Start Gridlines
(5/17/2008 03:51:10) Start ZoomFit
(5/17/2008 03:51:10) End PageSetup


LogMessage "Start PageSetup"
With .PageSetup
LogMessage "Start Orientation"
.Orientation = xlLandscape
LogMessage "Start Margins"
.LeftMargin = HeaderFooterLeftRightMargin
.RightMargin = HeaderFooterLeftRightMargin
.TopMargin = BottomTopMargin
.BottomMargin = BottomTopMargin
.HeaderMargin = HeaderFooterLeftRightMargin
.FooterMargin = HeaderFooterLeftRightMargin
LogMessage "Start HeaderFooter"
.LeftHeader = "&F"
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = "&D(&T)"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
LogMessage "Start Gridlines"
.PrintGridlines = True
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
LogMessage "Start ZoomFit"
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
LogMessage "End PageSetup"

Since the program will be creating N +3 worksheets, I can push the
overhead
to the Form's Load event by
setting the properties in the Sheet1 created by Excel when creating a New
Workbook, and then,
instead of adding a worksheet, as needed, I can COPY Sheet1.

PageSetup is very slow
 

"Bob Flanagan" wrote in message
. ..
You can greatly speed up the process by changing only values that need
changing. A check on values is far faster. For example:

If .Orientation < xlLandscape then .Orientation = xlLandscape

Thanx.

That won't help for the Header/Footer/Margin values, since I am creating the
workbook anew, and the defaults
are very unlikely to match the desired settings

Of course, I have to determine how long it takes to do a COPY rather than
ADD.