arrays - 在 Excel 中打印特定工作表，具有不同的打印区域和使用 VBA 的打印设置

Question

我有一个包含 6 个不同工作表的工作簿。此工作簿需要为用户提供打印 2 组不同报告的选项。选项 1 将打印工作表 1、3 和 5，选项 2 将打印工作表 2、4 和 6。每个工作表都需要不同的打印范围和打印设置。我已经这样处理它，想知道这是否是处理它的最佳方法(我确信有更好/正确的方法)。

Sub printingSheets()

Dim Sh1 As Worksheet
Dim Sh3 As Worksheet
Dim Sh5 As Worksheet

With ThisWorkbook

Set Sh1 = .Sheets(“Dataset1”)
Set Sh3 = .Sheets(“Dataset2”)
Set Sh5 = .Sheets(“Dataset3”)

‘Setting the print area

Sh1.PageSetup.PrintArea = "$A$1:$C$5"
Sh3.PageSetup.PrintArea = "$A$1:$S$5"
Sh5.PageSetup.PrintArea = "$A$1:$AA$7"

‘Setting up the print setup

Sh1.PageSetup.PaperSize = xlPaperA4
Sh3.PageSetup.PaperSize = xlPaperA4
Sh5.PageSetup.PaperSize = xlPaperA3

‘Print

Array(Sh1, Sh3, Sh5).PrintOut Copies:=1

End Sub

然后我会为其他工作表编写一个单独的宏。

感谢任何愿意提供帮助的人!

Answer 1

我会为每个功能制作单独的子，并将“魔术”数字作为常量放在顶部，以便于维护

Option Explicit

Private Const WS_NAME   As String = "Dataset"
Private Const WS_ID     As String = "1 2 3 4 5 6"           'Dataset & #
Private Const WS_RANGE1 As String = "A1 A1 A1 A1 A1 A1"     'First print Cell
Private Const WS_RANGE2 As String = "C5 X1 S5 X1 AA7 X1"    'Last print Cell
Private Const WS_SIZE   As String = "9 9 9 9 8 9"           'xlPaperA4=9, xlPaperA3=8

Public Sub printWSset()

    setPrintInfo        'main setup and config

    printSet "1 3 5"    'easy to change, independent of Worksheet setup

End Sub

Public Sub printSet(ByRef wsIDs As String)
    Dim wsID As Variant

    If Len(wsIDs) > 0 Then
        For Each wsID In Split(wsIDs)
            ThisWorkbook.Worksheets(wsID).PrintOut Copies:=1
        Next
    End If
End Sub

Public Sub setPrintInfo()
    Dim wsName As Variant, wsR1 As Variant, wsR2 As Variant, wsSz As Variant, i As Long

    wsName = Split(WS_ID)       'split string by default char (space); array base at 0
    wsR1 = Split(WS_RANGE1)
    wsR2 = Split(WS_RANGE2)
    wsSz = Split(WS_SIZE)

    For i = 0 To UBound(wsName)
        setInfo WS_NAME & wsName(i), wsR1(i), wsR2(i), Val(wsSz(i))
    Next
End Sub

Private Sub setInfo(ByVal x As String, r1 As Variant, r2 As Variant, sz As Long)

    With ActiveWorkbook.Worksheets(x).PageSetup
        .PrintArea = r1 & ":" & r2
        .PaperSize = sz
    End With

End Sub