Info

Sub Macro1() 1 Macrol Macro

' Macro recorded 12/2/2003 by Juan Pablo Gonzalez

With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = ""

End With

ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""

.LeftMargin = Application.InchesToPoints(1.5) .RightMargin = Application.InchesToPoints(1.5) .TopMargin = Application.InchesToPoints(1.5) .BottomMargin = Application.InchesToPoints(1.5) .HeaderMargin = Application.InchesToPoints(1) .FooterMargin = Application.InchesToPoints(1) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 End With End Sub

The macro recorder is doing a lot of extra work, which requires extra processing time. Considering that, plus the fact that the PageSetup object is one of the slowest objects to update, and you can have quite a mess...so, a cleaner version (that uses just the delete key!) follows:

Sub Macro1_Version2()

With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(1.5) .RightMargin = Application.InchesToPoints(1.5) .TopMargin = Application.InchesToPoints(1.5) .BottomMargin = Application.InchesToPoints(1.5) .HeaderMargin = Application.InchesToPoints(1) .FooterMargin = Application.InchesToPoints(1) End With End Sub

Okay, this runs faster than Macrol (the average reduction is around 70% on some simple tests!), but it can be improved even further. As noted earlier, the PageSetup object takes a long time to process, so if you reduce the number of operations that VBA has to make and include some IF functions to update only the properties which require changing, you can get much better results.

0 0

Post a comment