Why is PageSetup so slooooow?

[idbr]

Hi,

Does anyone know why updating the page setup for Excel worksheets via VBA from an Access module is so slow? As an example, all I'm setting is:

with appXL
...

'select the data range
.Cells(1 + frm.txtAddRows.Value, 1 + frm.txtAddCols.Value).Select
.Range(appXL.Selection, appXL.ActiveCell.SpecialCells(xlLastCell)).Select

'apply the print formatting
.ActiveSheet.PageSetup.PrintArea = appXL.Selection.Address
With .ActiveSheet.PageSetup
    .LeftHeader = "Deloitte & Touche LLP"
    .CenterHeader = lbx.ItemData(intListItems)
    .RightHeader = Format(Int(Now()), "dd-mmm-yyyy")
    .LeftFooter = "Confidential"
    .CenterHorizontally = True
    If appXL.Selection.Width > appXL.Selection.Height Then
    .Orientation = xlLandscape
    Else
    .Orientation = xlPortrait
    End If
    .PaperSize = xlPaperA4
    .Order = xlDownThenOver
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 100
End With

...
End With

Yet this is averaging about 4-5 seconds per worksheet. This is part of an auomation routine that could be reformatting 10s or even 100s of sheets, so speed is an issue.

Any advice would be greatly appreciated.

Thanks, Iain

 

[AnotherHiggins]

Step through the code by repeatedly pressing [F8], pausing after every press to see how long each line of code takes. This will allow you to see how long each individual step takes, and thereby pinpoint the problem.

I suspect

    .CenterHeader = lbx.ItemData(intListItems)

If that is the problem, and it is the same for every single sheet, then early in the code before you start formatting sheets you could do something like

    AnyVariableName = lbx.ItemData(intListItems)

Then for each sheet use

    .CenterHeader = AnyVariableName

so you aren't finding the list again for every single sheet.

Also, here are a few general suggestions that might help:

[tab]Make use of WITH
You can take the PrintArea line down into the WITH statement. I hate how macro recorder breaks that step out.

    With .ActiveSheet.PageSetup
        .PrintArea = appXL.Selection.Address

Or, if there aren't any unprinted cells on the sheet, just leave that line out altogether.

[tab]Avoid Using .Select And .Activate Whenever Possible
I notice that you are using Selection.Address for the print area. Better to just refer to the populated range.

[tab]Avoid Looping Whenever Possible
I imagine you are looping through the sheets. Not much of a way around that, unfortunately. But try not to loop through cells in a range as there is often a better way.

[tab]Disable Events That Take Time
At the minimum, you'll want to wrap your existing code with

    Application.ScreenUpdating = False
    ...
    'Your Code
    ...
    Application.ScreenUpdating = True

That stops the flickering you see while the macro is running.

Depending on how your code is written, you might also want to use:

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

One of our members wrote this little sub that can be called at the called at the beginning and end of any other macro:

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

I have since used the following modified code which also sets calculation to manual;

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
'Adapted by AnotherHiggins
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
        If blnState Then .Calculation = xlCalculationAutomatic
        If Not blnState Then .Calculation = xlCalculationManual
    End With
End Sub

So your macro would be wrapped up to look like this:

    Call ToggleEvents(False)
    ...
    'Your Code
    ...
    Call ToggleEvents(True)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"Avoid Looping Whenever Possible"

Do all the sheets have the same setup? Select ALL the sheets and do ONE setup.

Even if they are not all completely identical, find the properties that ARE identical on all sheets. Select all and assign THESE properties.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Pardon the use of SELECT. Rather REFERENCE all the sheets, and assign the common properties.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Skip - You've got my hopes up here. How do you change PageSetup properties for more than one sheet at a time? Changing manually is easy - just select all sheets and off you go. But with code....

I remember a few years back looking for a solution and reading that it was a known problem with Excel and that you cannot (or could not) do it.

Has something changed?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Looks like it hasn't:[tab]

http://support.microsoft.com/kb/213257

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hmmmmmm....

I must have been sleeping.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

OK, does this work for everyone???

Sub Macro1()
    Dim a, i As Integer
'
    ReDim a(ThisWorkbook.Worksheets.Count - 1)
    For i = 0 To UBound(a)
        a(i) = Worksheets(i + 1).Name
    Next
    Macro2 a
