<b>Excel Pagesetup property causing performance problem</b> 2

[anuktac]

I have a VBA Excel application (Office 97) that uses a function repetitively maybe 12-13 times. This function uses the pagesetup property to format the pages. I have noticed that it is very slow, and the time taken to run the macro cumulatively adds up. When I use the pagesetup property, it takes 12.5 mins to run the macro, and without it only 2.5 mins.

This is the exact syntax used within the function:
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With

Is there an alternative to using pagesetup? Any other way this could have been written?
I am new to VBA, would be grateful for any help.
-Anukta

 

[xlbo]

What do you actually need to use this page setup function for ??
Seems like you have all arguments of the pagesetup function there but the only one I see that is different from default is the orientation - I'd try and strip out any of the setup properties that you do not actually need to change - should speed things up... Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[anuktac]

Actually I have not written this code, it has been written by someone else originally. I am maintaining it now.
I think this function is used to tidy up the page, removing gridlines, header, footers etc, for printing purposes.

So all these arguments are the defaults in 97?

-Anukta

 

[rmikesmith]

Anukta,

I can't tell for sure from the code you've posted, but if your application is running the same code repeatedly (12-13 times?) and it includes this PageSetup snippet, you are incurring a lot of unnecessary overhead. Separate this from the repeatedly called segment of code and run it only once, at the beginning.


HTH
Mike

 

[anuktac]

Actually there are around 17-18 worksheets that get built up through this macro. This function gets called for most of them, for removing gridlines and removing margins. So I suppose that function needs to get called after all for each of these worksheets. But I used Xlbo's suggestion, and eliminated some of the defaults.
That speeded up things a bit, while the page still looks the same (which is what I wanted).

I am now using

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.PrintGridlines = False
.Orientation = xlLandscape
End With
That made it a little faster. Is there a way around to do the same thing without using the pagesetup function?

I was reading about the performance impacts of the pagesetup function, there are some interesting articles available.

http://p2p.wrox.com/archive/vba_excel/2002-02/28.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192869&gssnb=1

-Anukta

 

[rmikesmith]

Anukta,

The only way to avoid using the PageSetup properties is if you can set all options at design-time. Is this possible with your 17-18 worksheets?


Mike

 

[anuktac]

Mike,
The way this macro works is, it takes 5 text files as input, and using them builds a workbook containing 17-18 worksheets. The macro is called by the Autpen function.

I don't know what you mean by setting the properties at design time. If there is a way the options can be set at the beginning which will remain enabled workbook-wide (for the sheets already existing and sheets yet to be built), nothing like it.

Is it possible to do that? How?

If this can be done, then I am the happiest woman on earth !!!
-Anukta

 

[xlbo]

Unfortunately, I don't think there's any way apart from pagesetup to do this.

I may be teaching granny to suck eggs but do you have
application.screenupdating = false
anywhere in the macro ???
if you havn't, I suggest that you enter that line as it tends to speed things up a lot Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[anuktac]

Application.screenupdating = false in my application.
Right then, I will swallow the bitter cup, and use pagesetup. Actually eliminating some of the defaults I have got the timing down to 4 min from 14min.

If there is no better solution, then I am giving Xlbo a star.

-Anukta

 

[rmikesmith]

Anukta,

All I meant was if you could set all the Page Setup options manually for each worksheet then save this you wouldn't need to access the PageSetup properties via code. This could also take the form of a single worksheet that serves as a template and subsequently gets copied by your macro to generate the 17-18 worksheets. Of course, this will only work if the required options remain constant.


Mike

 

[anuktac]

I see what you mean. I will try this out. Though this will mean quite a big change in my code.
Thanks.

-Anukta

 

[rmikesmith]

Anukta,

Ahh, a classic case of trading the coder's time for the users' time. With the time-to-completion you've indicated, it's probably worth it!


Regards,
Mike

 

[combo]

There are only two solutions:
1. manual setting (as it was mentioned above),
2. upgrading to 2k or higher version.
There is a huge lost of availible memory while using PageSetup in excel 97. After code execution the memory will not free. Formatting few pages with older computers will freeze the computer.

 

[anuktac]

nah....setting up the pagesetup property at the beginning cannot be done, because pivot tables are widely used in this application. I found that whenever a pivot-table is created, it opens a new worksheet (instead of any existing sheet). If a new worksheet is opened everytime, then this is not possible.
I am a little mystified though. While creating a pivot-table, there IS actually an option for creating it on an existing sheet. But even if this option is selected, it still does the same thing.
-Anukta

 

[Nikita6003]

Anukta - dunno if this'll help but when I use the PivotTableWizard in xl VBA I set the

TableDestination

parameter to wherever I want the table to appear. Might help save time to put >1 Pivottable on 1 sheet ...

Cheers
Nikki