データの取り込みと集計　（手作業での操作例と、それをVBAで。）

■ Excel豆知識53

53-5 印刷用マクロを例にしてVBAの基本的な事を

豆知識34でやってみた帳票印刷をまたやってみようと思います。

豆知識34では、名簿から番号だけを印刷用シートに読み込んで、氏名と所属はVlookup関数で表示させま
したが、今回は、番号・氏名・所属の3つともVBAで順に読み込んで、名簿にある全データを印刷しようと
思います。

コードの動きの確認を、「印刷」ではなく「印刷プレビュー」でしようと思いますので、データを10件
だけにしました。（10回くらいならプレビュー画面をクリックで閉じるのも大丈夫かなと思って。）

上図で最初に表示させたい1001番のデータについて考えてみます。
「印刷用」シートの「B2」セルには、「名簿」シートの「A2」セルの値を表示。
「印刷用」シートの「C6」セルには、「名簿」シートの「B2」セルの値を表示。
「印刷用」シートの「C8」セルには、「名簿」シートの「C2」セルの値を表示。
これをコードにすると下のようになります。

Sub test1()
    Worksheets("印刷用").Range("B2").Value = Worksheets("名簿").Cells(2, 1).Value
    Worksheets("印刷用").Range("C6").Value = Worksheets("名簿").Cells(2, 2).Value
    Worksheets("印刷用").Range("C8").Value = Worksheets("名簿").Cells(2, 3).Value
End Sub

「印刷用」シートのB2セルを表すのに「Range("B2")」としたのに対して、「名簿」シートのA2セルを表すのに
「Cells(2, 1)」としています。このRangeとCellsの使い分けについてはこちらに説明を。

この時、印刷用シートには値だけを取得すればよいので、.Copy（コピー）などは使わずに、
左辺の値＝右辺の値　というような書き方で大丈夫です。
ここで使われている「＝」は「代入演算子」というもので、左辺に右辺を代入します。

このコードを実行すると下図のようになります。

次に表示させたい1002番のデータについて考えてみます。
「印刷用」シートの「B2」セルには、「名簿」シートの「A3」セルの値を表示。
「印刷用」シートの「C6」セルには、「名簿」シートの「B3」セルの値を表示。
「印刷用」シートの「C8」セルには、「名簿」シートの「C3」セルの値を表示。
これをコードにすると下のようになります。

Sub test2()
    Worksheets("印刷用").Range("B2").Value = Worksheets("名簿").Cells(3, 1).Value
    Worksheets("印刷用").Range("C6").Value = Worksheets("名簿").Cells(3, 2).Value
    Worksheets("印刷用").Range("C8").Value = Worksheets("名簿").Cells(3, 3).Value
End Sub

このコードを実行すると下図のようになります。

ふたつのコードを比べてみると、違うのは「.Cells(2, 1)」と「.Cells(3, 1)」の行の数字の部分だけ
です。

この行の数字が「2」行目から「11」行目まで繰り返して処理されればよいので、そこに変数を使います。
そして、そこに印刷プレビューのコードも入れてみましょう。

Sub test3()
    Dim i As Long
    
    For i = 2 To 11
        Worksheets("印刷用").Range("B2").Value = Worksheets("名簿").Cells(i, 1).Value
        Worksheets("印刷用").Range("C6").Value = Worksheets("名簿").Cells(i, 2).Value
        Worksheets("印刷用").Range("C8").Value = Worksheets("名簿").Cells(i, 3).Value
        
        Worksheets("印刷用").PrintPreview
    Next i
End Sub

Option Explicitとして変数の宣言を強制していますので、Dim i As Long というふうに2行目から11行目
までの繰り返しで使う変数iを長整数型で宣言しました。（データ型についてはこちら。）
整数型 (Integer) は「 -32,768 〜 32,767」の範囲なので、行の繰り返しの変数には向かないと判断して
（今回の場合には十分間に合うのですが）As Longと長整数型を使います。 

変数の名前の付け方などは下図のExcelのヘルプをご覧ください。

繰り返しは「For i = 2 To 11 ・・・ Next i」として、変数iに2から11までの値を繰り返して入れて、
その間に挟まれているコードをその都度処理します。（For...Nextについてはこちら。）


ではSub test3()を1行ずつステップ実行してみます。SubからEnd Subの間にカーソルを置いて、F8キー
を押します。すると、1行目が黄色になります。

またF8キーを押すと、変数の宣言の行は飛ばしてFor〜の行が黄色になります。この時、変数iにカーソル
を当てると下図のようにi=0とその時に変数に入っている値が表示されます。黄色の行はこれから実行さ
れる行なのでまだiには何も入っていない状態です。

またF8キーを押すと、今度はForの行が実行されてi=2になっているのがわかります。

F8キーを数回押すと最初の印刷プレビュー画面が出ます。

印刷プレビューを閉じると、下図の行が黄色になってi=3になっています。

またF8キーを数回押すと次のプレビュー画面が出ます。

F8キーで確認するのはもういいなあと思ったら、「継続」ボタンをクリックするか、F5キーを押すと、
それ以降のコードが全部実行されます。（今回の場合はプレビューが繰り返し処理の中に入っていますので、10回
プレビュー画面をクリックして閉じる必要がありますが。）

もう確認したし、プレビュー画面をクリックするのも面倒、という場合には、プレビューのコードを
コメントアウトして「継続」としてもいいですし、もう確認したから実行するのをおしまいにしても
いいという場合には「リセット」をクリックして終了します。

最後まで実行すると、印刷用シートには最終行のデータが表示されています。

一応動きは大丈夫なことがわかりましたので、もう少し手を加えて行こうと思います。


Worksheets("印刷用")とWorksheets("名簿")が何度も出てきますので、Withでまとめようと思いますが、
どちらのワークシートもWithでという訳にはいきませんので、とりあえず「名簿」シートの方をWithで
括ろうと思います。（Withについてはこちら。）

test3の赤字の

Sub test3()
    Dim i As Long
    
    For i = 2 To 11
        Worksheets("印刷用").Range("B2").Value = Worksheets("名簿").Cells(i, 1).Value
        Worksheets("印刷用").Range("C6").Value = Worksheets("名簿").Cells(i, 2).Value
        Worksheets("印刷用").Range("C8").Value = Worksheets("名簿").Cells(i, 3).Value
        Worksheets("印刷用").PrintPreview
    Next i
End Sub

Worksheets("名簿")をWithで括って外に出します。ピリオドから右が残ります。

また、test4の緑字の「印刷用」シートも何度も出てきますので、短い変数に置き換えたいと思います。

Sub test4()
    Dim i As Long
    
    With Worksheets("名簿")
        For i = 2 To 11
            Worksheets("印刷用").Range("B2").Value = .Cells(i, 1).Value
            Worksheets("印刷用").Range("C6").Value = .Cells(i, 2).Value
            Worksheets("印刷用").Range("C8").Value = .Cells(i, 3).Value            
            Worksheets("印刷用").PrintPreview
        Next i
    End With
End Sub

Worksheets("印刷用")を変数trSh（気分としてはターゲットシート^^;）に入れるのですが、RangeやWorksheet
などのオブジェクトと言われるものはSetを用いて変数に入れます。（Setについてはこちら。）

また、「2行目から11行目まで」としている繰り返し処理を「2行目からデータの最終行目まで」としたい
と思います。

Sub test5()
    Dim i As Long
    Dim trSh As Worksheet
    
    Set trSh = Worksheets("印刷用")
    
    With Worksheets("名簿")
        For i = 2 To 11
            trSh.Range("B2").Value = .Cells(i, 1).Value
            trSh.Range("C6").Value = .Cells(i, 2).Value
            trSh.Range("C8").Value = .Cells(i, 3).Value
            
            trSh.PrintPreview
        Next i
    End With
End Sub

最終行なので一応LastRowという変数にして長整数型で宣言します。（変数名は好みです。）
赤字の部分で最終行がどうして求まるのかを次に。

Sub test6()
    Dim i As Long
    Dim trSh As Worksheet
    Dim LastRow As Long
    
    Set trSh = Worksheets("印刷用")
    
    With Worksheets("名簿")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = 2 To LastRow
            trSh.Range("B2").Value = .Cells(i, 1).Value
            trSh.Range("C6").Value = .Cells(i, 2).Value
            trSh.Range("C8").Value = .Cells(i, 3).Value
            
            trSh.PrintPreview
        Next i
    End With
End Sub

