Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
need for speed! | Excel Programming | |||
Macros i XL 2003 versus 2000 - speed problems | Excel Programming | |||
Why 2 macros differ so much in speed? | Excel Programming | |||
Speed? | Excel Programming |