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

ID: Q213674

The information in this article applies to:

Microsoft Excel 2000

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.

To keep the current margins, click OK. Or, change the margin values to the measurements you want and then click OK.

Click Close.

On the Tools menu, point to Macro and then click Stop Recording.

NOTE: You can also record the margin settings without first going to print preview. To do this, 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 Page Setup.

Click the Margins tab.

To keep the current margins, click OK. Or, change the margin values to the measurements you want and then click OK.

On the Tools menu, point to Macro and then click Stop Recording.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

REFERENCES

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q226118 OFF2000: Programming Resources for Visual Basic for Applications

Additional query words: XL2000

Keywords : kbmacro kbprg kbdta kbdtacode KbVBA xlprint
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug