Excel: Macro Errors When Scaling Output with PAGE.SETUP()

ID: Q90953

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY

In Microsoft Excel, the PAGE.SETUP() macro command must be entered as an array when referencing a defined name or a range of cells for the Scale argument and when fitting the printout to a specific number of pages.

MORE INFORMATION

The PAGE.SETUP() macro command allows Page Setup options to be set within a macro. One of the options that can be set is scaling. You can choose to reduce or enlarge by a specific percentage or to fit to a certain number of pages.

Online Help in version 5.0 and page 313 of the "Microsoft Excel Function Reference," version 4.0, state that you can set the Scale argument to a two- item horizontal array. The PAGE.SETUP() command does not have to be entered as an array if the following syntax is used:


   =PAGE.SETUP(,,,,,,,,,,,,{2,1})

Note: The above command scales the printout to fit to two pages wide by one page tall. This is the example that will be used for the remainder of this article.

If using a defined name that is defined as either ={2,1} or =$A$1:$B$1 (where cells $A$1 and $B$1 contain the values 2 and 1 respectively), the PAGE.SETUP() command must be entered as an array by pressing CTRL+SHIFT+ENTER or using the FORMULA.ARRAY command.

The following are two examples that must be entered as an array:

Example 1

=PAGE.SETUP(,,,,,,,,,,,,scale)

where "scale" is a defined name that refers to either

={2,1} or =$A$1:$B$1

Example 2

=PAGE.SETUP(,,,,,,,,,,,,A1:B1)

When using a defined name that refers to a two-item horizontal array (as in Example 1), if PAGE.SETUP() is not entered as an array, the following error message appears:


   Number must be between 10 and 400

When using a defined name that refers to cell references (as in Example 2), if PAGE.SETUP() is not entered as an array, the following error message appears:


   Macro error at cell ___

REFERENCES

"Online Help," version 5.0
"Microsoft Excel Function Reference," version 4.0, pages 312-314
"Microsoft Excel User's Guide 1," version 4.0, pages 153-166

Additional query words: 5.0

Keywords :
Version :
Platform :
Issue type :