Excel VBA Print: 2 Macro Code Examples To Quickly Setup ...

Macro Example #2: Specify Page Setup And Display Print Preview Ifyou'reanythinglikeme,youprobablyspendasubstantialamountoftimeworkinginExcel.TheresultsofyoureffortsareprobablybeautifulandusefulExcelworksheets. Fromtime-to-time,youprobablysharetheresultsofyourworkinExcelwithothers.2ofthemostcommonwaysofachievingthisare: Printingyourworksheets. SavingyourfilesasPDF. PrintinganExcelfilemaybethelaststepyoutakepriortodistributingyourwork.Knowinghowtoappropriatelysetupandprintyourfilesis,therefore,veryimportant.Youprobablydon'twanttocauseabadimpressionsimplybecauseyourfiledoesn'tprintproperly. CorrectlysettingupandprintingyourExcelfilestakessometime.ThegoodnewsisthatyoucaneasilyautomatemostoftheseactivitiesusingVBA.Andifthat'syourpurpose,thenthisVBATutorialislikelytobeofhelp. MypurposewiththisblogpostistoprovideyouwiththemostrelevantinformationyouneedtoappropriatelysetupyourExcelfilesforprintingand,asrequired,printordisplayaprintpreview. ThefirstsectionofthisTutorialintroducesandexplainsseveralVBAconstructsthatarecommonlyusedwhenprintingExcelfiles. Inthesecondsection,Igothrough2detailedVBAcodeexamplesthatyoucaneasilyadjustandstartusingtoday. ThisExcelVBAPrintTutorialisaccompaniedbyanexampleworkbook.ThisworkbookcontainstheexampleVBASubproceduresIexplainbelow.Italsohasthedataandworksheetsthatappearinthescreenshotsthroughoutthispost.Youcangetimmediatefreeaccesstothisexampleworkbookbyclickingthebuttonbelow. ThefollowingTableofContentsliststhemaintopicsIcoverinthisblogpost: TableofContents HowToPrintOrDisplayPrintPreviewInExcelWithVBAPrintOutMethodParameters#1And#2:FromAndToParameter#3:CopiesParameter#4:PreviewParameter#5:ActivePrinterParameters#6And#8:PrintToFileAndPrToFileNameParameter#7:CollateParameter#9:IgnorePrintAreasPrintPreviewMethodPageSetupObjectPageSetupPropertiesDealingWithPrintArea,PaperSize,OrientationAndScalingPageSetupPropertiesDealingWithPrintingOrderAndBasicPageNumberingPageSetupPropertiesDealingWithThePrintingOfCertainItemsPageSetupPropertiesDealingWithPrintQualityPageSetupPropertiesDealingWithMarginsPageSetupPropertiesThatCenterTheSheetOnThePagePageSetupPropertiesDealingWithHeadersOrFootersPageSetupPropertiesDealingWithDifferencesBetweenPagesHPageBreakAndVPageBreakObjectsHPageBreak.DeleteAndVPageBreak.DeleteMethodsHPageBreak.DragOffAndVPageBreak.DragOffMethodsHPageBreak.LocationAndVPageBreak.LocationPropertiesHPageBreaks.AddAndVPageBreaks.AddMethodsWorksheet.ResetAllPageBreaksMethodApplication.InchesToPointsAndApplication.CentimetersToPointsMethodsMacroCodeExamplesToPrintWithExcelMacroExample#1:QuickPrintMacroExample#1ResultsMacroExample#2:SpecifyPageSetupAndDisplayPrintPreviewMacroExample#2ResultsConclusion Let'sstartbytakingaverybasiclookattheprocessyourVBAcodeusuallyfollowswhenprintingfromExcelordisplayingaprintpreview: HowToPrintOrDisplayPrintPreviewInExcelWithVBA Generally,youcanprintordisplayaprintpreviewwithVBAinthefollowing2easysteps: Specifythepageandprintsetup. Printordisplaytheprintpreview. ThisVBATutorialcoversthese2stepsindetail.Let'sstartbylookingattheVBAconstructyouusetoprintExcelworksheets,the… PrintOutMethod ThePrintOutmethodallowsyoutoprintthereferencedobject.However,youcan'tgenerallyprinthiddensheets. YoucanworkwiththePrintOutmethodatseveraldifferentlevels.Inotherwords,youcanrefertodifferentobjects.TheobjectyourefertodetermineswhatExcelprints. Becauseoftheabove,theMicrosoftDevCenterlistsdifferentversionsofthePrintOutmethod.Thefollowingarethemostrelevant: Workbook.PrintOut. Window.PrintOut. Sheets.PrintOut. Worksheets.PrintOut. Worksheet.PrintOut. Range.PrintOut. Charts.PrintOut. Chart.PrintOut. Generally,thesyntaxandparametersarealwaysthesame.Thatisregardlessoftheobjectyou'reprinting. ThebasicsyntaxofthePrintOutmethodisasfollows: expression.PrintOut(From,To,Copies,Preview,ActivePrinter,PrintToFile,Collate,PrToFileName,IgnorePrintAreas) “expression”istheobjectyouwanttoprint. Asshownabove,PrintOuthasthefollowing9parameters.Theseargumentsareoptional. From. To. Copies. Preview. ActivePrinter. PrintToFile. Collate. PrToFileName. IgnorePrintAreas. Insomecases,theIgnorePrintAreasparameterisn'tapplicable.ThisisthecasewhenyouapplythePrintOutmethodtotheWindow(Window.PrintOut),Range(Range.PrintOut)andChart(Chart.PrintOut)objects.Insuchcases,thebasicsyntaxlooksroughlyasfollows: expression.PrintOut(From,To,Copies,Preview,ActivePrinter,PrintToFile,Collate,PrToFileName) Let'stakeacloserlookatthe9parametersIlistabove: Parameters#1And#2:FromAndTo YoucanusetheFromandToparameterstospecifythepagesthatExcelprints.Moreprecisely,youcanspecifythefollowing: From: ThepageatwhichExcelstartsprinting. IfyouomittheFromparameter,Excelstartsprintingatthebeginning. To: ThelastpageExcelprints. Ifyoudon'tspecifytheToargument,Excelprintsstopsprintingafterthelastpage. Parameter#3:Copies TheCopiesparameterallowsyoutospecifyhowmanycopiesExcelprints.Thedefault,whichExcelusesifyoufailtospecifyCopies,isprinting1copy. Parameter#4:Preview Generally,whenyouprintwithVBA,Exceldoesn'tdisplaytheprintpreviewpriortoprinting.ThismeansthatVisualBasicforApplicationsgenerallyprintstherelevantobjectimmediately. Inotherwords,PreviewissettoFalsebydefault. IfyouwanttoinvoketheprintpreviewpriortoExcelprinting,setPreviewtoTrue. Parameter#5:ActivePrinter YouusetheActivePrinterparametertosetthenameoftheactiveprinter. Parameters#6And#8:PrintToFileAndPrToFileName Bydefault,Exceldoesn'tprinttoafile.Inotherwords,PrintToFileissettoFalse. Ifyouwanttoprinttoafile,youmustsetthePrintToFileparametertoTrue. ThePrToFileNameisrelevantifyousetPrintToFiletoTrue.Insuchacase: YoucanusePrToFileNametospecifythenameoftheoutputfile. Ifyoudon'tspecifyafilenamewiththePrToFileNameparameter,Excelpromptstheusertoenterthenameofthefileitshouldprintto. Parameter#7:Collate Collatingisusefulifyou'reprintingmultiplecopies.IfyouuseExcel'scollationfeature,Excelautomaticallyorganizestheprintedsheetsintologicallyorganizedsets.Sincethesetsofprintoutsarelogicallyorganized,yougenerallydon'thavetorearrangeorreorganizethemintoseparatepackages. Let'sassumethatyou'reprinting3copiesofaworksheetthathas3pages.Excelorganizestheprintedsheetsdifferentlydependingonwhetheryouchoosetocollateornot.Morespecifically: Collated:Excelcarriesouttheprintinginthefollowingorder: Theentirefirstcopy. Theentiresecondcopy. Theentirethirdcopy. Uncollated:Excelprintsouttheworksheetinthefollowingorder: 3copiesofpage1. 3copiesofpage2. 3copiesofpage3. TheCollateargumentallowsyoutocontrolthissetting,asfollows: True:Collate. False:Don'tcollate. Parameter#9:IgnorePrintAreas Bydefault,Excelconsidersanyprintareassetfortherelevantobject. Whenapplicable,youcanusetheIgnorePrintAreasparametertodeterminewhetherExcelignores(ornot)theseprintareas.ThevaluesthatIgnorePrintAreascantakeareasfollows: True:Ignoreprintareas. False:Don'tignoreprintareas. PrintPreviewMethod Parameter#4ofthePrintOutmethodIexplainintheprevioussectionallowsyoutodisplaytheprintpreviewpriortoprinting. Youcan,however,getExceltodisplaytheprintpreviewwithouthavingtoinvokethePrintOutmethod.Thefollowingmethodsshowapreviewoftheapplicableobjectprintout: Workbook.PrintPreview. Window.PrintPreview. Sheets.PrintPreview. Worksheets.PrintPreview. Worksheet.PrintPreview. Range.PrintPreview. Charts.PrintPreview. Chart.PrintPreview. ThebasicsyntaxofthePrintPreviewmethodisasfollows: expression.PrintPreview(EnableChanges) Thefollowingdefinitionsapply: expression:Objectwhoseprintoutyouwanttopreview. EnableChanges:Optionalparameter.allowsyoutospecifywhethertheusercanchangepagesetupoptionsthatareavailablewithintheprintpreviewscreen.YouspecifythissettingasoneoftheBooleanvaluesTrueorFalse.Thislooksroughlyasfollows: expression.PrintPreviewEnableChanges:=True expression.PrintPreviewEnableChanges:=False PageSetupObject ThePageSetupobjectholdsthedescriptionofthepagesetup. YougenerallyaccessthePageSetupobjectthroughthePageSetupproperty.Choose1ofthefollowing2propertiesdependingonwhichobject(worksheetvs.chart)you'reworkingwith: Worksheet.PageSetupproperty. Chart.PageSetupproperty. Therefore,yougenerallyaccessmostpagesetupattributesthroughthepropertiesofPageSetup.ThePageSetupobjecthasthefollowing48properties: AlignMarginsHeaderFooter. Application. BlackAndWhite. BottomMargin. CenterFooter. CenterFooterPicture. CenterHeader. CenterHeaderPicture. CenterHorizontally. CenterVertically. Creator. DifferentFirstPageHeaderFooter. Draft. EvenPage. FirstPage. FirstPageNumber. FitToPagesTall. FitToPagesWide. FooterMargin. HeaderMargin. LeftFooter. LeftFooterPicture. LeftHeader. LeftHeaderPicture. LeftMargin. OddAndEvenPagesHeaderFooter. Order. Orientation. Pages. PaperSize. Parent. PrintArea. PrintComments. PrintErrors. PrintGridlines. PrintHeadings. PrintNotes. PrintQuality. PrintTitleColumns. PrintTitleRows. RightFooter. RightFooterPicture. RightHeader. RightHeaderPicture. RightMargin. ScaleWithDocHeaderFooter. TopMargin. Zoom. Let'stakeacloserlookatsome(most)ofthe48propertiesIlistabove. Theexplanationsbelowdon'tfollowthealphabeticalorderabove.Rather,Icategorizethepropertiespermycriteria.Certainpropertiesmay,strictlyspeaking,fallinmorethan1category. PageSetupPropertiesDealingWithPrintArea,PaperSize,OrientationAndScaling Thefollowing5propertiesallowyoutospecifypapersize,orientationandscaling: #17:PageSetup.FitToPagesTall. #18:PageSetup.FitToPagesWide. #28:PageSetup.Orientation. #30:PageSetup.PaperSize. #32:PageSetup.PrintArea. #48:PageSetup.Zoom. Let'stakeacloserlookateachoftheseproperties: Property#32:PageSetup.PrintArea UsethePrintAreapropertytospecifytherangeyouwanttoprint.Thispropertyisonlyapplicabletoworksheets. Thebasicsyntaxyoucanusetospecifytheprintareaisasfollows: PageSetup.PrintArea=rangeA1String Thefollowingdefinitionsapply: PageSetup:PageSetupobject. rangeA1String:Rangeyouwanttosetasprintarea.YouexpresstheprintareaasastringusingA1-styleinthelanguageofthemacro. YoucansettheprintareatotheentireworksheetbysettingthePrintAreapropertytoFalseoranemptystring.Inthiscase,thebasicstatementstructureyouuselooksroughlyasfollows: PageSetup.PrintArea=False PageSetup.PrintArea="" Property#30:PageSetup.PaperSize ThePaperSizepropertyallowsyoutodeterminethesizeofthepaper. Youdothisbysettingthispropertytooneofthefollowing42XlPaperSizeconstants. Name Value Description xlPaperLetter 1 Letter(8-1/2in.x11in.) xlPaperLetterSmall 2 LetterSmall(8-1/2in.x11in.) xlPaperTabloid 3 Tabloid(11in.x17in.) xlPaperLedger 4 Ledger(17in.x11in.) xlPaperLegal 5 Legal(8-1/2in.x14in.) xlPaperStatement 6 Statement(5-1/2in.x8-1/2in.) xlPaperExecutive 7 Executive(7-1/2in.x10-1/2in.) xlPaperA3 8 A3(297mmx420mm) xlPaperA4 9 A4(210mmx297mm) xlPaperA4Small 10 A4Small(210mmx297mm) xlPaperA5 11 A5(148mmx210mm) xlPaperB4 12 B4(250mmx354mm) xlPaperB5 13 A5(148mmx210mm) xlPaperFolio 14 Folio(8-1/2in.x13in.) xlPaperQuarto 15 Quarto(215mmx275mm) xlPaper10x14 16 10in.x14in. xlPaper11x17 17 11in.x17in. xlPaperNote 18 Note(8-1/2in.x11in.) xlPaperEnvelope9 19 Envelope#9(3-7/8in.x8-7/8in.) xlPaperEnvelope10 20 Envelope#10(4-1/8in.x9-1/2in.) xlPaperEnvelope11 21 Envelope#11(4-1/2in.x10-3/8in.) xlPaperEnvelope12 22 Envelope#12(4-1/2in.x11in.) xlPaperEnvelope14 23 Envelope#14(5in.x11-1/2in.) xlPaperCsheet 24 Csizesheet xlPaperDsheet 25 Dsizesheet xlPaperEsheet 26 Esizesheet xlPaperEnvelopeDL 27 EnvelopeDL(110mmx220mm) xlPaperEnvelopeC5 28 EnvelopeC5(162mmx229mm) xlPaperEnvelopeC3 29 EnvelopeC3(324mmx458mm) xlPaperEnvelopeC4 30 EnvelopeC4(229mmx324mm) xlPaperEnvelopeC6 31 EnvelopeC6(114mmx162mm) xlPaperEnvelopeC65 32 EnvelopeC65(114mmx229mm) xlPaperEnvelopeB4 33 EnvelopeB4(250mmx353mm) xlPaperEnvelopeB5 34 EnvelopeB5(176mmx250mm) xlPaperEnvelopeB6 35 EnvelopeB6(176mmx125mm) xlPaperEnvelopeItaly 36 Envelope(110mmx230mm) xlPaperEnvelopeMonarch 37 EnvelopeMonarch(3-7/8in.x7-1/2in.) xlPaperEnvelopePersonal 38 Envelope(3-5/8in.x6-1/2in.) xlPaperFanfoldUS 39 U.S.StandardFanfold(14-7/8in.x11in.) xlPaperFanfoldStdGerman 40 GermanLegalFanfold(8-1/2in.x13in.) xlPaperFanfoldLegalGerman 41 GermanLegalFanfold(8-1/2in.x13in.) xlPaperUser 256 User-defined WhensettingthePaperSizeproperty,makesuretoconfirmthattherelevantprinterssupportthepapersizeyouwanttospecify. ThebasicsyntaxofthePaperSizepropertylooksasfollows: PageSetup.PaperSize=xlPaperSizeConstant Thefollowingdefinitionsareapplicable: PageSetup:PageSetupobject. xlPaperSizeConstant:OneoftheXlPaperSizeconstantsinthetableabove. Property#28:PageSetup.Orientation YoucanusetheOrientationpropertytodeterminethepageorientation.PageSetup.OrientationcantakeeitherofthefollowingXlPageOrientationconstants: xlPortrait(1):Portraitmode. xlLandscape(2):Landscapemode. Thestatementstructureyoucanusetosetthepageorientationtoportraitorlandscapemodeareroughlyasfollows: PageSetup.Orientation=xlPortrait PageSetup.Orientation=xlLandscape “PageSetup”isaPageSetupobject. Property#48:PageSetup.Zoom TheZoompropertyallowsyoutospecifythevaluebywhichExcelscalesaworksheetforprinting.Whenscaling,Excelretainstheaspectratiooftheworksheet. Youcanonlyapplythispropertytoworksheets.ToscaleaworksheetwiththeZoomproperty,usethefollowingstatementstructure: PageSetup.Zoom=zoom% Thefollowingdefinitionsapply: PageSetup:PageSetupobject. zoom%:Percentagebywhichyouwanttoscaletheworksheet.Itgenerallymustbebetween10%and400%. AsanalternativetotheZoomproperty,youcanscaleaworksheetbyusingtheFitToPagesTallandFitToPagesWidepropertiesIexplainbelow.However,anyscalingsettingsyouspecifywithZoomprevailoverthose2properties. Therefore,ifyouwanttoscaleaworksheetwiththeFitToPagesTallorFitToPagesWideproperties,setZoomtoFalsewiththefollowingstatement: PageSetup.Zoom=False Properties#17And#18:PageSetup.FitToPagesTallAndPageSetup.FitToPagesWide UsetheFitToPagesTallandFitToPagesWidepropertiestospecifythenumberofpagestallorwidetheworksheetisscaledto. Thefollowing2restrictionsapplytotheFitToPagesTallandFitToPagesWideproperties: Youcangenerallyonlyapplythemtoworksheets. IfyouassignavaluetothePageSetup.Zoomproperty(otherthanFalse),they'reignored.Inotherwords,ZoomprevailsoverFitToPagesTallandFitToPagesWide. Thebasicsyntaxyoucanusetoworkwiththesepropertiesisasfollows: PageSetup.FitToPagesTall=pages# PageSetup.FitToPagesWide=pages# Thefollowingdefinitionsareapplicable: PageSetup:PageSetupobject. pages#:Numberofpagesyouwanttheworksheetscaledto. YoucangenerallysetvaluesforboththeFitToPagesTallandFitToPagesWideproperties.Youcan,however,allowthescalingtobedeterminedbyasingleoftheseproperties.Todothis,settheotherproperty(representingthescalingyoudon'twant)toFalse.Thislooksasfollows: DeterminethescalingpertheFitToPagesWidepropertyonly: PageSetup.FitToPagesTall=False PageSetup.FitToPagesWide=pages# DeterminethescalingpertheFitToPagesTallpropertyonly: PageSetup.FitToPagesTall=pages# PageSetup.FitToPagesWide=False PageSetupPropertiesDealingWithPrintingOrderAndBasicPageNumbering Inthissection,Iintroducethefollowing2properties: #16:PageSetup.FirstPageNumber. #27:PageSetup.Order. Let'slookateachofthem: Property#27:PageSetup.Order TheOrderpropertyallowsyoutospecifytheorderinwhichExcelorganizesthepagesofalargeworksheet.Youhave2orderingoptions,representedbythefollowingXlOrderconstants: xlDownThenOver:First,godowntherowsoftheworksheet.Then,moveovertotheright. xlOverThenDown:First,goacrossthecolumns.Then,movedowntherows. Thefollowingisthebasicstatementstructureyoucanusetospecifyeitherofthevaluesabove: PageSetup.Order=xlDownThenOver PageSetup.Order=xlOverThenDown “PageSetup”isaPageSetupobject. Property#16:PageSetup.FirstPageNumber UsetheFirstPageNumbertosetthefirstpagenumberthatExceluseswhenprinting. ThefollowingisthebasicsyntaxyoucanusetoworkwithFirstPageNumber: PageSetup.FirstPageNumber=number# Thefollowingdefinitionsapply: PageSetup:PageSetupobject. number#:Numberyouwanttoassign.ThedefaultvalueoftheFirstPageNumberpropertyisxlAutomatic.YoucanalsosetFirstPageNumbertothisdefaultvalue,roughlyasfollows: PageSetup.FirstPageNumber=xlAutomatic PageSetupPropertiesDealingWithThePrintingOfCertainItems Whenprinting,youcanspecifythewayinwhichcertainitemsareprinted,andifthey'reprintedatall.ThefollowingarethemainPageSetuppropertiesyoucanuseforthesepurposes: #33:PageSetup.PrintComments. #34:PageSetup.PrintErrors. #35:PageSetup.PrintGridlines. #36:PageSetup.PrintHeadings. #37:PageSetup.PrintNotes. #39:PageSetup.PrintTitleColumns. #40:PageSetup.PrintTitleRows. Let'stakeacloserlookateachofthem: Properties#33And#37:PageSetup.PrintCommentsAndPageSetup.PrintNotes UsethePrintCommentspropertytodetermineif,andhow,commentsareprinted.YoucansetPrintCommentstoanyofthefollowingxlPrintLocationconstants: xlPrintNoComments(-4142):Don'tprintcomments. xlPrintSheetEnd(1):Printcommentsattheendofthesheet. xlPrintInPlace(16):Printcommentsasdisplayedonthesheet. Thebasicstatementstructureyoucanusetospecifyhowcommentsareprintedisasfollows: PageSetup.PrintComments=xlPrintLocationConstant Thefollowingdefinitionsapply: PageSetup:PageSetupobject. xlPrintLocationConstant:1ofthe3xlPrintLocationconstantsabove. Analternative,althoughmorerestricted,wayofhandlingtheprintingofcommentsisbyusingthePrintNotesproperty.PrintNotesacceptseitheroftheBooleanValuesTrueandFalse,asfollows: True:Printcommentsattheendofthesheet. False:Don'tprintcomments. ThebasicstatementstructureyoucanusewhenworkingwiththePrintNotespropertyisasfollows: PageSetup.PrintNotes=True PageSetup.PrintNotes=False “PageSetup”representsaPageSetupobject. Property#34:PageSetup.PrintErrors ThePrintErrorspropertyallowsyoudeterminethewayinwhicherrorvaluesaredisplayedwhenprinting.YoudeterminehowprinterrorsaredisplayedbysettingPrintErrorstoanyofthefollowingxlPrintErrorsconstants: xlPrintErrorsDisplayed(0):Displayprinterrors. xlPrintErrorsBlank(1):Displayblanksinsteadofprinterrors. xlPrintErrorsDash(2):Displayprinterrorsasdashes(–). xlPrintErrorsNA(3):Displayprinterrorsasnotavailable(#N/A). UsethefollowingstatementstructuretoassignavaluetoPageSetup.PrintErrors: PageSetup.PrintErrors=xlPrintErrorsConstant Thefollowingdefinitionsareapplicable: PageSetup:PageSetupobject. xlPrintErrorsConstant:AnyofthexlPrintErrorsconstantsabove. Property#35:PageSetup.PrintGridlines YoucanusethePrintGridlinespropertytospecifywhethergridlinesareprinted.YoudothisbysettingPrintGridlinestooneoftheBooleanvaluesTrueorFalse,asfollows: True:Printgridlines. False:Don'tprintgridlines. ThePrintGridlinespropertyisonlyapplicabletoworksheets. YoucanusethefollowingroughstatementstructureforpurposesofsettingPrintGridlines: PageSetup.PrintGridlines=True PageSetup.PrintGridlines=False “PageSetup”representsaPageSetupobject. Property#36:PageSetup.PrintHeadings ThePrintHeadingspropertyallowsyoutospecifywhetherrowandcolumnheadingsareprinted.Tospecifythesettingyouwant,setthePrintHeadingstooneoftheBooleanvaluesTrueorFalse,asfollows: True:Printheadings. False:Don'tprintheadings. Consideringtheabove,youcanusethefollowingbasicstatementstructuretospecifywhetherheadingsareprintedornot: PageSetup.PrintHeadings=True PageSetup.PrintHeadings=False “PageSetup”isaPageSetupobject. Properties#39And#40:PageSetup.PrintTitleColumnsAndPageSetup.PrintTitleRows ThePrintTitleColumnsandPrintTitleRowspropertiesallowyoutorepeatcertainrowsorcolumnsoneachpageofaworksheet.Moreprecisely,youcanspecifythefollowing: PrintTitleColumns:Certaincolumnsappearontheleftsideofeachpage. PrintTitleRows:Certainrowsappearatthetopofeachpage. Youcanonlyapplythese2propertiestoworksheets. YoucanusethefollowingbasicsyntaxasguidancewhenworkingwiththePrintTitleColumnsorPrintTitleRowsproperties: PageSetup.PrintTitleColumns=columnsA1String PageSetup.PrintTitleRows=rowsA1String Thefollowingdefinitionsapply: PageSetup:PageSetupobject. columnsA1StringandrowsA1String:Columnsorrowsyouwanttospecifyastitlecolumnsorrows.ExpressthetitlerowsorcolumnsasastringinA1-stylenotationinthelanguageofthemacro. Generally,youshouldspecifyfullrowsorcolumnsastitlerowsorcolumns.Ifyouspecifyincompleterowsorcolumns,Excelautomaticallyexpandstherangetocoverthefullroworcolumn. Youcanalso“turnoff”eitherofthese2propertiesbysettingthemtoFalseoranemptystring.Thislooksroughlyasfollows: PageSetup.PrintTitleColumns=False PageSetup.PrintTitleColumns="" PageSetup.PrintTitleRows=False PageSetup.PrintTitleRows="" PageSetupPropertiesDealingWithPrintQuality Thefollowing3propertiesofthePageSetupobjectdealwithprintqualitymatters: #3:PageSetup.BlackAndWhite. #13:PageSetup.Draft. #38:PageSetup.PrintQuality. ThesePageSetupsettings(usually)differamongprinters.AsIexplainbelow(forPageSetup.PrintQuality),themacrorecordermayhelpyoufindwhatistheappropriatesyntaxforacertainprinter. Executingthesamemacroonadifferentcomputer/printermay(still)resultinanerror.Insomecases,youmaywanttoconsiderworkingwitherrorhandlerconstructs(forex.,wrapthestatementswiththeOnErrorResumeNextstatement)tohandlethepotentialerrorscausedbytheseprintersettingdifferences. WhenworkingwithPageSetup,themacrorecorderusuallygeneratesseveralunnecessarystatementsthatyoucandelete.YoucanusetheinformationwithinthisTutorialtodecidewhichstatementsareusefultoachieveyourpurposes. Let'stakeacloserlookatthem: Property#38:PageSetup.PrintQuality UsethePrintQualitypropertytodeterminetheprintquality. ThePrintQualitypropertyhasasingleoptionalparameter:Index.Youuseindextospecifytowhichofthefollowingyou'rereferringto: 1:Horizontalprintquality. 2:Verticalprintquality. Ifyoudon'tusetheIndexargument,youcansetthepropertytoanarraywith2elements.Thefirstelementcontainsthehorizontalprintquality.Thesecondelementcontainstheverticalprintquality. ThebasicstatementstructureyoucanusetospecifythePrintQualitypropertylooksroughlyasfollows: PageSetup.PrintQuality(Index)=quality# Thefollowingdefinitionsapply: PageSetup:PageSetupobject. quality#:Printqualityyouwanttospecify. Inpractice,workingwiththePageSetup.PrintQualitypropertyistrickierthanitmayseematfirstglance.Themainreasonforthisisthattheprecisespecificationdependsontheprinterdriver. Inotherwords,thepreciseVBAstatementyoushoulduseusuallyvariesfromcasetocase.Youcanusuallygetagoodideaoftheexactsyntaxyouworkwithbyusingthemacrorecorder. Property#13:PageSetup.Draft TheDraftpropertyallowsyoutoprintasdraft.ThemainconsequenceofprintingasdraftisthatExceldoesn'tprintgraphics.Thismakestheprintingprocessfasterandusuallyrequireslessink. TheDraftpropertytakesoneoftheBooleanvaluesTrueorFalse,asfollows: True:Printasdraft. False:Don'tprintasdraft. ThefollowingstatementsshowthebasicstructureyoucangenerallyusetoworkwiththeDraftproperty: PageSetup.Draft=True PageSetup.Draft=False “PageSetup”representsaPageSetupobject. Property#38:PageSetup.BlackAndWhite YoucanusetheBlackAndWhitepropertytoprintinblackandwhite.Thisproperty,however,onlyappliestoworksheets. BlackAndWhitetakeseitheroftheBooleanvalues,asfollows: True:Printinblackandwhite. False:Don'tprintinblackandwhite. Thefollowingstatementsdisplaythebasicstructureyoucanusuallyworkwith: PageSetup.BlackAndWhite=True PageSetup.BlackAndWhite=False “PageSetup”isaPageSetupobject. PageSetupPropertiesDealingWithMargins Thefollowing7propertiesofthePageSetupobjectrefertomargins: #1:PageSetup.AlignMarginsHeaderFooter. #4:PageSetup.BottomMargin. #19:PageSetup.FooterMargin. #20:PageSetup.HeaderMargin. #25:PageSetup.LeftMargin. #45:PageSetup.RightMargin. #47:PageSetup.TopMargin. Icovereachoftheminthefollowingsections. Properties#19And#20:PageSetup.FooterMarginAndPageSetup.HeaderMargin ThePageSetup.HeaderMarginandPageSetup.FooterMarginpropertiesallowyoutospecifythemarginortheheaderorfooter(asappropriate). Youexpressmarginsaspointsmeasuringthefollowingdistances: HeaderMargin:Distancebetweenthetopofthepageandtheheader. FooterMargin:Distancebetweenthebottomofthepageandfooter. ThesyntaxoftheHeaderMarginandFooterMarginpropertiesissubstantiallythesame.Tospecifythesemargins,usethefollowingstatementstructure: PageSetup.HeaderMargin=margin# PageSetup.FooterMargin=margin# Thefollowingdefinitionsapply: PageSetup:PageSetupobject. margin#: Margin,expressedinpoints. YoucanusetheApplication.InchesToPointsorApplication.CentimetersToPointsmethodstoconvertameasurementfrom(i)inchesorcentimeters,to(ii)points.Iexplainbothmethodsbelow. Property#1:PageSetup.AlignMarginsHeaderFooter Whenworkingwithheadersandfooters,yougenerallydon'tsetleftandrightmargins. However,youcanchoosewhetherExcelalignstheheaderandthefooterwiththegeneralrightandleftpagemarginsbyusingthePageSetup.AlignMarginsHeaderFooterproperty.AlignMarginsHeaderFootercantakeeitherofthefollowingvalues: True:Headerandfooterarealignedwithmargins. False:Exceldoesn'taligntheheaderandfooterwiththemargins. Becauseoftheabove,youcanusethefollowingstatementstodeterminewhetherExcelcarriesouttheheaderandfooteralignment: PageSetup.AlignMarginsHeaderFooter=True PageSetup.AlignMarginsHeaderFooter=False “PageSetup”isaPageSetupobject. Thesettingyouchooseforthispropertyinfluencesallheadersandfooters:left,rightandcenter.ThefollowingcomparisonshowshowExcelalignsheadersandfooterswhenthey'realignedwithpagemarginsvs.whenthey'renot: AlignMarginsHeaderFooter True False Left Alignedwithleftmargin. Onleftsideofpage,regardlessofleftmargin. Center Centeredbetweenleftandrightmargin. Centeredinpage,regardlessofthemargins. Right Alignedwithrightmargin. Onrightsideofpage,regardlessofleftmargin. Properties#4,#25,#45And#47:PageSetup.BottomMargin,PageSetup.LeftMargin,PageSetup.RightMarginAndPageSetup.TopMargin TheTopMargin,RightMargin,BottomMarginandLeftMarginpropertiesallowyoutosetmargins. Thesyntaxofthesepropertiesissubstantiallythesame.Inotherwords,tosetamargin,usethefollowingbasicstatementstructure: PageSetup.TopMargin=margin# PageSetup.RightMargin=margin# PageSetup.BottomMargin=margin# PageSetup.LeftMargin=margin# Thefollowingdefinitionsapply: PageSetup:PageSetupobject. margin#: Marginyou'respecifying.Whenworkingwiththeseproperties,youmustexpressmarginsinpoints. Ifyouwanttoexpressmarginsininchesorcentimeters,usetheApplication.InchesToPointsorApplication.CentimetersToPointsmethods.Icoverbothmethodsintheirownsectionbelow. PageSetupPropertiesThatCenterTheSheetOnThePage MostofthesettingswithintheMarginstabofExcel'sPageSetupdialogboxdealdirectlywithmargins.Iexplainthemostrelevantmargin-relatedVBApropertiesabove. AtthebottomoftheMarginstab,thereare2settingsthatallowyoutospecifywhetherExcelcentersthesheethorizontallyorverticallywhenprinting. The2propertiesthatallowyoutospecifythesesettingsareasfollows: PageSetup.CenterHorizontally. PageSetup.CenterVertically. Let'slookateachofthem: Property#9:PageSetup.CenterHorizontally TheCenterHorizontallypropertyallowsyoutodeterminewhetherExcelcentersthecontenthorizontallyontheprintedpage.YouspecifythisbysettingthepropertytooneoftheBooleanvaluesTrueorFalse,asfollows: True:Center. False:Don'tcenter. Thebasicstructureofthestatementsthatallowyoutodothisisasfollows: PageSetup.CenterHorizontally=True PageSetup.CenterHorizontally=False “PageSetup”isaPageSetupobject. Property#10:PageSetup.CenterVertically TheCenterVerticallypropertyisliketheCenterHorizontallypropertyabove.ByusingCenterVertically,youcanspecifywhetherthecontentiscenteredverticallyontheprintedpage. CenterVerticallytakestheBooleanvaluesTrueandFalse,asfollows: True:Center. False:Don'tcenter. Thesyntaxyouusetospecifyyourvertical-centeringsettingisroughlyasfollows: PageSetup.CenterVertically=True PageSetup.CenterVertically=False “PageSetup”representsaPageSetupobject. PageSetupPropertiesDealingWithHeadersOrFooters Thefollowing18propertiesofthePageSetupobjectarerelatedtoheadersorfooters: #1:PageSetup.AlignMarginsHeaderFooter. #5:PageSetup.CenterFooter. #6:PageSetup.CenterFooterPicture. #7:PageSetup.CenterHeader. #8:PageSetup.CenterHeaderPicture. #12:PageSetup.DifferentFirstPageHeaderFooter. #19:PageSetup.FooterMargin. #20:PageSetup.HeaderMargin. #21:PageSetup.LeftFooter. #22:PageSetup.LeftFooterPicture. #23:PageSetup.LeftHeader. #24:PageSetup.LeftHeaderPicture. #26:PageSetup.OddAndEvenPagesHeaderFooter. #41:PageSetup.RightFooter. #42:PageSetup.RightFooterPicture. #43:PageSetup.RightHeader. #44:PageSetup.RightHeaderPicture. #46:PageSetup.ScaleWithDocHeaderFooter. Iexplainproperties#1(PageSetup.AlignMarginsHeaderFooter),#19(PageSetup.FooterMargin)and#20(PageSetup.HeaderMargin)above.Pleaserefertotheappropriatesectionfortheirexplanation. Inadditiontoadescriptionofalltheseproperties,IincludealistoftheVBAcodesyoucanusetoformatheadersandfooters,oraddcertainitemstothem. Inthefollowingsections,Ilookattheother15propertiesIlistabove: Properties#5,#7,#21,#23,#41And#43:PageSetup.CenterFooter,PageSetup.CenterHeader,PageSetup.LeftFooter,PageSetup.LeftHeader,PageSetup.RightFooterAndPageSetup.RightHeader Ifyouwanttosetaheaderorfooter,usethefollowingpropertiesofthePageSetupobject: PageSetup.LeftHeader:Leftheader. PageSetup.CenterHeader:Centerheader. PageSetup.RightHeader:Rightheader. PageSetup.RightFooter:Rightfooter. PageSetup.CenterFooter:Centerfooter. PageSetup.LeftFooter:Leftfooter. Ifyouwanttoworkwithanyoftheseproperties,usethefollowingsyntax: PageSetup.LeftHeader=string PageSetup.CenterHeader=string PageSetup.RightHeader=string PageSetup.RightFooter=string PageSetup.CenterFooter=string PageSetup.LeftFooter=string Thefollowingdefinitionsapply: PageSetup:PageSetupobject. string:Headerorfooteryouspecify.Younormallyspecifythisasastring. Properties#6,#8,#22,#24,#42And#44:PageSetup.CenterFooterPicture,PageSetup.CenterHeaderPicture,PageSetup.LeftFooterPicture,PageSetup.LeftHeaderPicture,PageSetup.RightFooterPictureAndPageSetup.RightHeaderPicture Intheprevioussection,Iintroducethepropertiesthatallowyoutospecifyaheaderorfooter.Thosepropertiesdealwithstrings. ThepropertiesIcoverinthissectionalsoallowyoutospecifyheadersorfooters.However,inthiscase,thepropertiesdealwithgraphicimages. Inotherwords,youworkwiththefollowingpropertiestospecifyaheaderorfooterpicture: LeftHeaderPicture:Leftheader. CenterHeaderPicture:Centerheader. RightHeaderPicture:Rightheader. RightFooterPicture:Rightfooter. CenterFooterPicture:Centerfooter. LeftFooterPicture:Leftfooter. Thesyntaxyouusetoworkwiththesepropertiesismorecomplexthantheoneyouusetosetastringheader/footer.ThereasonforthisisthatthepropertiesIcoverinthissectionreturnaGraphicobject.TheGraphicobjectholdsthepropertiesoftheheaderandfooterpictures. Inotherwords,yousetaheaderorfooterpictureinthefollowing3steps: Settheequivalentnon-picturefooterorheaderpropertytobeequaltothestring“&G”.&GisoneofthespecialVBAcodesyoucanusewhenworkingwithheadersandfooters(Icoverthistopicinitsownsectionbelow).&Ginitializesthepictureandshowsitinthelocationoftherelevantfooterorheader.Thestatements,dependingonwhichofthepropertiesaboveyouuse,areasfollows.Inallcases,“PageSetup”representsaPageSetupobject. LeftHeaderPicture: PageSetup.LeftHeader="&G" CenterHeaderPicture: PageSetup.CenterHeader="&G" RightHeaderPicture: PageSetup.RightHeader="&G" RightFooterPicture: PageSetup.RightFooter="&G" CenterFooterPicture: PageSetup.CenterFooter="&G" LeftFooterPicture: PageSetup.LeftFooter="&G" RefertotheappropriateGraphicobjectusingthepropertiesIlistabove. WorkwiththepropertiesoftheGraphicobjecttospecifythecharacteristicsoftheheaderorfooterpicture. TheGraphicobjecthasthefollowing14properties: Graphic.Application. Graphic.Brightness. Graphic.ColorType. Graphic.Contrast. Graphic.Creator. Graphic.CropBottom. Graphic.CropLeft. Graphic.CropRight. Graphic.CropTop. Graphic.Filename. Graphic.Height. Graphic.LockAspectRatio. Graphic.Parent. Graphic.Width. Inthefollowingsections,Iprovidemoredetailsaboutthemostrelevantoftheseproperties.Property#10(Graphic.Filename)isparticularlyimportant. Property#2:Graphic.Brightness UsetheGraphic.Brightnesspropertytospecifythebrightnessofafooterorheaderpicture. Thefollowinglinesshowthebasicstatementstructureyoucanuseforthesepurposes: PageSetup.LeftHeaderPicture.Brightness=brightness# PageSetup.CenterHeaderPicture.Brightness=brightness# PageSetup.RightHeaderPicture.Brigthness=brightness# PageSetup.RightFooterPicture.Brightness=brightness# PageSetup.CenterFooterPicture.Brightness=brightness# PageSetup.LeftFooterPicture.Brightness=brightness# Thefollowingdefinitionsapply: PageSetup:PageSetupobjectreference. brightness#:isthepropertyvalueyouspecify.Thisvaluecanrangebetween0.0(dimmest)and1.0(brightest). Property#3:Graphic.ColorType YoucanusetheGraphic.ColorTypepropertytoapplycertaincolortransformationstoaheaderorfooterpicture.YouspecifytheappropriatetransformationbyusingoneofthefollowingMsoPictureColorTypeconstants: msoPictureMixed(-2):Mixed. msoPictureAutomatic(1):Default. msoPictureGrayscale(2):Grayscale. msoPictureBlackAndWhite(3):Blackandwhite. msoPictureWatermark(4):Watermark. Thesyntaxofthispropertylooksasfollows: PageSetup.LeftHeaderPicture.ColorType=msoPictureColorTypeConstant PageSetup.CenterHeaderPicture.ColorType=msoPictureColorTypeConstant PageSetup.RightHeaderPicture.ColorType=msoPictureColorTypeConstant PageSetup.RightFooterPicture.ColorType=msoPictureColorTypeConstant PageSetup.CenterFooterPicture.ColorType=msoPictureColorTypeConstant PageSetup.LeftFooterPicture.ColorType=msoPictureColorTypeConstant Thefollowingdefinitionsareapplicable: PageSetup:PageSetupobjectreference. msoPictureColorTypeConstant:MsoPictureColorTypeconstant.Ilisttheseconstantsabove. Property#4:Graphic.Contrast TheGraphic.Contrastpropertyallowsyoutospecifythecontrastofaheaderorfooterpicture. Thebasicsyntaxforspecifyingthepicturecontrastisasfollows: PageSetup.LeftHeaderPicture.Contrast=contrast# PageSetup.CenterHeaderPicture.Contrast=contrast# PageSetup.RightHeaderPicture.Contrast=contrast# PageSetup.RightFooterPicture.Contrast=contrast# PageSetup.CenterFooterPicture.Contrast=contrast# PageSetup.LeftFooterPicture.Contrast=contrast# Thefollowingdefinitionsareapplicable: PageSetup:PageSetupobjectreference. contrast#:ValueyousetfortheContrastproperty.Thisvaluemustbebetween0.0(leastcontrast)and1.0(greatestcontrast). Properties#6,#7,#8And#9:Graphic.CropBottom,Graphic.CropLeft,Graphic.CropRightAndGraphic.CropTop Youcanusethefollowingpropertiestocropaheaderorfooterpicture: Graphic.CropTop:Cropoffthetop. Graphic.CropRight:Cropofftherightside. Graphic.CropBottom:Cropoffthebottom. Graphic.CropLeft:Cropofftheleftside. Thefollowinglistshowsthebasicsyntaxyouusetoworkwiththepropertiesabove: Graphic.CropTop: PageSetup.LeftHeaderPicture.CropTop=cropPoints# PageSetup.CenterHeaderPicture.CropTop=cropPoints# PageSetup.RightHeaderPicture.CropTop=cropPoints# PageSetup.RightFooterPicture.CropTop=cropPoints# PageSetup.CenterFooterPicture.CropTop=cropPoints# PageSetup.LeftFooterPicture.CropTop=cropPoints# Graphic.CropRight: PageSetup.LeftHeaderPicture.CropRight=cropPoints# PageSetup.CenterHeaderPicture.CropRight=cropPoints# PageSetup.RightHeaderPicture.CropRight=cropPoints# PageSetup.RightFooterPicture.CropRight=cropPoints# PageSetup.CenterFooterPicture.CropRight=cropPoints# PageSetup.LeftFooterPicture.CropRight=cropPoints# Graphic.CropBottom: PageSetup.LeftHeaderPicture.CropBottom=cropPoints# PageSetup.CenterHeaderPicture.CropBottom=cropPoints# PageSetup.RightHeaderPicture.CropBottom=cropPoints# PageSetup.RightFooterPicture.CropBottom=cropPoints# PageSetup.CenterFooterPicture.CropBottom=cropPoints# PageSetup.LeftFooterPicture.CropBottom=cropPoints# Graphic.CropLeft: PageSetup.LeftHeaderPicture.CropLeft=cropPoints# PageSetup.CenterHeaderPicture.CropLeft=cropPoints# PageSetup.RightHeaderPicture.CropLeft=cropPoints# PageSetup.RightFooterPicture.CropLeft=cropPoints# PageSetup.CenterFooterPicture.CropLeft=cropPoints# PageSetup.LeftFooterPicture.CropLeft=cropPoints# Thefollowingdefinitionsapply: PageSetup:PageSetupobjectreference. cropPoints#:Numberofpointsyouwanttocropoffthepicture.Generally,Excelcalculatesthesizeofthecroppedsectionwithrespecttotheoriginalsizeoftheheaderorfooterpicture. Property#10:Graphic.Filename Ifyou'respecifyingaheaderorfooterimage,you'llneedtodealwiththisproperty.Graphic.Filenameallowsyoutospecifythelocationofthefileyouuseasheaderorfooterpicture. Youcangenerallyspecifyanyofthefollowinglocations: AnURL. Afilepath,includingbothlocalandnetworkpaths. ThestatementstructureyouusetospecifyFilenameisasfollows: PageSetup.LeftHeaderPicture.Filename=locationString PageSetup.CenterHeaderPicture.Filename=locationString PageSetup.RightHeaderPicture.Filename=locationString PageSetup.RightFooterPicture.Filename=locationString PageSetup.CenterFooterPicture.Filename=locationString PageSetup.LeftFooterPicture.Filename=locationString Thefollowingaretheapplicabledefinitions: PageSetup:PageSetupobjectreference. locationString:Locationofyourheaderorfooterimage.YougenerallyspecifyFilenameasastring. Properties#11,#13And#14:Graphic.Height,Graphic.LockAspectRatioAndGraphic.Width The3propertiesIcoverinthissection(Height,LockAspectRatioandWidth)allowyoutospecifythesizeofaheaderorfooterimage. YoucansettheheightandwidthoftheheaderorfooterpicturewiththeHeightandWidthproperties(respectively).Thestatementsyoucanuseforthesepurposeslookasfollows: Height: PageSetup.LeftHeaderPicture.Height=height# PageSetup.CenterHeaderPicture.Height=height# PageSetup.RightHeaderPicture.Height=height# PageSetup.RightFooterPicture.Height=height# PageSetup.CenterFooterPicture.Height=height# PageSetup.LeftFooterPicture.Height=height# Width: PageSetup.LeftHeaderPicture.Width=width# PageSetup.CenterHeaderPicture.Width=width# PageSetup.RightHeaderPicture.Width=width# PageSetup.RightFooterPicture.Width=width# PageSetup.CenterFooterPicture.Width=width# PageSetup.LeftFooterPicture.Width=width# Thefollowingdefinitionsapply: PageSetup:PageSetupobjectreference. height#andwidth#: Heightandwidthoftheheaderorfooterpicture.YouspecifybothHeightandWidthinpoints. Ifyouwanttoworkwithinchesorcentimeters,youcanusetheApplication.InchesToPointsorApplication.CentimetersToPointsmethodsIdescribefurtherbelow. TheLockAspectRatiopropertyallowsyoutochoosewhichofthefollowingwaysExcelbehaveswhenyouresizeapicture: Theoriginalproportionsofthepicturearemaintained.Inotherwords,heightandwidthmaintainadependencyrelation. Theoriginalproportionsofthepicturearen'tnecessarilymaintained.Youcanchangethewidthorheightofthepictureindependently. Tospecifyanoption,usethefollowingMsoTriStateconstants: msoTrue(-1):Retainoriginalproportionsofpicture. msoFalse(0):Modifywidthorheightofpictureindependently. ThestatementstructureyoucanusetosetthevalueoftheLockAspectRatiopropertyisasfollows: PageSetup.LeftHeaderPicture.LockAspectRatio=msoTriStateConstant PageSetup.CenterHeaderPicture.LockAspectRatio=msoTriStateConstant PageSetup.RightHeaderPicture.LockAspectRatio=msoTriStateConstant PageSetup.RightFooterPicture.LockAspectRatio=msoTriStateConstant PageSetup.CenterFooterPicture.LockAspectRatio=msoTriStateConstant PageSetup.LeftFooterPicture.LockAspectRatio=msoTriStateConstant Thefollowingdefinitionsapply: PageSetup:PageSetupobjectreference. msoTriStateConstant:msoTrueormsoFalse,asIexplainabove. Properties#12And#26:PageSetup.DifferentFirstPageHeaderFooterAndPageSetup.OddAndEvenPagesHeaderFooter TheDifferentFirstPageHeaderFooterandOddAndEvenPagesHeaderFooterallowyoutospecifywhetherheadersandfootersare: Differentinthefirstpage,inthecaseofDifferentFirstPageHeaderFooter. Differentforoddandevennumbers,inthecaseoftheOddAndEvenPagesHeaderFooter. YouspecifywhetheranyofthedifferencesaboveappliesbysettingtherelevantpropertytoTrue.Thefollowingbasicstatementsdothis: PageSetup.DifferentFirstPageHeaderFooter=True PageSetup.OddAndEvenPagesHeaderFooter=True “PageSetup”isareferencetoaPageSetupobject. TheDifferentFirstPageHeaderFooterandOddAndEvenPagesHeaderFooterpropertiesonlyspecifywhethertherearedifferentheaders/footersforcertainpages.Thesepropertiesdon'tspecifythecharacteristicsorcontentoftheheaders/footersofthosepages.Youspecifythosecharacteristics/contentbyaccessingtheappropriateHeaderFooterobject.IexplainhowyoudothisinthesectionscoveringthePageSetup.FirstPageandPageSetup.EvenPagepropertiesbelow. Property#46:PageSetup.ScaleWithDocHeaderFooter UsetheScaleWithDocHeaderFooterpropertytodeterminewhetherExcelscalestheheaderandfooter(withtherestofthedocument)whenyouchangethesizeofthedocument. TheScaleWithDocHeaderFooterpropertycantaketheBooleanvaluesTrueandFalse,asfollows: True:Scaleheaderandfooter. False:Don'tscale. Thecorrespondingstatementstructureyoucanuseisasfollows: PageSetup.ScaleWithDocHeaderFooter=True PageSetup.ScaleWithDocHeaderFooter=False “PageSetup”isaPageSetupobject. VBACodesForFormattingOrAddingItemsToHeadersAndFooters Thetablebelowdisplaysthespecialcodesyoucanuseaspartofheaderorfooterproperties.Thesecodesallowyoutodothefollowing: Formattheheader/footer. Insertcertainitems/informationintheheader/footer. Someofthesecodescanbeveryuseful.Youcancheckoutanexampleofwhatyoucandowiththeminmacroexample#2below.Inthatcase,Iusecodesto: Insertapictureheader. Insertthenumberofeachpageexpressesas“Page#of#”. Code Description &L Aligncharactersthatfollowtotheleft. &C Centerthecharactersthatfollow. &R Aligncharactersthatfollowtotheright. &E Toggledouble-underlineprintingon/off. &X Togglesuperscriptprintingon/off. &Y Togglesubscriptprintingon/off. &B Toggleboldprintingon/off. &I Toggleitalicprintingon/off. &U Toggleunderlineprintingon/off. &S Togglestrikethroughprintingon/off. &”fontFaceName” Printcharactersthatfollowinspecifiedfont(fontFaceName). Includedoublequotations(“”). &fontSize# Printcharactersthatfollowinspecifiedfontsize(fontSize#). ExpressfontSize#inpointsandasa2-digitnumber. &colorHex Printcharactersthatfollowinspecifiedcolor(colorHex). ExpresscolorHexasahexadecimalcolorvalue. &”+” PrintcharactersthatfollowinHeadingfontofcurrenttheme. Includedoublequotations(“”). &”-“ PrintcharactersthatfollowinBodyfontofcurrenttheme. Includedoublequotations(“”). &Kxx.Syyy Printcharactersthatfollowinspecifiedcolorfromcurrenttheme. Thefollowingdefinitionsapply: xx:Themecolortouse.Expressasa2-digitnumberbetween01and12. S:+or-.+createsalightershade.–createsadarkershade. yyyPercentageofchangeinshadeofthemecolor.Expressasa3-digitnumberbetween0(0%)and100(100%). Ifthevaluesyouspecifyaren'twithinapplicablelimits,Excelusesnearestvalidvalue. &D Printcurrentdate. &T Printcurrenttime. &F Printfilename. &A Printtabname. &P Printpagenumber. &P+# Printpagenumberplusspecifiednumber(#). &P-# Printpagenumberminusspecifiednumber(#). && Printanampersand(&). &N Printtotalnumberofpages. &Z Printfilepath. &G Insertpicture. Ifyou'redealingwithcodeswhereyoumustincludenumbersattheend(forexample,fontSize#),becarefulwithhowyoustructurethestringyouassigntotheheaderorfooterproperty.Inthesecases,yougenerallywanttoavoidstructuringyourstringinsuchawaythatyouhavenumbersinthefollowingplaces: Attheendofyourspecialcode;and AtthebeginningoftheactualstringyouwantExceltodisplayasheader/footer. SuchastructuremayresultinExcelinterpretingthewholedigitcombination(both#1and#2above)asasinglenumber.Thisgenerallyresultsininappropriateformatting(forexample,averylargefontSize#)andincompleteheader/footertext(withoutthenumberyouintendedtoincludeatthebeginning). Apossiblesolutiontothisissueisincludingaspaceafterthespecialcode.Forexample,ifyouwanttospecifyacenterheaderthatstates“200Customers”andwhosefontsizeis15points: Insteadofusingastatementwiththefollowingstructure: PageSetup.CenterHeader="15200Customers" Trythestatementbelow: PageSetup.CenterHeader="15200Customers" PageSetupPropertiesDealingWithDifferencesBetweenPages AsIexplaininthesectionabouttheDifferentFirstPageHeaderFooterandOddAndEvenPagesHeaderFooterpropertiesabove,youcanspecifythatcertainpageshavedifferentheadersorfooters.YoudothisbysettingtheapplicablepropertytoTrue. Those2propertiesofthePageSetupobjectdon'tspecifytheactualcharacteristicsofthedifferingheaders/footers.Iexplainhowyoucanspecifysuchcharacteristicsinthissection. Youcanspecifydifferentheaders/footersfor(i)thefirstpagevs.theotherpages,or(ii)evenvs.oddpagesinthefollowing4easysteps: SettheDifferentFirstPageHeaderFooterorOddAndEvenPagesHeaderFooterproperty(asapplicable)toTrue. UsethePageSetup.FirstPageorPageSetup.EvenPageproperty(asapplicable)toaccesstheappropriatePageobject. UsethepropertiesofthePageobjecttoaccesstheHeaderFooterobjectrepresentingtheapplicableheader/footer. WorkwiththepropertiesoftheHeaderFooterobjecttospecifythecharacteristicsoftherelevantheader/footer. Iexplainstep#1,andprovidethebasicstatementsyntaxyouuse,above.Inthissection,wetakeacloserlookattheconstructsinvolvedinsteps#2to#4.ThisincludesthefollowingPageSetupproperties: #14:PageSetup.FirstPage. #15:PageSetup.EvenPage. Properties#14And#15:PageSetup.EvenPageAndPageSetup.FirstPage BothPageSetup.FirstPageandPageSetup.EvenPagereturnaPageobject.ThePageobjectrepresentsapage. ThePageobjecthasthefollowing6properties: Page.CenterFooter. Page.CenterHeader. Page.LeftFooter. Page.LeftHeader. Page.RightFooter. Page.RightHeader. ThesepropertiesreturnaHeaderFooterobject.Let'slookatthe… HeaderFooterObject TheHeaderFooterobjectrepresentsaheaderorfooter. Inotherwords,HeaderFooteristheobjectyouworkwithforpurposesofspecifyingtheheadersandfootersofeitherofthefollowing: Firstpage,iftheDifferentFirstPageHeaderFooterpropertyissettoTrue. Evenpages,iftheOddAndEvenPagesHeaderFooterpropertyisTrue. HeaderFooterhasthefollowing2properties: HeaderFooter.Text. HeaderFooter.Picture. HeaderFooter.TextProperty TheHeaderFooter.Textpropertyallowsyoutospecifythetextthatappearswithinaheaderorfooter. Thebasicsyntaxyouusetospecifythetextofaheaderorfooterisasfollows: expression.LeftHeader.Text=string expression.CenterHeader.Text=string expression.RightHeader.Text=string expression.RightFooter.Text=string expression.CenterFooter.Text=string expression.LeftFooter.Text=string Thefollowingdefinitionsapply: PageSetup:PageSetupobject. string:Headerorfooteryouwanttoset.Yougenerallysetthesepropertiestoastring. HeaderFooter.PictureProperty TheHeaderFooter.PicturepropertyreturnsaGraphicobject.ThisGraphicobjectrepresentstheheaderorfooterpictureyouuse. IcovertheGraphicobjectindetailwhenintroducingtheCenterFooterPicture,CenterHeaderPicture,LeftFooterPicture,LeftHeaderPicture,RightFooterPictureandRightHeaderPictureproperties.ThosepropertiesreturnaGraphicobjectrepresentingaheaderorfooterpicture.JustastheHeaderFooter.Picturepropertywe'relookingatinthissection. Therefore,youcanusetheHeaderFooter.Picturepropertyforpurposesofsettingaheaderorfooterpicture.YouspecifythecharacteristicsofthepicturethroughthepropertiesoftheGraphicobject. IexplainthemostimportantpropertiesoftheGraphicobjectabove.ThecommentsthereareroughlyapplicabletotheobjectreturnedbythePicturepropertyhere. Thereare,however,somedifferencesintheconstructsyouuseinthe3-stepprocessyouusetosettheheaderorfooterpicture.WhenworkingwiththeHeaderFooter.Pictureproperty,yousetaheaderorfooterpictureinthefollowing3simplesteps: Settheequivalentnon-picturefooterorheaderpropertytobeequal“&G”.Normally,youdothisbyworkingwiththeLeftHeader,CenterHeader,RightHeader,RightFooter,CenterFooterorLeftFooterpropertiesofthePageSetupobject.Thisisn'tthecasewhendealingwithaHeaderFooterobject.WhenworkingwithaHeaderFooterobject,aswe'redoingnow,thepropertyyousettoequal“&G”isHeaderFooter.Text.Inotherwords,thestatementsyouuselookroughlyasfollows: expression.LeftHeader.Text="&G" expression.CenterHeader.Text="&G" expression.RightHeader.Text="&G" expression.RightFooter.Text="&G" expression.CenterFooter.Text="&G" expression.LeftFooter.Text="&G"   “expression”representsaPageobject.Inthiscontext,yougenerallyusethePageSetup.FirstPageorPageSetup.EvenPagepropertiestorefertothatobject. RefertotheappropriateGraphicobjectusingtheHeaderFooter.Pictureproperty. UsethepropertiesoftheGraphicobjecttospecifythecharacteristicsoftherelevantheaderorfooterpicture. Pleaserefertotheappropriatesectionaboveforamoredetailedexplanationofsteps#2and#3. Asarelativelysimplyexample,thebasicstatementstructureyoucanusetospecifythelocationofthefileyouuseasheaderorfooterpicture(Filenameproperty)isasfollows: expression.LeftHeader.Picture.Filename=locationString expression.CenterHeader.Picture.Filename=locationString expression.RightHeader.Picture.Filename=locationString expression.RightFooter.Picture.Filename=locationString expression.CenterFooter.Picture.Filename=locationString expression.LeftFooter.Picture.Filename=locationString Thefollowingdefinitionsapply: expression:Pageobject. locationString:Stringspecifyinglocationofyourheaderorfooterimage. HPageBreakAndVPageBreakObjects TheHPageBreakandVPageBreakobjectsrepresentindividualpagebreaks.ThecorrespondingcollectionsofallpagebreakswithintheprintareaareHPageBreaksandVPageBreaks. HPageBreakandHPageBreaksrefertohorizontalpagebreaks.VPageBreakandVPageBreaksrefertoverticalpagebreaks. YoucangenerallyaccesstheHPageBreakofVPageBreakobjectsthroughthefollowingproperties: Sheets.HPageBreaksorSheets.VPageBreaks. Worksheets.HPageBreaksorWorksheets.VPageBreaks. Worksheet.HPageBreaksorWorksheet.VPageBreaks. Charts.HPageBreaksorCharts.VPageBreaks. Generally,thepropertiesabovereturnoneofthefollowing: AHPageBreakscollectionrepresentingallthehorizontalpagebreakswithintheapplicableobject. AVPageBreakscollectionthatrepresentsalltheverticalpagebreakswithintheobject. Inthefollowingsections,IintroducesomeofthemostimportantVBAconstructsyoucanusetoworkwithpagebreaks: HPageBreak.DeleteAndVPageBreak.DeleteMethods UsetheHPageBreak.DeleteortheVPageBreak.Deletemethodstodeleteapagebreak. Thebasicstructureofthestatementsyouusetoworkwiththesemethodsisasfollows: HPageBreak.Delete VPageBreak.Delete Thefollowingdefinitionsapply: HPageBreak:HPageBreakobject. VPageBreak:VPageBreakobject. HPageBreak.DragOffAndVPageBreak.DragOffMethods Generally,theeasiestwaytoeliminateapagebreakistoworkwiththeDeletemethodIdescribeintheprevioussection. However,ifyou'reworkingwiththemacrorecorder,youmayencountertheDragOffmethod.TheHPageBreak.DragOffandVPageBreak.DragOffmethodsdragapagebreakoutsideoftheprintarea. ThebasicsyntaxoftheDragOffmethodlooksroughlyasfollows: HPageBreak.DragOff(Direction,RegionIndex) VPageBreak.DragOff(Direction,RegionIndex) Thefollowingdefinitionsapply: HPageBreak:HPageBreakobject. VPageBreak:VPageBreakobject. Direction:Thedirectioninwhichthedragoffoccurs.DirectionisexpressedasoneofthefollowingXlDirectionconstants: xlUp(-4162):Up. xlToRight(-4161):Right. xlToLeft(-4159):Left. xlDown(-4121):Down. RegionIndex:Theindexoftheprintarearegionwherethepagebreakislocated.Ifyouhaveasinglecontiguousprintarea,there'sonly1printarearegionandRegionIndexis1.Ifyouhavenon-contiguousprintingareas,you'redealingwithmorethan1printarearegion. HPageBreak.LocationAndVPageBreak.LocationProperties TheLocationpropertyallowsyoutosetthelocationofapagebreak. Thebasicstatementstructureyouuseforthesepurposesisasfollows: HPageBreak.Location=locationRange VPageBreak.Location=locationRange Thefollowingdefinitionsareapplicable: HPageBreak:HPageBreakobject. VPageBreak:VPageBreakobject. locationRange:istheRangeobject(usuallyacell)thatdefinesthelocationofthepagebreak.Thefollowingrulesapply: HPageBreak.Location:Ahorizontalpagebreakissetatthetopedgeoftherangeyouspecifyaslocation. VPageBreak.Location:Averticalpagebreakisontheleftedgeoftherangeyousetaslocation. HPageBreaks.AddAndVPageBreaks.AddMethods UsetheAddmethodtoaddapagebreak. ThebasicsyntaxoftheAddmethodisasfollows: HPageBreaks.Add(locationRange) VPageBreaks.Add(locationRange) Thefollowingdefinitionsareapplicable: HPageBreak:HPageBreakobject. VPageBreak:VPageBreakobject. locationRange:Rangeobjectyouusetospecifythelocationofthenewpagebreak.Inthissense,theAddmethodworksinaverysimilarwaytotheHPageBreak.LocationandVPageBreak.LocationpropertiesIdescribeabove. Whenspecifyingthelocationoftheaddedpagebreak,considerthefollowingrules: HPageBreaks.Add:Ahorizontalpagebreakisaddedabovetherangeyouspecifyaslocation. VPageBreaks.Add:Verticalpagebreaksareaddedtotheleftoftherangeyoudefineaslocation. Worksheet.ResetAllPageBreaksMethod Intheprevioussection,IintroducetheHPageBreakandVPageBreakobjects.Thesearetheobjectsyougenerallyworkwithwhendealingwithpagebreaks. TheWorksheet.ResetAllPageBreaksmethodalsodealswithpagebreaks.Itresetsallpagebreakswithinaworksheet. ThebasicstatementstructureyouusetoresetpagebreakswithResetAllPageBreaksisasfollows: Worksheet.ResetAllPageBreaks “Worksheet”isaworksheetobject. Application.InchesToPointsAndApplication.CentimetersToPointsMethods Inprevioussections,IintroducecertainVBAconstructsthatmeasuredistancesinpoints.Thisisthecase,forexample,ofthepropertiesthatdealwithmarginsandimagecropping. Insuchcases,youmayprefertoexpressthosedistancesininchesorcentimeters.Thefollowing2methodsallowyoutothat: Application.InchesToPoints. Application.CentimetersToPoints. Bothmethodstakeameasurement(ininchesorcentimeters)andconvertittopoints.Thebasicstatementsyntaxyoucanuseforthesepurposesisasfollows: Application.InchesToPoints(Inches) Application.CentimetersToPoints(Centimeters) “Inches”and“Centimeters”arethevaluesyouwanttoconverttopoints. MacroCodeExamplesToPrintWithExcel Inthefollowingsections,Iprovide2step-by-stepexamplesofVBAcodethatyoucaneasilyadjusttoeasilymanipulatetheconstructsIintroduceintheprevioussections. Fortheseexamples,Icreatedasampleworkbook.Thissampleworkbookcontainsatablewith200entriesofrandomcustomerdata(Name,AddressandEmail).Theemailsareallmade-upoutlook.comaddresses. Youcangetimmediatefreeaccesstothisexampleworkbookbyclickingthebuttonbelow. Thefollowingscreenshotshowshowthesampledatalookslike: MacroExample#1:QuickPrint Thefollowingsamplemacro(printInExcel)showshowyoucanquicklyprintanExcelworksheetwithVBA.ThisSubprocedureprints2copiesofthefirst2pagesof“PrintOutExample”. Themacrohasasinglestatement.Ibreakthisstatementapartinseverallinesforlegibility. Let'stakeacloserlookateachoftheselinesofcode: Line#1:Worksheets(“PrintOutExample”).PrintOut UsestheWorksheet.PrintOutmethodtoprinttheworksheetnamed“PrintOutExample”(Worksheets(“PrintOutExample”)). Thefollowinglinesofcode(#2to#7)areparametersofthismethod. Lines#2And#3:From:=1|To:=2 TheFromandToparametersspecifythepagesthatExcelprints.Inthiscase,theinstructionisasfollows: Startprintingatpage1(From:=1). Printuntilpage2(To:=2). Line#4:Copies:=2 TheCopiesargumentspecifiesthat2copiesareprinted. Line#5:Preview:=False ThePreviewparameterissettoitsdefault(False).Becauseofthis,Exceldoesn'tdisplaytheprintpreviewpriortoprinting. Line#6:Collate:=True TheCollateparameterissettoTrue.ThisensuresthatExcelorganizestheprintedsheetsintoseparatesets. Inthisexample,Excelprints2copies.Eachcopyhas2pages.Therefore,bycollating,Excelproceedsasfollows: Printtheentirefirstcopy. Printtheentiresecondcopy. Line#7:IgnorePrintAreas:=True TheIgnorePrintAreasparameterissettoTrue.TheconsequenceofthisisthatExcelignoresanyprintareasandprintstheentireworksheet(PrintOutExample). MacroExample#1Results ThefollowingimagesshowtheroughresultsIobtainwhenexecutingthesamplemacroabove(printInExcel).Idisplayscreenshotsoftheprintpreviewforclaritypurposes.Theprintoutlooksmateriallythesame. Asyoucanseeabove,themacrodoesitsjobbutresultsaren'tnecessarilythebest. Themacroprintsthe2firstpagesofthesampleworksheet.However,thepagesetupcanbeeasilyimprovedto,forexample: Displayallcolumnswithinthesamesheetofpaper.TheprintoutresultingfromtheexecutionoftheprintInExcelmacroexampleaboveleavesouttheEmailcolumn. Repeattheheaderrow(CustomerName|Address|Email)inallprintedpages. Thefollowingmacroexample#2dealswiththeseandseveralothercommonpagesetupsettings.Let'sdiverightintoit: MacroExample#2:SpecifyPageSetupAndDisplayPrintPreview Thefollowingmacroexample(pageSetupPrintPreview)doesthefollowing: Specifiesseveralpagesetupsettings. Displaystheprintpreviewofthesampleworksheet. ThecodeofthisSubproceduremaylookrelativelycomplexatfirstglance.Don'tworryaboutthattoomuch.ThiscodesimplyimplementsseveraloftheVBAconstructsthatIexplaininthefirstpartofthisVBATutorial. SomeoftheVBAconstructsIincludeinthemacrodon'thaveamaterialeffectontheprintpreviewthatIdisplayattheend.Thisisthecase,forexample,withthePageSetup.PrintQualitypropertyImentioninstep#7below.Idothisbecausemymainpurposeistoprovideyouexamplesthatyoucaneasilyadjustanduse. Let'sdiveintosomemoredetailsofthissamplemacro.Inthiscase,ItakeyouthrougheachofthemainstepstheVBAcodegoesthrough. AtthebeginningofthisVBATutorial,Imentionthatyoucangenerallyprintordisplaytheprintpreviewofapagein2easysteps: Specifythepagesetup. Printordisplaytheprintpreview. I'mawarethatthefollowingexplanationgoesthrough(many)moresteps.However,Iorganizeitthiswaymostlyforclaritypurposes.Atabasiclevel,I'msimplyapplyingthe2stepsabove. Step#1:VariableDeclarationAndAssignment:DimmyWorksheetAsWorksheet|DimiCounterAsLong|DimmyCmPointsBaseAsSingle|SetmyWorksheet=Worksheets(“PrintPreviewExample”)|myCmPointsBase=Application.CentimetersToPoints(0.5) Thefirst3linesofcodedeclare3variables,oneofthese(myWorksheet)anobjectvariable.Thefourthandfifthlinesofcodecarryoutassignments.Thisgoesasfollows: DimmyWorksheetAsWorksheet:myWorksheetrepresentstheworksheetyourmacroworkswith. DimiCounterAsLong:iCounteristhecounterforaFor…NextLoopIdescribefurtherbelow. DimmyCmPointsBaseAsSingle:Thismacrosetsseveralmarginsand(some)picturesizes.Thosemeasurementsmustbeexpressedinpoints.myCmPointsBaseholdsa“base”or“lowestcommondenominator”thatIusetocalculatethosemargins/sizes. SetmyWorksheet=Worksheets(“PrintPreviewExample”):Assignsaworksheet(PrintPreviewExample)tothemyWorksheetobjectvariable. myCmPointsBase=Application.CentimetersToPoints(0.5):TheApplication.CentimetersToPointsmethodconverts0.5centimeterstopoints.ThisvaluebecomesthebaseIusetocalculatemarginsandsizesthatmustbeexpressedinpoints.Youcanseethisatworkinsteps#8through#11below. Step#2:SetUpWith…EndWithBlocks NoticethatthereareseveralWith…EndWithblocks.ThepurposeoftheseWith…EndWithblocksistosimplifythesyntax.Theseriesofstatementswithineachblockworkwiththeobjectspecifiedintheopeninglineoftheblock. WithmyWorksheet:VirtuallyallthestatementswithinthesamplemacroarewithinthisWith…EndWithblock.Generally,thestatementswithintheblockworkwithmyWorksheet.myWorksheetisanobjectvariablerepresentingtheworksheetyouwanttoprint. With.PageSetup:Thisblockisnestedwithinblock#1above.Therefore,it'sasimplificationof“myWorksheet.PageSetup”.Generally,thestatementswithinthisblockrefertothePageSetupobject. With.CenterHeaderPicture:Theblockisnestedwithinblock#2above.Therefore,it'sasimplificationof“myWorksheet.PageSetup.CenterHeaderPicture”.ThestatementswithintheblockdealwiththeGraphicobjectreturnedbytheCenterHeaderPictureproperty. With.EvenPage:Thisblockisalsonestedwithinblock#2above.Thefully-qualifiedobjectreferenceis“myWorksheet.PageSetup.EvenPage”.ThestatementswithintheblockworkwiththePageobjectthatEvenPagereturns. With.CenterHeader:Thisblockisnestedwithinblock#4above.Therefore,it'sasimplificationof“myWorksheet.PageSetup.EvenPage.CenterHeader”.ThestatementswithintheblockrefertotheHeaderFooterobjectthatitreturns. With.Picture:Theblockisnestedwithinblock#5above.Thisisasimplificationof“myWorksheet.PageSetup.EvenPage.CenterHeader.Picture”.TheblockdealswiththeGraphicobjectreturnedbytheHeaderFooter.Pictureproperty.AsIexplainbelow,theinternalstructureofthisblockissubstantiallythesameasthatofblock#3above(With.CenterHeaderPicture). Step#3:SpecifyPageBreaks:.ResetAllPageBreaks|ForiCounter=5To205Step41|.HPageBreaks.Add.Cells(iCounter,1)|NextiCounter Youcanspecifypagebreaksinthefollowing2easysteps: Resetallpagebreaks. Addthenewpagebreaksyouwant. Thelinesofcodewe'relookingatareoneofthewaysinwhichyoucanachievethis. Thefirstofthesestatementsisrelativelystraightforward: .ResetAllPageBreaks:resetsallpagebreakswithinmyWorksheet. Thesecondstepisslightlymoreinvolved.Atitsbasiclevel,itaddsapagebreakevery41rows,startingonthefirstrowwithdatainthesampleworksheet(row5). Tounderstandhowthisisachieve,let'slookatwhat'sgoingon: ForiCounter=5To205Step41|NextiCounter:AFor…Nextloop.Iexplainloops(includingFor…Nextloops)inthepostthatyoucanfindwithintheArchives.ForpurposesofthisVBATutorial,Ionlyhighlightthemainaspectsofthisloop,asfollows: ForiCounter=5To205: Theloopcounterstartsat5(iCounter=5)andendsat205(To205).Thesenumbers(5and205)correspondtothelocationofthefirst(5)andthelast(205)rowswithdatainthesampleworksheetthemacroworkswith. InplainEnglish,theloopstartsonthefirstrowwithdataandstopsafterthelastrowwithdatawithintheworksheet.AsIexplainbelow(whensettingthePageSetup.PrintAreaproperty),theserows(5to205)alsocorrespondtotheprintareaIspecifyinthemacro. Youcaneasilygeneralizethiskindofloopby,forexample,gettingVBAtoidentifywhichisthelastrowoftheworksheetyou'reworkingwith. Step41: Everytimetheloopgoesthrougharow,thecounterchangesby41.Becauseofthis,theloopdoesn'treallygothrougheverysinglerowbetweenrows5and205.Itstartsonrow5,andthengoesthroughevery41throwthereafter.Thiscontinuesuntilthevalueofthecounterexceeds205. Inotherwords,theloopgoesthroughthefollowingrows: Row5(see#1above). Row46(5+41). Row87(46+41). Row128(87+41). Row169(128+41). .HPageBreaks.Add.Cells(iCounter,1): TheHPageBreaks.Addmethodaddsanewhorizontalpagebreak. Eachpagebreakisaddedabovetherangeidentifiedby“.Cells(iCounter,1)”.TheWorksheet.CellspropertyreferstothecelllocatedattheintersectionofrowiCounterandcolumn1(columnA).iCounterfollowsthepatternIexplainabove(5,46,87,128and169). Therefore,theconstructIexplainhereaddsapagebreakaboveeachoftherowsidentifiedbyiCounter.Thisresultsinapagebreakevery41rows. Step#4:SpecifyPrintArea,PaperSizeAndOrientation:.PrintArea=“B5:D205”|.PaperSize=xlPaperA4|.Orientation=xlPortrait These3linesofcodeusethePageSetup.PrintArea,PageSetup.PaperSizeandPageSetup.Orientationpropertiestospecifytheprintarea,papersizeandorientation.Thisgoesasfollows: .PrintArea=“B5:D205”:Specifiestheprintarea.TherangetoprintisfromcellB5tocellD205(“B5:D205”).Thiscorrespondstothecellswithdatawithinthesampleworksheet. .PaperSize=xlPaperA4:SetsthesizeofpapertoxlPaperA4.TheconstantxlPaperA4representsA4(210mmx297mm). .Orientation=xlPortrait:Determinesthatthepageorientationisportrait(xlPortrait). Step#5:SpecifyWorksheetScalingForPrinting:.Zoom=False|.FitToPagesTall=False|.FitToPagesWide=1 Oneofthemainissueswiththeresultsobtainedwhenexecutingmacroexample#1above(printInExcel)isthatnotallthecolumnsappearedinthesamepieceofpaper.TheEmailcolumndidn'tfit. The3linesofcodewe'reworkingwithsolvethisproblem.TheymakeExcelscaletheworksheetto1pagewide.Let'slookatwhateachlineofcodedoes: .Zoom=False:SetstheZoompropertytoFalse.ThisensuresthattheworksheetisscaledinaccordancewiththeFitToPagesTallandFitToPagesWideproperties. .FitToPagesTall=False:SetstheFitToPagesTallpropertytoFalse.Like#1above,thisallowsthescalingtobedeterminedbyasingleproperty:FitToPagesWideinthiscase. .FitToPagesWide=1:Scalestheworksheetto1pagewide. Step#6:PrintGridlines,HeadingsAndSpecifyTitleRows:.PrintGridlines=True|.PrintHeadings=True|.PrintTitleRows=myWorksheet.Rows(5).Address These3linesofcodespecifytherulesfortheprintingofcertainitems,asfollows: .PrintGridlines=True:Determinesthatgridlinesareprinted. .PrintHeadings=True:Specifiesthatrowandcolumnsheadingsareprinted. .PrintTitleRows=myWorksheet.Rows(5).Address:Specifiesthatarowisrepeatedasheaderrowatthetopofeachprintedpage.Inthiscase,therowcorrespondstorow5ofmyWorksheet(myWorksheet.Rows(5)).ThestatementusestheAddressproperty(.Address)toreturnastringrepresentingrow5. Step#7:SpecifyPrintQuality:.PrintQuality=-3 ThislineofcodespecifiesthePrintquality. AsImentioninthesectiononthePageSetup.PrintQualityproperty,youcangenerallygetagoodideaofthesyntaxyoushouldusebyworkingwiththemacrorecorder.ThisiswhatIdidtodeterminethattheappropriatevalueis-3.Inthecaseofthecomputer/printerI'mworkingwith,thisvaluecorrespondstoMediumprintquality. Theexactstatementyouusetospecifyprintqualitymaybedifferentinyoursituation.Withinthecontextofthisexample,itdoesn'treallymakemuchdifferencebecause,attheend,we'renotgoingtoprinttheworksheet.Justgetaprintpreview. Step#8:SpecifyHeaderAndFooterMargins:.FooterMargin=myCmPointsBase*3|.HeaderMargin=myCmPointsBase*3|.AlignMarginsHeaderFooter=True These3linesofcodespecifythemarginsandbasicalignmentofheadersandfooters.Forpurposesofthesestatements,rememberthatthevariablemyCmPointsBaseholdsthenumberofpointsequivalentto0.5centimeters. Theprocessisasfollows: .FooterMargin=myCmPointsBase*3:Setsthefootermarginto1.5centimeters(myCmPointsBase*3=0.5*3=1.5centimetersconvertedtopoints). .HeaderMargin=myCmPointsBase*3:Like#1above.Specifiesthattheheadermarginis1.5centimeters. .AlignMarginsHeaderFooter=True:Alignsheadersandfooterswithgeneralrightandleftmargins(specifiedinstep#9below). Step#9:SpecifyMargins:.TopMargin=myCmPointsBase*6|.RightMargin=myCmPointsBase*5|.BottomMargin=myCmPointsBase*6|.LeftMargin=myCmPointsBase*5 These4linesofcodespecifythegeneralpagemargins.Forthesepurposes,IrelyonthemyCmPointsBasevariable.Thevalueheldbythisvariableisthenumberofpointsequivalentto0.5centimeters.Thestatementswe'relookingatproceedasfollows: .TopMargin=myCmPointsBase*6|.BottomMargin=myCmPointsBase*6:Setthetopandbottommarginsto3centimeters(myCmPointsBase*6=0.5*6=3centimetersconvertedtopoints). .RightMargin=myCmPointsBase*5|.LeftMargin=myCmPointsBase*5:Settherightandleftmarginsto2.5centimeters(myCmPointsBase*5=0.5*5=2.5centimetersconvertedtopoints). Step#10:CenterContentsOnPage:.CenterHorizontally=True|.CenterVertically=True These2linesofcodedeterminethatthesheetiscenteredbothhorizontallyandverticallywhenprinting.Bothstatementshavethesamebasicstructureasfollows: .CenterHorizontally=True:Centersthesheethorizontally. .CenterVertically=True:Centersthesheetvertically. Step#11:SetHeadersAndFooters Withinthisexample,severalstatementsdealwithheadersandfooters.Let'slookateachofthedifferentheadersandfootersthatthemacrosets: Step#11.1:SetCenterAndRightFooter(Text):.CenterFooter=“&BCustomerListExample”|.RightFooter=“Page&Pof&N” These2linesofcodesetfooters.However,thewaytheyworkdiffers,asfollows: .CenterFooter=“&BCustomerListExample”:Setsthecenterfootertodisplay“CustomerListExample”.Usesthe&Bformattingcodetotoggleboldprintingon/off. .RightFooter=“Page&Pof&N”:Setstherightfootertodisplaythepagenumberoutofthetotalnumberofpages.Forthesepurposes,itusesthe&Pand&Ncodes.&Pprintsthepagenumber.&Nprintsthetotalnumberofpages. Step#11.2:SetCenterHeader(AllowForPicture):.CenterHeader=“&G” Thisstatementmakesreferencetothecenterheader(.CenterHeader).BysettingthePageSetup.CenterHeaderpropertytothecode“&G”,thepicture(seenextsection)isinitializedanddisplayedinthelocationofthecenterheader. Step#11.3:SetPictureCenterHeader:With.CenterHeaderPicture|.Filename=“https://powerspreadsheets.com/wp-content/uploads/Power-Spreadsheets-Website-Logo.jpg”|.ColorType=msoPictureAutomatic|.LockAspectRatio=msoTrue|.Height=myCmPointsBase*2|EndWith ThisWith…EndWithblockspecifiesacenterheaderpictureanddeterminesseveralofitsmostimportantcharacteristics. ThestatementswithintheblockworkwiththeGraphicobjectthatthePageSetup.CenterHeaderPicturepropertyreturns.ThosestatementsdeterminethemainattributesoftheGraphicobject,asfollows: .Filename=“https://powerspreadsheets.com/wp-content/uploads/Power-Spreadsheets-Website-Logo.jpg”:Specifiesthelocationofthefileusedaspictureheader.Inthiscase,thiscorrespondstotheURLwherethePowerSpreadsheetslogo(whichyoufindattheheaderofthiswebsite)islocated.Inotherwords,thefollowingimageisused: .ColorType=msoPictureAutomatic:Specifiesthatnocolortransformationisappliedtotheheaderimage.ThemsoPictureAutomaticconstantrepresentsdefaultcolortransformation. .LockAspectRatio=msoTrue:Specifiesthattheoriginalproportionalrelationshipofthepicture(heightvs.width)ismaintainedwhentheimageisresized. .Height=myCmPointsBase*2:Setstheheightoftheheaderimageto1centimeter.myCmPointsBaseisavariablerepresentingthenumberofpointsequivalentto0.5centimeters. Step#11.4:SpecifyThatHeadersAndFootersAreDifferentForOddAndEven-NumberedPages:.OddAndEvenPagesHeaderFooter=True ThisstatementusesthePageSetup.OddAndEvenPagesHeaderFootertospecifythatheaderandfootersdifferbetweenoddandeven-numberedpages. Thisleadsusthenextgroupofstatements.Noticethatthey'reallwithinaWith…EndWithblock(With.EvenPage|EndWith).Therefore,thestatementsbelowrefertothePageobjectreturnedbythePageSetup.EvenPageproperty. Step#11.5:SetCenterAndRightFooter(Text)ForEvenPages:.CenterFooter.Text=“&BCustomerListExample”|.RightFooter.Text=“Page&Pof&N” Theselinesofcodesetthecenterandrightfooterforevenpages. Theeffectofthesestatementsisvirtuallythesameasthatwhichsetsthecenterandrightfootersingeneral,whichIdescribeabove.Inotherwords: Thecenterfooterforevenpagesisalso“CustomerListExample”.The&Bcodeturnsboldprintingon/off. Therightfooterforevenpagesalsodisplaysthecurrentpagenumberoutofthetotalnumberofpages(Page#of#)byusingthe&Pand&Ncodes. Thestatementstructure,however,differsdependingonwhichfooteryouset.Thesedifferencesareasfollows: Ifyouwanttosetthegeneralcenterfooter,youworkwiththePageSetup.CenterFooterproperty.Ifyou'resettingthetextofthecenterfooterofevenpages,youusethePage.CenterFooterandtheHeaderFooter.Textproperties. Liketheabove,yousetthegeneralrightfooterwiththePageSetup.RightFooterproperty.Inthecaseofevenpages,youusethePage.RightFooterandHeaderFooter.Textproperties. Step#11.6:SetPictureCenterHeaderForEvenPages:With.CenterHeader|.Text=“&G”|With.Picture|.Filename=“https://powerspreadsheets.com/wp-content/uploads/Power-Spreadsheets-Website-Logo.jpg”|.ColorType=msoPictureAutomatic|.LockAspectRatio=msoTrue|.Height=myCmPointsBase*2|EndWith|EndWith Thisgroupofstatementsdoesthefollowingtoevenpages: Initializestheheaderpictureandallowsittobedisplayedintheplaceofthecenterheader. Specifiesthemainattributesoftheheaderpicture. Noticethat,furtherabove,Iincludelinesofcodethatdothesametothegeneralcenterheaders.ThestatementsweanalyzeinthissectionuseasimilarlogicandstructuretothatwhichIexplainthen.Let'stakeacloserlookatthis: Thestatementswe'relookingatarewrappedwithinaWith…EndWithblock(With.CenterHeader|EndWith).Therefore,we'reworkingwiththeHeaderFooterobjectthatthePage.CenterHeaderpropertyreturns. Thetextofthecenterheaderissettothecode“&G”(.Text=“&G”).Doingthisallowsustodisplaythepictureinthelocationofthecenterheader. ThisconstructdiffersfromthatIuseforthegeneralcenterheaderabove(.CenterHeader=“&G”).Inthatcase,theVBAconstructIrelyonisthePageSetup.CenterHeaderproperty. AsecondWith…EndWithblock(With.Picture|EndWith)workswiththeGraphicobjectthattheHeaderFooter.Picturepropertyreturns. ThisdiffersfromthestructureIuseabovetospecifythegeneralcenterheaderpictures(With.CenterHeaderPicture|EndWith).Inthatcase,IaccesstherelevantGraphicobjectthroughthePageSetup.CenterHeaderPictureproperty. Despitethedifferencesin#3above,noticethatthestatementswithinthatWith…EndWithblockarethesameregardlessoftheconstructIusetoaccesstheGraphicobject: .Filename=“https://powerspreadsheets.com/wp-content/uploads/Power-Spreadsheets-Website-Logo.jpg”:Specifiesthelocationofthepictureheaderfile.Forthisexample,istheheaderofthePowerSpreadsheetswebsite. .ColorType=msoPictureAutomatic:Appliesthedefaultcolortransformationtothepictureheader. .LockAspectRatio=msoTrue:Lockstheproportionalrelationshipbetweentheheightandthewidthofthepicture,asit'sresized. .Height=myCmPointsBase*2:Setstheheightofthepictureheaderto1centimeter. Step#11.7:SetRightHeader(Text)ForEvenPages:.RightHeader.Text=“Thisisanevenpage” ThecenterfooterandcenterheaderthatIsetforevenpagesis,inpractice,thesameasthatwhichIspecifyingeneral. However,priortothislineofcode.Ihaven'tspecifiedarightheader. Thestatementwe'relookingat(.RightHeader.Text=“Thisisanevenpage”)setstherightheaderforevenpages.Thechosenstringis“Thisisanevenpage”. Becauseoftheabove,thisstatementgeneratesapracticaldifferencebetweenevenandoddpages: Oddpages,whichfollowthegeneralrule,don'thavearightheader. Evenpages,whichfollowtherulewe'relookingat,havearightheaderstating“Thisisanevenpage”. Step#12:DisplayPrintPreview:.PrintPreview Afterallthepagesettingsaredefinedbythepreviousstatements,thislineofcodegetsExceltodisplaytheprintpreview. Ifyouwanttoprint,thisisusuallytheplacewhereyou'dworkwiththePrintmethod.Macroexample#1abovecangiveyouaroughideaofthesyntaxyoucanuseforthesepurposes. MacroExample#2Results Theimagesbelowshowtheprintpreviewofthesampleworksheet.Thisresultsin5pages. Noticethefollowingcharacteristics,causedbythesamplemacro(pageSetupPrintPreview).YoucancomparesomeofthesewiththeresultsIobtainwhenexecutingmacroexample#1above. Theworksheetisscaledto1pagewide.Thisaconsequenceofthespecificationsforworksheetscaling(step#5above). Thesheetiscenteredbothhorizontallyandvertically.Thissettingisexplainedinstep#10above. Gridlinesandheadingsareprinted.ThisisaresultoftherequirementsIexplaininstep#6above. Row5ofthesampleworksheetappearsatthetopofthedataineverypage.Thisisalsoaconsequenceofstep#6above. Pagebreaksexistevery41rowsoftheworksheet.Thisspecificationissetinstep#3above. ThecenterheaderisPowerSpreadsheets'websiteheader,asrequiredbystep#11above. Thecenterfooterstates“CustomerListExample”.Thisisalsosetinstep#11above. Therightfooterprovidesthepagenumberoutofthetotalnumberofpages.Again,thisisaconsequenceofstep#11. Therightheaderofevenpagesstates“Thisisanevenpage”.Justas#5and#6above,thisisestablishedinstep#11. Conclusion KnowinghowtocorrectlysetupandprintExcelfilesisessential.Whendoneappropriately,thishelpsyoutoeasilyshareyourworkwithothers. AfterreadingthisVBATutorial,youhavetheknowledgeyourequiretobeginautomatingmostofyourprintingandpagesetupactivities.YoualsohaveagoodunderstandingofthemainVBAconstructsyoucanuse.Thefollowingaresomeoftheseconstructswe'vereviewedinthispost: ThePrintOutmethod. ThePrintPreviewmethod. ThePageSetupobject. TheHPageBreakandVPageBreakobjects. TheWorksheet.ResetAllPageBreaksmethod. TheApplication.InchesToPointsandApplication.CentimetersToPointsmethods. You'vealsoseen2practicalanddetailedexamplesofVBAcodethatyoucanadjustandstartusingimmediately. RememberthatthisExcelVBATutorialisaccompaniedbyasampleworkbook.ThisworkbookcontainstheVBAcodeofthesamplemacrosandthedataIshowinthescreenshotsabove.Youcangetimmediatefreeaccesstothisexampleworkbookbyclickingthebuttonbelow. Label {} [+] Enteryournamehere...* Enteryouremailhere...* Label {} [+] Enteryournamehere...* Enteryouremailhere...* 0Comments InlineFeedbacks Viewallcomments LoadMoreComments IpublishalotofTutorialsandTrainingResourcesaboutMicrosoftExcelandVBA.HerearesomeofmymostpopularExcelTrainingResources: FreeExcelVBAEmailCourse ExcelMacroTutorialforBeginners ExcelPowerQuery(GetandTransform)TutorialforBeginners ExcelKeyboardShortcutCheatSheet ExcelResources About Contact TermsandConditions PrivacyPolicy LimitofLiabilityandDisclaimerofWarranty AffiliateDisclosure Insert