VBA, Settings, excel,wps, page Setup

Fully understand the PageSetup object of page Setup
When we write VBA code, especially when we do small program development, we often use VBA to set
The options in the page setup, you can also use VBA to achieve some special effects, which requires the
Use the Pagesteup object. The PageSetup object contains properties for all page settings (left margin, bottom edge
Paper size, etc.). There are 49 properties for this object, where: 8 properties are EXCEL2007 newly added
Added, 1 properties were removed in 2007.
Below I follow the page, margins, header/footer, worksheet, and no corresponding tabs.
Five categories to describe each of these 49 attributes:
One, there are 7 properties corresponding to the page tab. respectively: PrintQuality, Orientation,
FirstPageNumber, Zoom, FitToPagesWide, FitToPagesTall, PaperSize.
1. PrintQuality: Returns or sets the print quality. Variant type, readable and writable. This property is associated with the print
The quality option corresponds.
Worksheets ("Sheet1"). pagesetup.printquality = Array (240, 140) ' Specifies the horizontal and
Print quality in vertical direction
Worksheets ("Sheet1"). Pagesetup.printquality (1) =600 ' Specify print quality in horizontal direction
Worksheets ("Sheet1"). Pagesetup.printquality (2) =600 ' Specify print quality in the vertical direction
The above example is used in the process of whether the error occurs depends on the printer driver used, because the different hit
Printing machine can set the quality is different.
2. Orientation: Returns or sets a XlPageOrientation value that represents the portrait or landscape print mode
Expression This property corresponds to the orientation option.
Worksheets ("Sheet1"). Pagesetup.orientation = Xllandscape ' Set mode to print mode
Worksheets ("Sheet1"). Pagesetup.orientation = xlportrait ' Set portrait print mode
3. FirstPageNumber: Returns or sets the page number of the first page when the specified sheet is printed. If set to
Xlautomatic, the MicrosoftExcel takes the page number of the first page. The default value is xlautomatic. Long
Type, readable and writable. This property corresponds to the Start Page number option.
You can set arbitrary data as needed so that the printed report does not start on the first page.
4. Zoom: Returns or sets a Variant value that represents a value between 10% and 400%
The percentage is the scale at which MicrosoftExcel prints the worksheet. This property is associated with the zoom ratio
Example "option corresponds.
Do not enter a percent sign (%) when entering, just enter the numeric value to be able.
5. FitToPagesWide: Returns or sets the width of the page used to scale the sheet when the worksheet is printed.
Applies only to worksheets. Variant type, readable and writable. This property corresponds to the page height option.
6. FitToPagesTall: Returns or sets the height of the page used to scale the sheet when the sheet is printed.
Applies only to worksheets. Variant type, readable and writable. This property corresponds to the page width option.
The FitToPagesWide and FitToPagesTall properties only work if the Zoom property value is False.
Please be careful when you use it.
7. PaperSize: Returns or sets the size of the paper. XlPaperSize type, readable and writable. This property is associated with the paper
The size of the sheet option corresponds.
There are 8 properties corresponding to the Margins tab. respectively: CenterVertically,
CenterHorizontally, TopMargin, BottomMargin, FooterMargin, HeaderMargin,
RightMargin, LeftMargin
1. CenterVertically: If the specified sheet is printed in the vertical center of the page, the property value is
True. A Boolean type that can be read and written. This property corresponds to the vertical option.
Worksheets ("Sheet1"). PageSetup. centervertically = True to set the worksheet to vertical
In
2. CenterHorizontally: If the specified sheet is printed in the horizontal center of the page, the property
The value is True. A Boolean type that can be read and written. This property corresponds to the horizontal option.
Worksheets ("Sheet1"). pagesetup.centerhorizontally = True ' Set Worksheet to Horizontal
Center.
3. TopMargin: Returns or sets the size of the top margin, measured in points. Double type, readable and writable.
This property corresponds to the UP option.
4. BottomMargin: Returns or sets the size of the bottom margin, measured in points. Double type, readable
Write. This property corresponds to the "Down" option.
5. FooterMargin: Returns or sets the distance, in points, of the footer to the bottom of the page. Double type,
can read and write. This property corresponds to the footer option.
6. HeaderMargin: Returns or sets the distance from the top of the page to the header, in points. Double type,
can read and write. This property corresponds to the header option.
7. RightMargin: Returns or sets the size of the right margin, measured in points. Double type, readable and writable.
This property corresponds to the "right" option.
8. LeftMargin: Returns or sets the size of the left margin, measured in points. Double type, readable and writable.
This property corresponds to the left option.
I set the top margin of the page to 2 through the Page setup form, and note that the units here are "centimeters"
(That is, in the Margins tab, the data is in centimeters) and using the recording macro generates
The following code:
. TopMargin = application.inchestopoints (0.78740157480315)
This piece of code is not the code of our general thinking, why not. TopMargin = 2? Original
Because the units of these 6 properties are all "points", what is the unit of "points"? LBS: Refers to the printed
The unit of measure for the height of the character. 1 pounds equals 1/72 inches (i.e. 1 pickups), or about 1 centimeters
of 1/28.
So what unit is this "0.78740157480315"? The answer is: "inch". therefore need to enter
The function of the InchesToPoints method is to convert the unit of measure from inches to points.
Obviously this kind of writing does not conform to our countrymen's custom, converts "centimeters" into "inches", and then into "pounds",
It is not more convenient to convert "centimeters" directly into "pounds"! We can use centimeterstopoints side
The "centimeter" directly to the "pound" can be realized by the method. Code to write:. TopMargin
Application.centimeterstopoints (2).
Three, there are 16 properties corresponding to the Header/Footer tab. The following were:
OddAndEvenPagesHeaderFooter, DifferentFirstPageHeaderFooter,
Scalewithdocheaderfooter, Alignmarginsheaderfooter, RightFooter,
RightFooterPicture, CenterFooter, CenterFooterPicture, LeftFooter,
LeftFooterPicture, RightHeader, RightHeaderPicture, CenterHeader,
CenterHeaderPicture, LeftHeader, leftheaderpicture
1. OddAndEvenPagesHeaderFooter: If you specify an odd page and an even number of PageSetup objects
True if the page has a different header and footer. A readable/write Boolean type. This property is associated with the "odd and even page
Different "options correspond.
2. DifferentFirstPageHeaderFooter: If a different header or footer is used on the first page;
True. A readable/write Boolean type. This property corresponds to the "first page different" option.
If the property is TRUE, you cannot select the header/Footer style for the system settings. Need to use FirstPage
property to set the header/footer for the first page.
3. Scalewithdocheaderfooter: Returns or sets whether the header and footer are changed with the document size
Document scaling. A readable/write Boolean type. This property corresponds to the auto scale with document option.
4. Alignmarginsheaderfooter: If Excel aligns the page with the margins set in the page setup options
Eyebrow and footer, returns TRUE. A readable/write Boolean type. This property is aligned with the page margin alignment option.
Should.
5. RightFooter: Right-aligns the footer information in the PageSetup object. A read/write String type.
A read/write String type. This property corresponds to the custom footer-right option.
6. RightFooterPicture: Returns a Graphic object that represents the picture to the right of the footer, with
To set the properties of the picture. This property corresponds to the custom footer-right option.
7. CenterFooter: Center Aligns the footer information in the PageSetup object. A read/write String type.
This property corresponds to the custom footer-in option.
8, CenterFooterPicture: Returns a Graphic object that represents the middle part of the footer of the diagram
Chip The property used to set the picture. This property corresponds to the custom footer-right option.
9. LeftFooter: Left aligns the footer information in the PageSetup object. A read/write String type. This
The properties correspond to the custom footer-left option.
10. LeftFooterPicture: Returns a Graphic object that represents the picture to the left of the footer. Use
To set the properties of the picture. This property corresponds to the custom footer-left option.
11. RightHeader: Right-aligns the header information in the PageSetup object. A read/write String type.
This property corresponds to the custom header-right option.
12. RightHeaderPicture: Returns a Graphic object that represents the picture to the right of the header.
Properties for setting the picture ... This property corresponds to the custom header-right option.
13. CenterHeader: Center Aligns the header information in the PageSetup object. A read/write String type.
This property corresponds to the custom header-in option.
14, CenterHeaderPicture: Returns a Graphic object that represents the middle part of the header of the diagram
Chip The property used to set the picture. This property corresponds to the custom header-in option.
15. LeftHeader: Left aligns the header information in the PageSetup object. A read/write String type.
This property corresponds to the custom header-left option.
16. LeftHeaderPicture: Returns a Graphic object that represents the picture to the left of the header. Use
To set the properties of the picture. This property corresponds to the custom header-left option.
The above description of properties is not exactly the same as EXCEL's help, as some of the tests help say
There is a mistake.
There are 11 properties corresponding to the Sheet tab. respectively: Draft, PrintErrors,
BlackAndWhite, PrintHeadings, PrintComments, PrintNotes, PrintArea, Order,
PrintGridlines, PrintTitleRows, PrintTitleColumns.
1. Draft: True If the graphic is not printed when the worksheet is printed. Boolean
Type, readable and writable. This property corresponds to the print-Draft quality option.
Set this property to True to speed up printing (but not to print the graphics).
2. PrintErrors: Sets or returns a XlPrintErrors constant that specifies the print error displayed
Type. This feature allows users to suppress the error display when they print a worksheet. can read and write. This property is associated with the print-
Error cell print as "option corresponds."
Name Value Description option setting value
Xlprinterrorsblank 1 print error is blank. < Blank >
Xlprinterrorsdash 2 Print errors are displayed as dashes. --
Xlprinterrorsdisplayed 0 shows all printing errors. Display value
Xlprinterrorsna 3 Print errors are displayed as unavailable. #N/A
3. BlackAndWhite: True If the elements in the specified document are printed in black and white.
A Boolean type that can be read and written. This property corresponds to the print-monochrome printing option.
Execution Code: Worksheets ("Sheet1"). Pagesetup.blackandwhite = TRUE,SHEET1 Worksheet
Will print in black and white.
4. PrintHeadings: True If the row and column headings are printed at the same time as the page is printed.
Applies only to worksheets. A Boolean type that can be read and written. This property corresponds to the print-Line number column label option.
5. PrintComments: Returns or sets how the annotation prints with the worksheet. XlPrintLocation type,
can read and write. This property corresponds to the print-comment option.
Name Value Description option setting value
Xlprintinplace 16 comments are printed where they are inserted into the worksheet. As shown in the worksheet
xlPrintNoComments-4142 does not print annotations. (default) (empty)
Xlprintsheetend 1 annotations are printed as endnotes at the end of the worksheet. End of sheet
6. PrintNotes: True if the cell annotation is printed as an endnote when the worksheet is printed.
Applies only to worksheets. A Boolean type that can be read and written. This property corresponds to the print-comment option.
This property is set to True with the PrintComments property set to the same xlprintsheetend effect. This
The PrintComments property is set to False and the xlprintnocomments effect is the same as the property set.
That
Printcomments=xlprintsheetend equivalent to Printnotes=true
printcomments= xlprintnocomments equivalent to Printnotes=false
7. PrintArea: Returns or sets the area to be printed as a string using the A1 of the macro language
A reference to the style. A String type that can be read and written. This property corresponds to the Print Area option.
Set this property to False or an empty string ("") to print the entire worksheet.
Worksheets ("Sheet1"). Pagesetup.printarea = "$A $ $C $"
8. Order: Returns or sets a XlOrder value that represents MicrosoftExcel printing a large work
The order in which the page numbering is used for the table. This property corresponds to the print order option.
Name Value Description option setting value
Xldownthenover 1 processes the rows down and then the page or page fields to the right. First row and then
Xloverthendown 2 processes the page or page field one by one, and then processes the row down. First post column
9. PrintGridlines: If the cell gridlines are printed on the page, this value is True. Apply only to
Worksheet. A Boolean type that can be read and written. This property corresponds to the print-grid line option.
10. PrintTitleRows: Returns or sets the rows that contain the cells that recur at the top of each page,
The macro language string is represented by the A1 style notation. A String type that can be read and written. This property is associated with the top label
Line option corresponds.
If you specify only a portion of the row, Microsoft Excel expands the area to the entire row. This property is set
False or an empty string (""), the header row will be closed.
11. PrintTitleColumns: Returns or sets the column that contains the repeating cell on the left side of each page.
A string representation in the a1-style with the macro language. A String type that can be read and written. This property is associated with the "left-side header row"
Option corresponds.
If you specify only a portion of a column, Microsoft Excel automatically expands the zone to the entire column. (Plus One
Example) sets the property to False or to an empty string (""), which closes the header column.
There are 7 attributes that have no corresponding relationship to the tab. respectively: Application, Creator,
Evenpage, FirstPage, Pages, Parent, ChartSize.
1, application if the object qualifier is not used, the property returns a Application object that
The MicrosoftExcel object represents the application. If you use an object qualifier, this property returns a table
Specifies the object (an application that can use this property to return an OLE auto-action object)
The Application object of the creator. Read-only.
Execute code: ActiveSheet.PageSetup.Application.Name, you can return the name of the application
"Microsoft Excel"
2. Creator returns a 32-bit integer that indicates the application in which this object was created. Only
Read Long type.
Execution code: ActiveSheet.PageSetup.Creator, returns the number 1480803660, which is equivalent to 16 in
5843454C, the ACSII code combination of XCEL. Microsoft has the code to create XCEL.
3. Evenpage Returns or sets the alignment of text on even pages of a workbook or section.
EvenPage.LeftHeader.Text setting the left header of a even page
EvenPage.CenterHeader.Text setting the header in a even-page
EvenPage.RightHeader.Text set the right header of a even page
EvenPage.LeftFooter.Text set the left footer of an even page
EvenPage.CenterFooter.Text Setting the middle footer of an even page
EvenPage.RightFooter.Text set the right footer for even pages
4. FirstPage Returns or sets the alignment of text on the first page of a workbook or section.
FirstPage.LeftHeader.Text set the left header of the first page
FirstPage.CenterHeader.Text Setting the first page of the header
FirstPage.RightHeader.Text set the right header of the first page
FirstPage.LeftFooter.Text set the left footer of the first page
FirstPage.CenterFooter.Text Setting the middle footer of the first page
FirstPage.RightFooter.Text set the right footer for the first page
5. Pages Returns or sets the number of pages in the Pages collection.
Pages.count the number of pages printed on behalf of the worksheet.
6. Parent returns the parents of the specified object. Read-only.
Represents the parent object of PageSetup, which is the Worsheet object. To run the code:
ActiveSheet.PageSetup.Parent.Name, gets the name of the current worksheet.
7. ChartSize Returns or sets the way the chart is scaled to fit the page size.
This example sets the text for the central title of "Chart1".
Charts ("Chart1"). Pagesetup.centerheader = "December Sales"
Order
No.
Name Description version
Choose
Item
Card
Choose
Item
Preparation
Note
1 Draft
If you print a worksheet without printing
The property value of the graphic that is
True. Boolean type, readable
Write.
Workers
For
Table
Play
Printed
-
Grass
Draft
Product
Quality
2 printerrors
Sets or returns a
XlPrintErrors constants, which are often
volume Specifies the print error class that is displayed
Type. This feature allows the user to print
Cancel the error display when working on the worksheet. Can
Write.
Workers
For
Table
Play
Printed
-
Wrong
Miss
Single
Yuan
Pane
Play
Printed
For
3 BlackAndWhite
If you specify elements in the document to
Print in black and white, the property value
Is True. Boolean type,
can read and write.
Workers
For
Table
Play
Printed
-
Single
Color
Play
Printed
4 printheadings
If you print this page at the same time
Row and column headings, the value is
True. Applies only to worksheets.
A Boolean type that can be read and written.
Workers
For
Table
Play
Printed
-
Yes
No.
Column
Standard
5 printcomments
Returns or sets an annotation with a worksheet
The way you print.
XlPrintLocation type, can be
Write.
Workers
For
Table
Play
Printed
-
Batch
Note
6 PrintNotes
If the cell is printed on a worksheet
Annotations are printed together as endnotes, the
The value is True. Apply only to work
As a table. Boolean type, readable
Write.
Workers
For
Table
Play
Printed
-
Batch
Note
7 PrintArea
Returns or sets the string to be hit
The printed area, which uses the macro
A reference to the A1 style of the language.
A String type that can be read and written.
Workers
For
Table
Play
Printed
Area
Domain
8 Order
Returns or sets a XlOrder
Value, which represents the Microsoft
Excel Prints a large sheet
The order in which the page numbers are used.
Workers
For
Table
Play
Printed
Shun
Order
9 PrintGridlines
If you print cells on a page
Gridlines, the value is True.
Applies only to worksheets. Boolean
Type, readable and writable.
Workers
For
Table
Play
Printed
-
Network
Pane
Line
Ten PrintTitleRows
Returns or sets those contained in each
A repeating unit at the top of a page
The line of the grid, using the macro language string to
A1 style notation.
A String type that can be read and written.
Workers
For
Table
Top
End
Standard
Problem
Yes
PrintTitleColumns
Returns or sets the page contained in the
Repeating cells on the left side of the
Columns, in the a1-style with the macro language
The string representation of the. String class
Type, can read and write.
Workers
For
Table
Left
End
Standard
Problem
Yes
Application
If you do not use an object qualifier, the
This property returns a
The Application object that the object
Indicates that Microsoft Excel should
Use the program. If you use object recognition
property returns a representation of the
Specifies an object that can be used on an OLE
No no No
Automatic manipulation objects Use this property
To return the object's application)
The application of the creator
Like. Read-only.
Creator
Returns a 32-bit integer that is the entire
Number indicating where this object is created
The application. Read-Only Long class
Type.
No no No
Evenpage
Returns or sets a workbook or section
The alignment of text on even pages.
2007 No No
FirstPage
Returns or sets a workbook or section
The text alignment on the first page.
2007 No No
Pages
Returns or sets the Pages in the collection
Number of pages.
2007 No No
-Parent
Returns the parent object of the specified object. Only
Read.
No no No
ChartSize
Returns or sets the chart to fit the page
The size of the polygon and how it is scaled.
2003 No No
Has
By deleting
Except
CenterVertically
If the vertical center position on the page
Print the specified sheet, the genus
A value of True. Boolean class
Type, can read and write.
Page
Side
From
Ju
In
Party
Expression
-
Vertical
Straight
CenterHorizontally
If the position is centered horizontally on the page
Print the specified sheet, the genus
A value of True. Boolean class
Type, can read and write.
Page
Side
From
Ju
In
Party
Expression
-
Water
Flat
TopMargin
Returns or sets the up in points
The size of the margin. Double type,
can read and write.
Page
Side
From
On
BottomMargin
Returns or sets the bottom in points
The size of the end margin. Double class
Type, can read and write.
Page
Side
From
Under
FooterMargin
Returns or sets the page in points
The distance from the bottom of the page to the foot.
Double type, readable and writable.
Page
Side
From
Page
Feet
HeaderMargin Returns or sets the page page in points
The distance from the top of the face to the header.
Double type, readable and writable.
Side
From
Eyebrow
RightMargin
Returns or sets the right, measured in points
The size of the margin. Double type,
can read and write.
Page
Side
From
Right
LeftMargin
Returns or sets the left, measured in points
The size of the margin. Double type,
can read and write.
Page
Side
From
Left
OddAndEvenPagesHeaderFooter
If the specified PageSetup pair
Odd and even pages of the image have
A different header and footer;
True. Readable/writable Boolean class
Type.
2007
Page
Eyebrow
/
Page
Feet
Odd
I
Page
No
With
DifferentFirstPageHeaderFooter
If the first page uses a different
The header or footer is True.
A readable/write Boolean type.
2007
Page
Eyebrow
/
Page
Feet
First
Page
No
With
Scalewithdocheaderfooter
Returns or sets the header and footer that are
No when the document size changes
File scaling. Readable/write Boolean
Type.
2007
Page
Eyebrow
/
Page
Feet
With
Text
File
Self -
Move
Shrink
Put
Alignmarginsheaderfooter
If Excel sets the selected page
Align header with margin set in item
And the footer, returns TRUE. Can
Read/write Boolean type.
2007
Page
Eyebrow
/
Page
Feet
And
Page
Side
From
Right
Qi
RightFooter
Right-aligns the PageSetup object
The footer information. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
-
Right
Text
This
Letter
Interest
RightFooterPicture
Returns a Graphic object,
The object represents the diagram to the right of the footer
To set the properties of the picture.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
Figure
Chip
Letter
Interest
-
Right
CenterFooter
Center Align PageSetup Objects
The footer information in the. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
-
In
Text
This
Letter
Interest
CenterFooterPicture
Returns a Graphic object,
The object represents the middle part of the footer
The picture. The genus used to set the picture
Of
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
-
In
Figure
Chip
Letter
Interest
LeftFooter
Left-justified in PageSetup object
The footer information. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
-
Left
Text
This
Letter
Interest
Approx. leftfooterpicture
Returns a Graphic object,
The object represents the diagram to the left of the footer
Chip The property used to set the picture.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Feet
-
Left
Figure
Chip
Letter
Interest
Panax Notoginseng RightHeader
Right-aligns the PageSetup object
Header information. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
Right
Text
This
Letter
Interest
RightHeaderPicture
Returns a Graphic object,
The object represents the diagram to the right of the header
Chip The property used to set the picture.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
Right
Figure
Chip
Letter
Interest
CenterHeader
Center Align PageSetup Objects
Header information in the. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
In
Text
This
Letter
Interest
CenterHeaderPicture
Returns a Graphic object,
The object represents the middle part of the header
The picture. The genus used to set the picture
Of
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
In
Figure
Chip
Letter
Interest
LeftHeader
Left-justified in PageSetup object
Header information. Readable/writable
The String type.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
Left
Text
This
Letter
Interest
LeftHeaderPicture
Returns a Graphic object,
The object represents the diagram to the left of the header
Chip The property used to set the picture.
Page
Eyebrow
/
Page
Feet
Self -
Availability
Yi
Page
Eyebrow
-
Left
Figure
Chip
Letter
Interest
PrintQuality
Returns or sets the print quality.
Variant type, readable and writable.
Page
Surface
Play
Printed
Quality
Amount
Orientation
Returns or sets a
The XlPageOrientation value, which
Represents portrait or landscape print mode.
Page
Surface
Party
To
FirstPageNumber
Returns or sets the print specified work
The page number of the first page when the table is. If you set
Is xlautomatic, the
Microsoft Excel uses the first
The page number of a page. The default value is
Xlautomatic. Long type,
can read and write.
Page
Surface
From
Began
Page
Code
Zoom to return or set a Variant page indent
Value, which represents a value of 10%
To a percentage between 400%, the
Percent is Microsoft
When Excel prints a worksheet
Proportional.
Face put
Than
Cases
FitToPagesWide
Returns or sets when the worksheet is printed,
The scale used for the worksheet
The page width. Applies only to worksheets.
Variant type, readable and writable.
Page
Surface
Shrink
Put
-
Page
High
FitToPagesTall
Returns or sets when the worksheet is printed,
The scale used for the worksheet
Page height. Applies only to worksheets.
Variant type, readable and writable.
Page
Surface
Shrink
Put
-
Page
Wide
PaperSize
Returns or sets the size of the paper.
XlPaperSize type, readable
Write.
Page
Surface
Paper
Single
Big
Small

VBA, Settings, excel,wps, page Setup