ilia
12/13/2006 2:58:00 PM
The macro recorder, besides listing unchanged parameters, also makes
extensive use of Select, Selection, ActiveSheet, ActiveWorkbook, and
the like. All those are less efficient than early-bound objects. So,
in fact, macro recorder is good at creating inefficient code. For
short macros that don't run very often, however, you won't see a
significant drop in performance.
Dave F wrote:
> Thanks.
>
> It seems that the macro recorder is more useful for learning syntax than it
> is for creating efficient code.
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "Jim Thomlinson" wrote:
>
> > The key is that when you record a macro if a dialog comes up with a bunch of
> > possible things that you could change the recorder does not determine which
> > items you actually changed. It just takes the shot gun approach and assumes
> > nothing making a line for every item that you could have changed... There are
> > a couple of places where you need to be careful such as when doing a Find
> > (you want to keep all of the parameters on that one as the parameters used
> > during a find are whtever thye were left at last)...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Dave F" wrote:
> >
> > > I actually cut out 95% of the lines and it works perfectly:
> > >
> > > Application.ScreenUpdating = False
> > > 'Set page set up for printing
> > > Range("A1:O26").Select
> > > Range("O26").Activate
> > > ActiveSheet.PageSetup.PrintArea = "$A$1:$O$26"
> > > With ActiveSheet.PageSetup
> > > .CenterHeader = "Summary: Depreciation + COF + One time expenses Per
> > > Project Per Month"
> > > .HeaderMargin = Application.InchesToPoints(0.73)
> > > End With
> > > Application.ScreenUpdating = True
> > >
> > > The only thing I modified was the header and the header margin.
> > > --
> > > Brevity is the soul of wit.
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Most of it actually. Any Selects and anything in a with are all areas where
> > > > you can tweak...
> > > >
> > > > With ActiveSheet.PageSetup
> > > > .PrintArea = "$A$1:$O$79"
> > > > .LeftMargin = Application.InchesToPoints(0.75)
> > > > .RightMargin = Application.InchesToPoints(0.75)
> > > > .TopMargin = Application.InchesToPoints(1)
> > > > .BottomMargin = Application.InchesToPoints(1)
> > > > .HeaderMargin = Application.InchesToPoints(0.73)
> > > > .FooterMargin = Application.InchesToPoints(0.5)
> > > > .CenterHeader = "COF: Per Project Per Month"
> > > > .Orientation = xlLandscape
> > > > End With
> > > > ActiveWindow.SelectedSheets.PrintPreview
> > > >
> > > > I hope I did not remove anything that should have stayed... If I did just
> > > > find the required line and put it back...
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Dave F" wrote:
> > > >
> > > > > Interesting.
> > > > >
> > > > > Here is the entirety of the code that the macro recorder spit out. What
> > > > > else can I eliminate?
> > > > >
> > > > > Range("A1:O79").Select
> > > > > Range("O79").Activate
> > > > > ActiveSheet.PageSetup.PrintArea = "$A$1:$O$79"
> > > > > With ActiveSheet.PageSetup
> > > > > .PrintTitleRows = ""
> > > > > .PrintTitleColumns = ""
> > > > > End With
> > > > > ActiveSheet.PageSetup.PrintArea = "$A$1:$O$79"
> > > > > With ActiveSheet.PageSetup
> > > > > .LeftHeader = ""
> > > > > .CenterHeader = ""
> > > > > .RightHeader = ""
> > > > > .LeftFooter = ""
> > > > > .CenterFooter = ""
> > > > > .RightFooter = ""
> > > > > .LeftMargin = Application.InchesToPoints(0.75)
> > > > > .RightMargin = Application.InchesToPoints(0.75)
> > > > > .TopMargin = Application.InchesToPoints(1)
> > > > > .BottomMargin = Application.InchesToPoints(1)
> > > > > .HeaderMargin = Application.InchesToPoints(0.5)
> > > > > .FooterMargin = Application.InchesToPoints(0.5)
> > > > > .PrintHeadings = False
> > > > > .PrintGridlines = False
> > > > > .PrintComments = xlPrintNoComments
> > > > > .PrintQuality = 600
> > > > > .CenterHorizontally = False
> > > > > .CenterVertically = False
> > > > > .Orientation = xlLandscape
> > > > > .Draft = False
> > > > > .PaperSize = xlPaperLetter
> > > > > .FirstPageNumber = xlAutomatic
> > > > > .Order = xlDownThenOver
> > > > > .BlackAndWhite = False
> > > > > .Zoom = False
> > > > > .FitToPagesWide = 1
> > > > > .FitToPagesTall = 1
> > > > > .PrintErrors = xlPrintErrorsDisplayed
> > > > > End With
> > > > > With ActiveSheet.PageSetup
> > > > > .LeftHeader = ""
> > > > > .CenterHeader = "COF: Per Project Per Month"
> > > > > .RightHeader = ""
> > > > > .LeftFooter = ""
> > > > > .CenterFooter = ""
> > > > > .RightFooter = ""
> > > > > .LeftMargin = Application.InchesToPoints(0.75)
> > > > > .RightMargin = Application.InchesToPoints(0.75)
> > > > > .TopMargin = Application.InchesToPoints(1)
> > > > > .BottomMargin = Application.InchesToPoints(1)
> > > > > .HeaderMargin = Application.InchesToPoints(0.5)
> > > > > .FooterMargin = Application.InchesToPoints(0.5)
> > > > > .PrintHeadings = False
> > > > > .PrintGridlines = False
> > > > > .PrintComments = xlPrintNoComments
> > > > > .PrintQuality = 600
> > > > > .CenterHorizontally = False
> > > > > .CenterVertically = False
> > > > > .Orientation = xlLandscape
> > > > > .Draft = False
> > > > > .PaperSize = xlPaperLetter
> > > > > .FirstPageNumber = xlAutomatic
> > > > > .Order = xlDownThenOver
> > > > > .BlackAndWhite = False
> > > > > .Zoom = False
> > > > > .FitToPagesWide = 1
> > > > > .FitToPagesTall = 1
> > > > > .PrintErrors = xlPrintErrorsDisplayed
> > > > > End With
> > > > > With ActiveSheet.PageSetup
> > > > > .LeftMargin = Application.InchesToPoints()
> > > > > .RightMargin = Application.InchesToPoints()
> > > > > .TopMargin = Application.InchesToPoints()
> > > > > .BottomMargin = Application.InchesToPoints()
> > > > > .HeaderMargin = Application.InchesToPoints(0.73)
> > > > > .FooterMargin = Application.InchesToPoints()
> > > > > .Zoom = False
> > > > > .FitToPagesWide = 1
> > > > > .FitToPagesTall = 1
> > > > > .PrintErrors = 12504
> > > > > End With
> > > > > ActiveWindow.SelectedSheets.PrintPreview
> > > > >
> > > > > --
> > > > > Brevity is the soul of wit.
> > > > >
> > > > >
> > > > > "Jim Thomlinson" wrote:
> > > > >
> > > > > > One thing to note about recorded macros is that they write a lot of
> > > > > > unnecessary junk. Assuming that the only thing you changed is the one margin
> > > > > > your macro could be shortened to ...
> > > > > >
> > > > > > ActiveSheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.73)
> > > > > >
> > > > > > As for why that got recorded that way I couldn't tell you... I don't record
> > > > > > too much these days...
> > > > > > --
> > > > > > HTH...
> > > > > >
> > > > > > Jim Thomlinson
> > > > > >
> > > > > >
> > > > > > "Dave F" wrote:
> > > > > >
> > > > > > > I got the above error when I tried to run a macro. The error occurs in the
> > > > > > > following block of code:
> > > > > > >
> > > > > > > With ActiveSheet.PageSetup
> > > > > > > .LeftMargin = Application.InchesToPoints()
> > > > > > > .RightMargin = Application.InchesToPoints()
> > > > > > > .TopMargin = Application.InchesToPoints()
> > > > > > > .BottomMargin = Application.InchesToPoints()
> > > > > > > .HeaderMargin = Application.InchesToPoints(0.73)
> > > > > > > .FooterMargin = Application.InchesToPoints()
> > > > > > > .Zoom = False
> > > > > > > .FitToPagesWide = 1
> > > > > > > .FitToPagesTall = 1
> > > > > > > .PrintErrors = 12504
> > > > > > > End With
> > > > > > >
> > > > > > > I generated this code via the macro recorder, and, as you can see, the only
> > > > > > > margin whose value I changed from the default was the header margin. From
> > > > > > > the VBA help files it appears that these margins require a measurement (i.e.,
> > > > > > > the measurement is not an optional parameter). So, two questions: (1) how do
> > > > > > > I know what value to enter for this required parameter, and (2) why didn't
> > > > > > > the macro recorder enter values for any of these arguments, except for the
> > > > > > > header margin?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > Dave
> > > > > > > --
> > > > > > > Brevity is the soul of wit.