Microsoft KB Archive/213674

= XL2000: "Compile Error" Running Macro That Changes Margins in Print Preview =

Article ID: 213674

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213674





SYMPTOMS
When you run a recorded Microsoft Visual Basic for Applications macro that attempts to modify the margins of your worksheet in print preview, you may receive the following error message:

Compile error:

Argument not optional



CAUSE
This problem occurs if you change at least one margin in the print preview window, while you are recording the macro. Any margins that you do not change are recorded without a value. For example, if you only drag the left margin to 0.5 inch in the print preview window, the macro recorder records code similar to the following: .LeftMargin = Application.InchesToPoints(.5) .RightMargin = Application.InchesToPoints .TopMargin = Application.InchesToPoints .BottomMargin = Application.InchesToPoints .HeaderMargin = Application.InchesToPoints .FooterMargin = Application.InchesToPoints When you run the macro, you receive a compile error because only one line of code that applies to the page margin has a value; the remainder of the code that applies to the margin settings don't contain any values. In order for the code to work correctly, a value must appear inside the parentheses for each line of code referencing the margin setting.



WORKAROUND
To work around this behavior, use the appropriate method for your situation.

Method 1: If You Already Recorded the Macro
If you already recorded the macro, you can remove, comment, or modify the code so that you won't receive the error message:
 * Edit the macro and remove the lines of code that contain Application.InchesToPoints without a value inside the parentheses.

-or- Edit the macro and comment any lines of code that contain Application.InchesToPoints without a value inside the parentheses. To comment a line of code, type an apostrophe (') character at the beginning of that line of code.

-or- Specify a value for each line of code that contains Application.InchesToPoints without a value inside the parentheses.

Method 2: If You Haven't Already Recorded the Macro
If you haven't already recorded the macro or if you want to re-record it, you can prevent this error from occurring by recording the settings from the Page Setup dialog box. This method will record a value for each line of code that pertains to a margin. To record the margin settings from the Page Setup dialog box in print preview, follow these steps:  On the Tools menu, point to Macro and then click Record New Macro. Give your macro a name and click OK. Record the following procedure:  On the File menu, click Print Preview. In the print preview window, click Setup. Click the Margins tab.</li> To keep the current margins, click OK. Or, change the margin values to the measurements you want and then click OK.</li> Click Close.</li></ol> </li> On the Tools menu, point to Macro and then click Stop Recording.</li></ol>

NOTE: You can also record the margin settings without first going to print preview. To do this, follow these steps: <ol> On the Tools menu, point to Macro and then click Record New Macro.</li> Give your macro a name and click OK.</li> Record the following procedure: <ol style="list-style-type: lower-alpha;"> On the File menu, click Page Setup.</li> Click the Margins tab.</li> To keep the current margins, click OK. Or, change the margin values to the measurements you want and then click OK.</li></ol> </li> On the Tools menu, point to Macro and then click Stop Recording.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. If you use the Page Setup dialog box to change margins in a worksheet while recording a macro, code similar to the following is recorded: .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) Note that each set of parentheses contains a value.

If you use the Margins button in print preview to change margins, only margins that you change contain a value in the parentheses. For example, if you only change the left margin in print preview, your code will look similar to the following: .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints .TopMargin = Application.InchesToPoints .BottomMargin = Application.InchesToPoints .HeaderMargin = Application.InchesToPoints .FooterMargin = Application.InchesToPoints If you try to run this code, you will receive a compile error.

<div class="references_section">