下図は、「名簿」シートの途中の行を非表示にして、シートの最下行が見えるようにしています。
A列のデータのないセルを選択して、Cｔrlキーと下向き矢印キーを押すと「A1048576」セルが選択され
ます。（その状態が下図の左です。）その後、Ctrlキーと上向き矢印キーを押すと、データの一番下にある
セルが選択されます。（赤矢印のように、A1048576セルから上に突き当たったA11セルにアクティブセルが移ります。）

この「下方向へ、上方向へ」の二つの操作をマクロ記録したものが下のコードです。
上方向の終端のセルを取得するEnd(xlUp)を利用することにします。(Endについてはこちら。）

でも、記録されたコードのように「Selectionから上方向へ」というのでは、どこが選択されているのか
わかりませんので、ここに、「データのある列の、シートの一番下の行のセル」から上方向へ、という
ふうにしたいと思います。
ここではデータのあるA列で最終行を求めたいと思います。シートの一番下のセルは「A1048576」セルです
ので、Cells(1048576,1)と表すことができます。でも、最下行の生の数字を使うよりは、Rows.Countとし
て求まるシートの最下行を利用したいと思いますので、Cells(Rows.Count, 1)になります。
Cells(Rows.Count, 1).End(xlUp)とするとA列の最下行から上へCtrlキーと上向き矢印キーを押した時と
同じにデータの最終行のセルを求めることができます。
で、そこの行番号なので、Cells(Rows.Count, 1).End(xlUp).Rowです。これでデータの最終行の「11」を
求めることが出来ます。「For i = 2 To 11」の11の代わりにLastRowを使えるようになりましたので、デ
ータの行数が代わっても対応可能になりました。
なお、
With Worksheets("名簿")
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
このようにシートの最下行を求めるRowsにも「Worksheets("名簿")の」と指定するピリオドを付けた理由
はこちらです。

これで大体出来上がりました。あとは、マクロを実行したら、印刷を開始して良いかどうかをメッセージ
ボックスで確認して、返事によっては作業を終了するようにします。
また、テストは印刷プレビューで行いましたが、実際のコードなら「印刷」にしたいと思います。（ここ
ではまだ「印刷」はコメントアウトしていますが。）
また、画面のちらつきを抑えるように、Application.ScreenUpdating = Falseを入れてみました。一応
作業のあとでApplication.ScreenUpdating = Trueに戻します。（Application.ScreenUpdatingについてはこちら。）

Sub 帳票印刷()
    Dim i As Long
    Dim LastRow As Long
    Dim trSh As Worksheet
    Dim res As Integer

    res = MsgBox("印刷を開始していいですか?", vbYesNo)
    If res = vbNo Then Exit Sub

    Set trSh = Worksheets("印刷用")

    Application.ScreenUpdating = False
    With Worksheets("名簿")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = 2 To LastRow
            trSh.Range("B2").Value = .Cells(i, 1).Value
            trSh.Range("C6").Value = .Cells(i, 2).Value
            trSh.Range("C8").Value = .Cells(i, 3).Value
            trSh.PrintPreview   '印刷プレビュー
'            trSh.PrintOut      '印刷
        Next i
    End With
    Application.ScreenUpdating = True
    MsgBox "終わりました。"
End Sub

メッセージボックスは MsgBox "こんにちは" のように、メッセージを表示させるだけにも使えますが、
「はい」「いいえ」などの答えを求めることも出来ます。
答えを求める場合は MsgBox("印刷を開始していいですか?", vbYesNo) のように、引数を括弧で括りま
す。上の「帳票印刷」のコードでは、変数resにその答えを代入しています。（MsgBoxについてはこちら。）

「帳票印刷」を実行すると下図のメッセージボックスが表示されます。

ここで「はい」か「いいえ」をクリックすると、次のIfの文でどちらを選んだかで処理が分岐されます。
If res = vbNo Then Exit Sub　としていますので、

答えが「いいえ」だったらIf res = vbNo の条件が満たされますので　Exit Subに進みます。つまり印刷
をせずにコードの実行を終了します。（If...Then...Elseについてはこちら。Exitについてはこちら。）

答えが「はい」だったらIf res = vbNo の条件が満たされていませんので Exit Subには進まずに、印刷が
実行される流れに進みます。

印刷プレビューが10回終了すると、最後の MsgBox "終わりました。" が実行され下図のメッセージが出ます。

これで「帳票印刷」はおしまいです。


ということで、次回は、53-4 そのまま取り込めるデータを毎月1回取り込み作業を行うの続きに戻ります。