PageSetup is very slow

H

Howard Kaikow

In Excel 2003, on an old computer, the PageSetUp stuff (code below) takes
6-7 seconds in a visible Excel VBA IDE.
Using VB6 to automate Excel, corresponding code takes 9 seconds.

Commenting out the .Order = xlDownThenOver saves about 1 second.
Commenting out the .FirstPageNumber = xlAutomatic seems to have no impact,
likely because it is the default.

Does not seem to be any way to speed things up.

Sub TestPageSetup()
Dim d As Double
Dim d1 As Double

Application.ScreenUpdating = False

With ActiveSheet
Debug.Print Application.ScreenUpdating
Debug.Print Now
''''''''''''''''''''''''''''''''''''''''
With .PageSetup
.Orientation = xlLandscape
With .Application
d = .InchesToPoints(0.5)
d1 = .InchesToPoints(0.75)
End With
.LeftMargin = d
.RightMargin = d
.TopMargin = d1
.BottomMargin = d1
.HeaderMargin = d
.FooterMargin = d
.LeftHeader = "&F"
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = "&D(&T)"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.PrintGridlines = True
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
''''''''''''''''''''''''''''''''''''''''
Debug.Print Now
End With
Debug.Print Application.ScreenUpdating
Application.ScreenUpdating = True
End Sub
 
G

Gary Keramidas

have you tried it on another computer? it runs fine here, but i have a quad cire
with 8gb of ram, so i can't really test it on a slow computer.

False
5/15/2008 1:55:29 AM
5/15/2008 1:55:29 AM
False

but turning off calc did seem to improve it a little

Sub TestPageSetup()
Dim d As Double
Dim d1 As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ActiveSheet
Debug.Print Application.ScreenUpdating
Debug.Print Now
''''''''''''''''''''''''''''''''''''''''
With .PageSetup
.Orientation = xlLandscape
With .Application
d = .InchesToPoints(0.5)
d1 = .InchesToPoints(0.75)
End With
.LeftMargin = d
.RightMargin = d
.TopMargin = d1
.BottomMargin = d1
.HeaderMargin = d
.FooterMargin = d
.LeftHeader = "&F"
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = "&D(&T)"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.PrintGridlines = True
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
''''''''''''''''''''''''''''''''''''''''
Debug.Print Now
End With
Debug.Print Application.ScreenUpdating
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
H

Howard Kaikow

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

Bob Flanagan

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
 
H

Howard Kaikow

Bob Flanagan said:
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
Click to expand...

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

Howard Kaikow

Howard Kaikow said:
In Excel 2003, on an old computer, the PageSetUp stuff (code below) takes
6-7 seconds in a visible Excel VBA IDE.
Using VB6 to automate Excel, corresponding code takes 9 seconds.

Commenting out the .Order = xlDownThenOver saves about 1 second.
Commenting out the .FirstPageNumber = xlAutomatic seems to have no impact,
likely because it is the default.

Does not seem to be any way to speed things up.

Sub TestPageSetup()
Dim d As Double
Dim d1 As Double

Application.ScreenUpdating = False

With ActiveSheet
Debug.Print Application.ScreenUpdating
Debug.Print Now
''''''''''''''''''''''''''''''''''''''''
With .PageSetup
.Orientation = xlLandscape
With .Application
d = .InchesToPoints(0.5)
d1 = .InchesToPoints(0.75)
End With
.LeftMargin = d
.RightMargin = d
.TopMargin = d1
.BottomMargin = d1
.HeaderMargin = d
.FooterMargin = d
.LeftHeader = "&F"
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = "&D(&T)"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.PrintGridlines = True
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
''''''''''''''''''''''''''''''''''''''''
Debug.Print Now
End With
Debug.Print Application.ScreenUpdating
Application.ScreenUpdating = True
End Sub
Click to expand...


I'm baaaack!

previously, my main system was a Windoze 2000 Pentium II 400 with 768MB of
memory and three 7200 rpm SCSI hard drives.

As you may recall, from this, and other threads, I pointed out that
PageSetup was very slow , and the best that I could do was to split the
burden out of the Load event and display a splash screen at an appropriate
time. Typically, the PageSetup stuff took 8-9 seconds to run on the PII.

I put in a more accurate timer and the last time I ran on the PII, the time
was 8516 milliseconds.
At end of May, I purchased a Vista notebook with 4GB of memory, a 5400rpm
drive, and a 21.GHz Intel Centrino processor using Intel Core 2 Duo
Processor T8100.

I needed that to use Office 2007, and I figured that it would beat the crap
out of the PII speedwise.

Well, I finally got around to installing Office Ultimate 2007 today.

The Pagesetup stuff used 6085 milliseconds on a computer that is several
times faster than a PII 400. Rather disappointing!

I am using a .exe that was compiled with VB 6 and Excel 2003.
Maybe would be faster if I compiled with Excel 2007 and VB 6?
If so, then I may need to have a separate .exe for use with Excel 2007, not
a desired goal,
 
H

Howard Kaikow

I am using a .exe that was compiled with VB 6 and Excel 2003.
Maybe would be faster if I compiled with Excel 2007 and VB 6?
If so, then I may need to have a separate .exe for use with Excel 2007, not
a desired goal,
Click to expand...

Recompiling in Vista with VB 6 and Excel 2007 indeed makes a difference.

Using the Vista compile with VB 6 and Excel 2007, it took 868 milliseconds
to load Excel 2007, and 3134 milliseconds to do the Pagesetup stuff.

Using that same .exe, but running in Windows 2000 with Excel 2003, it took
3134 milliseconds to load Excel 2003, and 7731 milliseconds to do the
Pagesetup stuff.

since early binding is used, I was surprised that the Excel 2007 .exe even
worked in Excel 2003
Is this documented, or was I just lucky?
 

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

Page Breaks in VBA Q 6
PageSetup Performance Question 1
Print area - selection 4
Page setup error after moving sheet tabs 3
Define a Variable 1
Strange behavior with old macro now throws error with "Application 2
Print sub returns 4
Page setup too slow 2

Top