PageSetup calls are slow
 

The code below takes nearly 11 seconds to run; this is on a WinXP with
a P4 at 1.2 GHz, 512MB RAM, oceans of free disk space and Excel 2002.
Experiments with various lines commented out show that each line
requires an astonishing 1.2 seconds to run, except the last three
(.Zoom and the two .FitToPages? lines), which execute all but
instantaneously.

Other open programs, available RAM, all the usual suspects for slow
code don't seem to matter here; I get very constant results from
checking the timer before and after this code runs -- even in an empty
workbook with nothing else open.

Any tips on speeding this up?

Thanks!
Stan

With ActiveSheet.PageSetup
'margins are measured in points, 72 points to the inch
.LeftMargin = 36
.RightMargin = 36
.TopMargin = 36
.BottomMargin = 36
.HeaderMargin = 0
.FooterMargin = 0
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

PageSetup calls are slow
 

For some reason, excel does a complete recalculation at each line of the
with statement.
Setting calculation=false doesn't help.
On the other hand, once a page has its settings set, they persist. So
once you have set them, you don't need to run the:
With ActiveSheet.PageSetup. The only time you would need this is if
your macro is creating a new sheet. If you could copy an existing sheet
with the settings already done, you wouldn't need to run this statement
ever.

Stan wrote:
The code below takes nearly 11 seconds to run; this is on a WinXP with
a P4 at 1.2 GHz, 512MB RAM, oceans of free disk space and Excel 2002.
Experiments with various lines commented out show that each line
requires an astonishing 1.2 seconds to run, except the last three
(.Zoom and the two .FitToPages? lines), which execute all but
instantaneously.

Other open programs, available RAM, all the usual suspects for slow
code don't seem to matter here; I get very constant results from
checking the timer before and after this code runs -- even in an empty
workbook with nothing else open.

Any tips on speeding this up?

Thanks!
Stan

With ActiveSheet.PageSetup
'margins are measured in points, 72 points to the inch
.LeftMargin = 36
.RightMargin = 36
.TopMargin = 36
.BottomMargin = 36
.HeaderMargin = 0
.FooterMargin = 0
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

PageSetup calls are slow
 

VBA accesses to the PageSetup object are painfully slow. You can speed
it up dramatically by using XL4M call. Take a look he

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



In article ,
(Stan) wrote:

The code below takes nearly 11 seconds to run; this is on a WinXP with
a P4 at 1.2 GHz, 512MB RAM, oceans of free disk space and Excel 2002.
Experiments with various lines commented out show that each line
requires an astonishing 1.2 seconds to run, except the last three
(.Zoom and the two .FitToPages? lines), which execute all but
instantaneously.

Other open programs, available RAM, all the usual suspects for slow
code don't seem to matter here; I get very constant results from
checking the timer before and after this code runs -- even in an empty
workbook with nothing else open.

Any tips on speeding this up?

Thanks!
Stan

With ActiveSheet.PageSetup
'margins are measured in points, 72 points to the inch
.LeftMargin = 36
.RightMargin = 36
.TopMargin = 36
.BottomMargin = 36
.HeaderMargin = 0
.FooterMargin = 0
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

PageSetup calls are slow
 

Didn't try running your code but I have a similar situation. I have a macro
that creates 10 pivot tables, refreshes eight queries on a 2 million record
SQL database and then adds formating to about two dozen sheets. Then it adds
a custom footer to each of the two dozen sheets. It takes longer to do this
last step than all of the other steps combined! Would love to know why.

- John Michl
www.JohnMichl.com


"Stan" wrote in message
om...
The code below takes nearly 11 seconds to run; this is on a WinXP with
a P4 at 1.2 GHz, 512MB RAM, oceans of free disk space and Excel 2002.
Experiments with various lines commented out show that each line
requires an astonishing 1.2 seconds to run, except the last three
(.Zoom and the two .FitToPages? lines), which execute all but
instantaneously.

Other open programs, available RAM, all the usual suspects for slow
code don't seem to matter here; I get very constant results from
checking the timer before and after this code runs -- even in an empty
workbook with nothing else open.

Any tips on speeding this up?

Thanks!
Stan

With ActiveSheet.PageSetup
'margins are measured in points, 72 points to the inch
.LeftMargin = 36
.RightMargin = 36
.TopMargin = 36
.BottomMargin = 36
.HeaderMargin = 0
.FooterMargin = 0
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With