ExcelVBA実用講座

第７回　会社の売上げを集計する⑦ExcelVBAその５

　今回は、プログラムによる処理の繰り返しを詳しく見ていきます。
一般的なExcelの手作業による操作では、セルに式を入力して、データを加工します。
　Excelには、コピー＆貼付けという便利な機能があるので、式を１つのセルに入力し、 これをコピーして同種の処理をする別のセルに貼り付けると、複数の同種の処理を短時間で実装することができます。 しかし、あくまでセルごとの操作ですので、セルが数百あればその数だけ式を貼り付ける
必要があります。また、セルの範囲が拡大したり縮小したりするたびにこれらのセルを維持管理しなければならず、 悪くすると修正漏れなどが発生する恐れもあります。
　VBAのプログラムでは、大量のセルの処理を「処理の繰り返し」という方法で解決します。

「マスター」シートのセルを操作して、テーブルのデータを取得する

　下記のプログラムソースでは、「マスター」シートに記録されている店舗データと品目データを
それぞれのマスターテーブルから読み込んで、店舗数、品目数を取得し、店舗名、品目コード等を配列に格納しています。

    With Worksheets("マスター")
    '「マスター」シートの1列目(A列)を順次読み込む
        wCnt = 3
        wTenpoSu = 0
        Do Until Len(.Cells(wCnt, 1)) = 0
            wTenpoSu = wTenpoSu + 1
        '配列wTenpoMeiを再宣言する
            ReDim Preserve wTenpoMei(wTenpoSu)
        '配列wTenpoMeiにセルの値を格納する
            wTenpoMei(wTenpoSu) = .Cells(wCnt, 1)
            wCnt = wCnt + 1
        Loop
    '「マスター」シートの3列目(C列)と4列目(D列)を順次読み込む
        wCnt = 3
        wHinmokuSu = 0
        Do Until Len(.Cells(wCnt, 3)) = 0
            wHinmokuSu = wHinmokuSu + 1
            wCnt = wCnt + 1
        Loop
    'データを品目コード順にソートする
        .Range("C3:D" & Format(wHinmokuSu + 2, "#")).Sort Key1:=.Range("C3"), _
        Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, SortMethod:=xlPinYin
    '配列wHinmokuCdとwHinmokuMeiを再宣言する
        ReDim wHinmokuCd(wHinmokuSu)
        ReDim wHinmokuMei(wHinmokuSu)
        For wCnt = 1 To wHinmokuSu
        '配列wHinmokuCdとwHinmokuMeiにセルの値を格納する
            wHinmokuCd(wCnt) = .Cells(wCnt + 2, 3)
            wHinmokuMei(wCnt) = .Cells(wCnt + 2, 4)
        Next wCnt
    End With

このページの先頭へ

①　処理の繰り返し　その１　「Do Until . . . Loop」

　データ数が固定されていないデータを処理するには、「Do Until . . . Loop」命令を用います。

構文：
  Do Until 終了条件
      繰り返しの処理
  Loop

  終了条件が真になったときに、繰り返しを終了し、「Loop」の次行に処理が移ります。

例：
  wCnt = 3
  wTenpoSu = 0
  Do Until Len(.Cells(wCnt, 1)) = 0
    wTenpoSu = wTenpoSu + 1
    ReDim Preserve wTenpoMei(wTenpoSu)
    wTenpoMei(wTenpoSu) = .Cells(wCnt, 1)
    wCnt = wCnt + 1
  Loop
WorksheetsとCells

このページの先頭へ

②　処理の繰り返し　その２　「For . . . Next」

　固定回数処理を繰り返すときは、「For . . . Next」命令を用います。

構文：
  For カウンタ用の変数 = 開始値 To 終了値
      繰り返しの処理
  Next カウンタ用の変数

  カウンタ用の変数を開始値から処理を始めます。
  処理を1回行うたびにカウンタ用の変数に1加えます。
  カウンタ用の変数が終了値を超えたときに、繰り返しを終了し、
  「Next カウンタ用の変数」の次行に処理が移ります。

例：
  For wCnt = 1 To wHinmokuSu
      wHinmokuCd(wCnt) = .Cells(wCnt + 2, 3)
      wHinmokuMei(wCnt) = .Cells(wCnt + 2, 4)
  Next wCnt

このページの先頭へ

③　配列の再宣言　「Redim」

　プログラムの中で配列のデータ数を計算し、それを元に配列を宣言し直すことができます。

構文①：
  Redim 変数名(引数)

例：
  wCnt = 3
  wTenpoSu = 0
  Do Until Len(.Cells(wCnt, 1)) = 0
    wTenpoSu = wTenpoSu + 1
      wCnt = wCnt + 1
  Loop
  ReDim wTenpoMei(wTenpoSu)
    プログラム中で、変数wTenpoSuの値を算出した後、 配列wTenpoMeiの引数にwTenpoSuを
    セットしています。再宣言する前に格納されていた配列の値は、全てクリアされます。

構文②：
  Redim Preserve 変数名(引数)

例：
  wCnt = 3
  wTenpoSu = 0
  Do Until Len(.Cells(wCnt, 1)) = 0
      wTenpoSu = wTenpoSu + 1
      ReDim Preserve wTenpoMei(wTenpoSu)
      wTenpoMei(wTenpoSu) = .Cells(wCnt, 1)
      wCnt = wCnt + 1
  Loop
    構文②は、変数wTenpoSuの値が増えるたびに配列wTenpoMei を再宣言します。
    「Preserve」オプションを用いると、再宣言する前に格納した配列の値を保持する
    ことができます。

このページの先頭へ

④　Excelシートのセルの範囲を指定する　「Range」

　Excelシートの中で、セルの範囲を指定するには、「Range」プロパティを使用します。

構文①：  Worksheets("シート名").Range("列行:列行")
構文②：  Worksheets("シート名").Range(左上のセル, 右下のセル)

二つの構文の具体的な記述方法は、下図を参照してください。
Range

このページの先頭へ

⑤　セルの範囲を指定して、並べ替える

　セルの範囲を指定して、特定の列の値の順に並べ替えます。

構文：
  Worksheets("シート名").Range("セル範囲").Sort Key1:=.Range("先頭セル"), _
  Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, SortMethod:=xlPinYin

例：
  並べ替えの例を下図に示します。
セルの並べ替え

（次回に続く）

このページの先頭へ

★　次回：「第８回　会社の売上げを集計する⑧ExcelVBAその６」は、 引き続きプログラムソースを見ていきます。シートのセルの表示設定（セルの結合、罫線、背景色など）をプログラムで行う方法を説明します。