Programming Excel with VBA and .NET

Change Printer Settings

Use the Worksheet or Chart objects’ PageSettings property to get or set the printer settings before printing. The PageSettings object provides a set of read/write properties that correspond to the Page Setup dialog box (Figure 11-1).

For example, this procedure displays common printer settings in some named ranges on a worksheet:

Sub GetProperties(  )
    Dim ws As Worksheet, ps As PageSetup
    Set ws = ActiveSheet
    Set ps = ws.PageSetup
    [BlackAndWhite] = ps.BlackAndWhite
    [Draft] = ps.Draft
    [BottomMargin] = ps.BottomMargin
    [TopMargin] = ps.TopMargin
    [RightMargin] = ps.RightMargin
    [LeftMargin] = ps.LeftMargin
    [Zoom] = ps.Zoom
End Sub

Figure 11-1. The PageSettings object provides properties that control these settings

This procedure changes the print settings by applying the settings from the named ranges back to the PageSettings object:

Sub SetProperties(  )
    Dim ws As Worksheet, ps As PageSetup
    Set ws = ActiveSheet
    Set ps = ws.PageSetup
    ps.BlackAndWhite = [BlackAndWhite]
    ps.Draft = [Draft]
    ps.BottomMargin = [BottomMargin]
    ps.TopMargin = [TopMargin]
    ps.RightMargin = [RightMargin]
    ps.LeftMargin = [LeftMargin]
    ps.Zoom = [Zoom].Value
End Sub

You have to use the Value property of the named range when setting Zoom in the preceding code because the Zoom property is a Variant type. Variants accept objects, so Visual Basic doesn’t automatically call the default property of ...