Visual Basic for Applications (VBA)

Source: 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s