Bizarre slowing down of macro

Posted by Tamsin on August 08, 2001 8:28 AM

Hello everyone,

I have been working on a new reporting application for my company. I'm now just polishing it off, testing, etc. One of the things I wanted to do was for the reports to always come out on one page (the report size changes). I used this sort of code:-

Sub Test()
t = Timer
With sht1
.PageSetup.Zoom = False
.PageSetup.Orientation = xlPortrait
.PageSetup.PrintArea = .UsedRange.Address
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.Protect
End With
With sht2
.PageSetup.Zoom = False
.PageSetup.Orientation = xlLandscape
.PageSetup.PrintArea = .UsedRange.Address
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.Protect
End With
MsgBox Timer - t

End Sub

Now when I run this on my (not completely ancient) 500mhz machine it takes around 5 seconds. I'm creating about 20 reports so that's nearly a minute just to set the print area when the rest of the macro runs in about 2 seconds.

Does anyone know why this takes so long and/or how I can get around this?

Thanks in advance,
Tamsin.

Posted by Gregc on August 08, 2001 9:38 AM

You can try puting Application.screenupdating = false
at the beginning of the macro, that may help. Most of the macros that I have that have sheet formatting on them tend to run slow and I can't make them run any faster.

Posted by Damon Ostrander on August 08, 2001 10:09 AM

Hi Tamsin,

This is one of those little-known issues that the Office documentation does not adequately cover. It took me quite a while to uncover the source of this problem when it happened to me. In my case, there was nothing I could do about it, but your situation looks like it could be improved.

The slowness is caused by the fact that each time you edit a PageSetup object property, Excel actually checks with the printer driver to see if it supports the property. The printer driver often has to check with the printer and wait for a response, which, if it is a network printer can take some time. Because of this, the only way to reduce the time is to reduce the number of references to PageSetup object properties in your program. For example, if you are setting a PageSetup property to a value that is already its default, eliminate it. In your case, it looks like you can reduce the number of PageSetup references by setting all the properties that are common to all sheets in one step, then setting the unique properties separately. In your example, the only property that is different between the two sheets is the Orientation. It would look like this (if you were setting it for worksheets 1 and 2):

With Worksheets(Array(1,2))
.PageSetup.Zoom = False
.PageSetup.PrintArea = .UsedRange.Address
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.Protect
End With
sht1.PageSetup.Orientation = xlPortrait
sht2.PageSetup.Orientation = xlLandscape

The With statement now sets a collection of worksheets as the qualifier for the PageSetup object.

Good luck.

Damon

Posted by Tamsin on August 08, 2001 3:10 PM

Damon,

Once again you have provided an experts answer. What you say makes perfect sense. The company I work for has good systems generally but certain things (including the network printers) are very slow. I will try your method tomorrow and keep my fingers crossed.

Regards,
Tamsin.