Compile error: Argument not optional
 

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.