（追記）

ページ数を頑張って取得していたけれど、PageSetupのFirstPageNumberオプションを設定することで簡単に表示することができることがわかったのでわりと根幹部を更新しました。
このオプションをどう使うかというだけの話でしかないですが、検索すると上位にでてくるQAサイトなどではいけていないやり方が多いのでこの記事は残しておきます。

前置き

こんにちは。
RubyやRailsだと問題にぶつかってもだいたいググればドキュメントやStackOverflow、Qiitaでいい記事が見つかって解決するのですが、VBAはそうではありません。

玉石混交のQAサイト、情報の少ない公式ドキュメント（もしかしたら本当の情報が掲載されたドキュメントがあるのではという気もする）、なまじエラーメッセージなど日本語対応しているために英語で検索しにくいという問題もあります。

そのために、高度なプログラミングをするエンジニアではない一業務エンジニアの自分がQiitaに書くのはVBAばかりになっています（言い訳）。

今回も、VBAで困った問題をやっていきます。

問題

さて、VBAでExcelシートをつくった際、ヘッダーなどにページ数を出力したい場合があります。
印刷するのが1シートであれば簡単。下記のようにすれば右上のヘッダーにページ数が表示されます。
&Nが全ページ数で&Pがその印刷時点でのページ数なのですね。

module1

With ThisWorkbook.Worksheets("test").PageSetup
  .RightHeader = sortBookSheetName & "&P/&Nページ"
End With

では、同じブックに複数のシートをつくってこのPageSetupをした場合はどうなるか。
1シートずつ印刷するのであれば問題なくページ数は、そのシートのものが表示されるけれど、複数シートを同時印刷しようとすると、&Nは印刷対象の全シートの全ページ数を表示し、&Pは通算のページ数を表示してしまうのです。
それがいい場合もあるけれど、たとえば、複数の人向けの帳票をVBAで一括してつくってそれぞれ刷って配布する場合は困ります。

解決策

PageSetupでFirstPageNumberを指定する。
ポイントは、PageSetupを2つにわけて、ひとつで印刷範囲の設定や印刷設定をして、枚数を確定させてからpagebreakを取得すること。

サンプルコードはこんなかんじ。

module1


Sub test
    '' 変数初期化などは省略

    '' ワークシートを足す際はAfterではなくBeforeにして、シートが作成順に並ぶようにすると全ページ数からの差分を計算しやすい。
    Worksheets.Add before:=Worksheets(1)
    ActiveSheet.name = targetSheetName

    With ThisWorkbook.Worksheets(targetSheetName)
        '' 印刷設定
        With .PageSetup
            .PrintArea = targetRange '' 適当に印刷範囲を設定する
            .FitToPagesWide = 1 '' 横幅を一枚にする
            .Orientation = xlLandscape '' 横向きに印刷する
            .FirstPageNumber = 1 '' 印刷時にページ番号を1から表示する
        End With
    
        Dim page_num As Integer
        page_num = getPageCount(ThisWorkbook.Worksheets(targetSheetName))
    
        '' 印刷設定
        With .PageSetup
            .LeftHeader = "&20" & targetSheetName & "&P / " & page_num & "ページ"
        End With

    End With
End Sub

'' 各シートのページ数を取得する
Function getPageCount(ws As Worksheet) As Integer
    Dim h As Integer, v As Integer
    getPageCount = 0
    h = ws.HPageBreaks.Count
    v = ws.VPageBreaks.Count
    If v = 0 Then
        getPageCount = h + 1
    Else
        getPageCount = (h + 1) * (v + 1)
    End If
End Function

はい。

Excel、VBAでの帳票出力は、たくさん作る場合は重かったり（どうしてもテンプレートの行挿入が重い・・・）、印刷のレイアウトが微妙にずれたり問題はあるのですが出力した後にExcelを使える人ならだれでも微修正や編集できるので例外が多い場合は便利です。
とはいえメンテナンス性は悪くなりがちなので、どうしたらよいのでしょうね。

ご意見お待ちしております。