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

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:
1. On the File menu, click Print Preview.
2. In the print preview window, click Setup.
3. Click the Margins tab.
4. To keep the current margins, click OK. Or, change the margin values to the measurements you want and then click OK.
5. 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:
1. On the File menu, click Page Setup.
2. Click the Margins tab.
3. 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.

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.