speed up macros
 

Is there any way to speed up procedures that change Print Setup attributes?
The entire procedure is:

Sub DavesPageSetup()
With ActiveSheet.PageSetup
.LeftHeader = "&8&F"
'...15 total attributes
End With
End Sub

That's it, and yet it still takes 30+ seconds. My other macros are fast but
whenever I do something that involves the printer it's about as fast as a
crippled turtle. Anyone know how to make it run faster? (I have an HP 842C
Deskjet if that helps.) Thanks.

speed up macros
 

Hi Dave

I think the "PageSetup" within Excel is notoriously slow. The following
suggestions won't help with your current dilemma but may be useful in the
future to reduce the amount of code required and therefore reduce the
execution time of a macro.

1) Preformat the headers / footers / margins / orientation, etc of all
worksheets in a workbook so the whole page setup process does not have to be
repeated each time you go to print. With a bit of luck the only thing that
will need to be amended via code on each sheet is the print range.
Regardless, I think its quicker to not display page breaks and set the zoom
to 100 before you "ActiveSheet.PageSetup.....", etc

2) Keep a preformatted blank worksheet with the required print settings
within the file. Instead of adding a new sheet using "Sheets.Add", copy the
blank sheet within the workbook so the print settings are already there.

3) Create a one sheet file with the print settings already set up. Name the
file whatever you want but save it as a template, say, "MySheet.xlt". When
you want to add a new sheet to the file, rather than using
"ThisWorkbook.Sheets.Add" use something like the following which will
hopefully ensure that most of the print settings have been dealt with.
"ThisWorkbook.Sheets.Add Type:="C:\MyFolder\MySubFolder\MySheet.xlt"

--
XL2002
Regards

William



"Dave B" wrote in message
...
| Is there any way to speed up procedures that change Print Setup
attributes?
| The entire procedure is:
|
| Sub DavesPageSetup()
| With ActiveSheet.PageSetup
| .LeftHeader = "&8&F"
| '...15 total attributes
| End With
| End Sub
|
| That's it, and yet it still takes 30+ seconds. My other macros are fast
but
| whenever I do something that involves the printer it's about as fast as a
| crippled turtle. Anyone know how to make it run faster? (I have an HP
842C
| Deskjet if that helps.) Thanks.
|
|

speed up macros
 

Dave, the trick is to only change the pagesetup properties that need
changing. For example:

if .LeftHeader < "&8&F" then .LeftHeader = "&8&F"

This will make the code fly as checking a property is lightning fast, and
you only change the ones you need to change.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"Dave B" wrote in message
...
Is there any way to speed up procedures that change Print Setup
attributes?
The entire procedure is:

Sub DavesPageSetup()
With ActiveSheet.PageSetup
.LeftHeader = "&8&F"
'...15 total attributes
End With
End Sub

That's it, and yet it still takes 30+ seconds. My other macros are fast
but
whenever I do something that involves the printer it's about as fast as a
crippled turtle. Anyone know how to make it run faster? (I have an HP
842C
Deskjet if that helps.) Thanks.

speed up macros
 

That trick works if you're not sure you need to set the property
(otherwise it's no faster than just setting the property).

You can get significantly improved performance over individual VBA
commands using XL4M, especially if you have multiple .pagesetup
properties to set:


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




In article ,
"Bob Flanagan" wrote:

Dave, the trick is to only change the pagesetup properties that need
changing. For example:

if .LeftHeader < "&8&F" then .LeftHeader = "&8&F"

This will make the code fly as checking a property is lightning fast, and
you only change the ones you need to change.

speed up macros
 

Also, after you make your pagesetup changes, hide the display of pagebreaks
if you have more code to run.

ActiveSheet.DisplayPageBreaks = False

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
Is there any way to speed up procedures that change Print Setup
attributes?
The entire procedure is:

Sub DavesPageSetup()
With ActiveSheet.PageSetup
.LeftHeader = "&8&F"
'...15 total attributes
End With
End Sub

That's it, and yet it still takes 30+ seconds. My other macros are fast
but
whenever I do something that involves the printer it's about as fast as a
crippled turtle. Anyone know how to make it run faster? (I have an HP
842C
Deskjet if that helps.) Thanks.