Visual Basic for Applications (VBA)

Problem:- How to set your page format automatically using VBA so that print report can be generated without formatting the page through page setup.

To have a A4 size Portrait page with 0 margin around one can call the ActiveSheet 
object with PageSetup method one can call the following routine with 'P' option.

Sub setpageA4_ver1(typ As String) 'typ = 'L' - Landscape , 'P' - Portrait
 
 With ActiveSheet.PageSetup
 .LeftHeader = ""
 .CenterHeader = ""
 .RightHeader = ""
 .LeftFooter = ""
 .CenterFooter = ""
 .RightFooter = ""
 .LeftMargin = Application.InchesToPoints(0) 'Set left margin
 .RightMargin = Application.InchesToPoints(0) 'Set right margin
 .TopMargin = Application.InchesToPoints(0.01) 'Set top margin 
 .BottomMargin = Application.InchesToPoints(0.01) 'Set bottom margin
 .HeaderMargin = Application.InchesToPoints(0.01) 'Set header margin
 .FooterMargin = Application.InchesToPoints(0.01) 'Set footer margin
 .PrintHeadings = False
 .PrintGridlines = False
 .PrintComments = xlPrintNoComments
 .PrintQuality = 600 'Set print quality
 .CenterHorizontally = False 'True
 .CenterVertically = False 'True
 Select Case typ
 Case "L"
 .Orientation = xlLandscape
 Case "P"
 .Orientation = xlPortrait
 End Select
 
 .Draft = False
 
 .PaperSize = xlPaperA4
 .FirstPageNumber = xlAutomatic
 .Order = xlDownThenOver
 .BlackAndWhite = False
 .Zoom = 100
 .PrintErrors = xlPrintErrorsDisplayed
 .OddAndEvenPagesHeaderFooter = False
 .DifferentFirstPageHeaderFooter = False
 .ScaleWithDocHeaderFooter = True
 .AlignMarginsHeaderFooter = True
 .EvenPage.LeftHeader.Text = ""
 .EvenPage.CenterHeader.Text = ""
 .EvenPage.RightHeader.Text = ""
 .EvenPage.LeftFooter.Text = ""
 .EvenPage.CenterFooter.Text = ""
 .EvenPage.RightFooter.Text = ""
 .FirstPage.LeftHeader.Text = ""
 .FirstPage.CenterHeader.Text = ""
 .FirstPage.RightHeader.Text = ""
 .FirstPage.LeftFooter.Text = ""
 .FirstPage.CenterFooter.Text = ""
 .FirstPage.RightFooter.Text = ""
 End With
 
End Sub 

If you are interested to set margins around the page then values can be passed through the subroutine argument list. 
For example, we can modify the above routine to suite the mentioned changes as follows:

Sub setpageA4_ver2(typ As String, lm as integer, rm as integer, tm as integer, bm as integer)
 'typ = 'L' - Landscape , 'P' - Portrait
 
 With ActiveSheet.PageSetup
 .LeftHeader = ""
 .CenterHeader = ""
 .RightHeader = ""
 .LeftFooter = ""
 .CenterFooter = ""
 .RightFooter = ""
 .LeftMargin = Application.InchesToPoints(lm) 'Set left margin - lm
 .RightMargin = Application.InchesToPoints(rm) 'Set right margin - rm
 .TopMargin = Application.InchesToPoints(tm) 'Set top margin - tm 
 .BottomMargin = Application.InchesToPoints(bm 'Set bottom margin - bm
 .HeaderMargin = Application.InchesToPoints(0.01) 'Set header margin
 .FooterMargin = Application.InchesToPoints(0.01) 'Set footer margin
 .PrintHeadings = False
 .PrintGridlines = False
 .PrintComments = xlPrintNoComments
 .PrintQuality = 600 'Set print quality
 .CenterHorizontally = False 'True
 .CenterVertically = False 'True
 Select Case typ
 Case "L"
 .Orientation = xlLandscape
 Case "P"
 .Orientation = xlPortrait
 End Select
 
 .Draft = False
 
 .PaperSize = xlPaperA4
 .FirstPageNumber = xlAutomatic
 .Order = xlDownThenOver
 .BlackAndWhite = False
 .Zoom = 100
 .PrintErrors = xlPrintErrorsDisplayed
 .OddAndEvenPagesHeaderFooter = False
 .DifferentFirstPageHeaderFooter = False
 .ScaleWithDocHeaderFooter = True
 .AlignMarginsHeaderFooter = True
 .EvenPage.LeftHeader.Text = ""
 .EvenPage.CenterHeader.Text = ""
 .EvenPage.RightHeader.Text = ""
 .EvenPage.LeftFooter.Text = ""
 .EvenPage.CenterFooter.Text = ""
 .EvenPage.RightFooter.Text = ""
 .FirstPage.LeftHeader.Text = ""
 .FirstPage.CenterHeader.Text = ""
 .FirstPage.RightHeader.Text = ""
 .FirstPage.LeftFooter.Text = ""
 .FirstPage.CenterFooter.Text = ""
 .FirstPage.RightFooter.Text = ""
 End With
 
End Sub 

Hope readers can understand the concept and use the method to suits into his/her specific requirements.

Advertisements