End Sub
Sub Macro2(sh)
'
    Sheets(sh).Select
    Sheets(sh(0)).Activate
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HughLerwill]

In the begining use a Workbook template (.xlt) which contains a single sheet formatted just the way you like and copy that sheet into however many new ones you need. This avoids use of .PageSetup in VBA code which can be slow.

 

[xlbo]

To be honest - pagesetup is nearly always slow - not because of excel but because it has to go and talk to the printer - that is almost always where the delay occurs

your best bet for speedup is therefore making as few calls to the printer as possible and/or HughLerwill's suggestion of using templates that are pre formatted

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lmbayley]

Okay, so here's my two thoughts on the matter.

First, whenever I know I have to have the same formatting on a bunch of sheets, I'll put that formatting into my Workbook_BeforePrint sub. To make it so that it doesn't go super-slow each time I print-preview a document, I only tell it to format a setting the way I want if the setting isn't already that way. Like this:

With sh.PageSetup
        If .RightHeader <> "TEST PRINT" Then .RightHeader = "TEST PRINT"
End With

Now, about selecting a bunch of sheets at once and have the same formatting apply for all. Here's what I just tried, and it worked for me:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    For Each sh In ThisWorkbook.Windows(1).SelectedSheets
        With sh.PageSetup
            If .RightHeader <> "TEST PRINT" Then .RightHeader = "TEST PRINT"
        End With
    
    Next
End sub

So, idbr, can you set something like this up in your BeforePrint, select all your sheets at once, and hit the Print Preview button to get them all formatted?

-LB

 

[SkipVought]

"about selecting a bunch of sheets at once and have the same formatting apply for all..."

Do I hear an ECHO echo [sup]ECHO echo[/sup] [sub]ECHO echo[/sub]?????

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lmbayley]

Skip,

I was honestly trying to contribute to this forum. Did you really need to slam me like that?

I've only been programming for a couple of years, so maybe I don't know the difference. But it looked to me like my way of working through all the sheets was different from your way. You appear to be selecting each sheet in turn. My method allowed the user to select all the sheets *first* and then have the macro working through just the selected sheets. Call me crazy, but I thought my idea might be worth considering.

-LB

 

[lmbayley]

Sigh.

Skip, now I see which post you were referring to. When I read your idea the first time, it didn't seem to say the same thing as what I was thinking, probably because you said it in words, rather than code. I apologize for my snarky response in the second paragraph of my previous post.

I still, however, stand by the first paragraph of my previous post. People should be encouraged to contribute, not just lurk about and wait for the mvp's to magically provide all the answers. Ridiculing people who fall short of the mark is not going to encourage them to try again.

-LB

 

[SkipVought]

My point being, when you post, ADD to the content.

I welcome your contributions as well as others.

I also view MANY threads that I never post in, because other have very adequately, and in many cases eloquently, done the job, and often better than I could.

I appologize if it offended you. The comment was framed to be a light-hearted jab, rather than a slam.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[idbr]

Hi all,

Apologies for the slow response, as tends to happen, got pulled onto something else.

Some fair points on all of these posts. Dealing with each of the core issues:

Assigning form refences to variables. Check. Plus smack round side of head for not doing this properly in the first place.
Turning off screen updates. Check. Even though running from Access via invisible Excel.Application object I still think the 'screen' gets updated.
Turning off display alerts. Not check, need these for a couple of reasons.
Selecting all sheets then applying common formats. Not check, could be working with some sheets in the book and not others, which involves a call back to my listbox to work out which is which, then still a call to PageSetup for the non-common items, so works out less efficient and makes the code hard to follow. Good bit of code though Skip, which I'm sure might be useful for something else.

The key point for me was Geoff's - .PageSetup does need to call the printer. Which is sloooow. So digging around brought me to Application.ExecuteExcel4Macro. This offers access to the print setup without having to make a call to the printer.

Now this is old tech, admitted. However, from a couple of articles on the Microsoft website, it seems that MS (whilst not supporting it, or documenting it within modern versions of Excel) are not planning to abrogate this anytime soon as they still have plenty of clients using old .xlm code that they don't want to have to re-write. So I'm using that.

There is a help file documenting Excel4 macros here...

Thanks to all for contributions. I'd be interested in opinions on the solution.

Regards, Iain

 

[xlbo]

some of those old excel4 macros are VERY useful! If it works and is faster ,then it's a good solution!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question