Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error Argument Not optional | Excel Discussion (Misc queries) | |||
Argument not optional Error 449! Need Help | Excel Programming | |||
Compile Error: Argument not optional | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
Complie Error- Argument not optional | Excel Programming |