ExcelVBA実用講座

トップページ> ExcelVBA実用講座目次> 第８回

　今回は、Excelシートのセルを結合したり、罫線を引いたり、背景色を設定したりする操作を
VBAで実行する方法について、詳しく説明します。

• 「月間店別売上集計」シートに集計表を表示する
• �@　プログラム中のオブジェクトの表示をまとめる。　With 命令
• �A　セルに値を格納（代入）する。　代入演算子
• �B　セルを結合する。「MergeCells」プロパティ
• �C　セルに罫線を引く。「Borders」プロパティ
• �D　セルの背景色を設定する。「Interior」プロパティ
• �E　セル内の文字の配置を設定する。「HorizontalAlignment/
  　　　VerticalAlignment」プロパティ
• �F　セル内の文字の表示形式を設定する。「NumberFormatLocal」プロパティ

「月間店別売上集計」シートに集計表を表示する

　前回、「マスター」シートから店舗と品目のデータを取得することについて説明しました。
これを元にして、「月間店別売上集計」シート上に、店別品目別売上集計表の枠を作ります。

'「月間店別売上集計」シートに店舗名と品目名をセットする
    With Worksheets("月間店別売上集計")
    '1列目(A列)の4行目から順次、品目名をセットする
        For wCnt = 1 To wHinmokuSu
            .Cells(wCnt + 3, 1) = wHinmokuMei(wCnt)
        'セルの結合、罫線、背景色、文字の配置を設定する
            With .Range(.Cells(wCnt + 3, 1), .Cells(wCnt + 3, 2))
            'セルを結合する
                .MergeCells = True
            '罫線を引く
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Weight = xlThin
                .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Weight = xlThin
                .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
                .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            '背景色を設定する
                .Interior.ColorIndex = 37
                .Interior.Pattern = xlSolid
            End With
        Next wCnt
    '合計行の表示
        .Cells(wHinmokuSu + 4, 1) = "合　計"
    'セルの結合、罫線、背景色、文字の配置を設定する
        With .Range(.Cells(wHinmokuSu + 4, 1), .Cells(wHinmokuSu + 4, 2))
        'セルを結合する
            .MergeCells = True
        '罫線を引く
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        '背景色を設定する
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
        '文字の配置を中央にする
            .HorizontalAlignment = xlCenter
        End With
    '2行目の3列目(C列)から順次、店舗名をセットする
        For wCnt = 1 To wTenpoSu
            .Cells(2, wCnt + 2) = wTenpoMei(wCnt)
        'セルの結合、罫線、背景色、文字の配置を設定する
            With .Range(.Cells(2, wCnt + 2), .Cells(3, wCnt + 2))
            'セルを結合する
                .MergeCells = True
            '罫線を引く
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeTop).Weight = xlThin
                .Borders(xlEdgeTop).ColorIndex = xlAutomatic
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Weight = xlThin
                .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Weight = xlThin
                .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            '背景色を設定する
                .Interior.ColorIndex = 37
                .Interior.Pattern = xlSolid
            '文字の配置を中央にする
                .HorizontalAlignment = xlCenter
            End With
        Next wCnt
    '合計列の表示
        .Cells(2, wTenpoSu + 3) = "合計"
        'セルの結合、罫線、背景色、文字の配置を設定する
        With .Range(.Cells(2, wTenpoSu + 3), .Cells(3, wTenpoSu + 3))
        'セルを結合する
            .MergeCells = True
        '罫線を引く
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle  = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight =  xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        '背景色を設定する
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
        '文字の配置を中央にする
            .HorizontalAlignment = xlCenter
        End With
    '4行目の3列目(C列のから集計結果を表示するセル（合計を含む）の罫線、文字の配置を設定する
        For wCnt = 1 To wHinmokuSu + 1
            For wCnt2 = 1 To wTenpoSu + 1
                With .Range(.Cells(4, 3), .Cells(wCnt + 3, wCnt2 + 2))
                '罫線を引く
                    .Borders(xlEdgeBottom).LineStyle = xlContinuous
                    .Borders(xlEdgeBottom).Weight = xlThin
                    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
                    .Borders(xlEdgeRight).LineStyle = xlContinuous
                    .Borders(xlEdgeRight).Weight = xlThin
                    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
                '文字の配置を右にする
                    .HorizontalAlignment = xlRight
                '数値をカンマ付きで表示する
                    .NumberFormatLocal = "#,##0_ "
                End With
            Next wCnt2
        Next wCnt
    '合計列の背景色を設定
        For wCnt = 1 To wHinmokuSu
            With .Range(.Cells(4, wTenpoSu + 3), .Cells(wCnt + 3, wTenpoSu + 3))
            '背景色を設定する
                .Interior.ColorIndex = 36
                .Interior.Pattern = xlSolid
            End With
        Next wCnt
    '合計行の背景色を設定
        For wCnt = 1 To wTenpoSu
            With .Range(.Cells(wHinmokuSu + 4, 3), .Cells(wHinmokuSu + 4, wCnt + 2))
            '背景色を設定する
                .Interior.ColorIndex = 36
                .Interior.Pattern = xlSolid
            End With
        Next wCnt
    '全社、全品目合計の背景色を設定
        With .Range(.Cells(wHinmokuSu + 4, wTenpoSu + 3), _
          .Cells(wHinmokuSu + 4, wTenpoSu + 3))
        '背景色を設定する
            .Interior.ColorIndex = 6
            .Interior.Pattern = xlSolid
        End With
    End With

このページの先頭へ

�@　プログラム中のオブジェクトの表示をまとめる。　「With」命令

　複数のシートがある場合、セルの位置を特定するには、Worksheetオブジェクトから指定して記述しなければなりません。 そうなると、プログラムの記述がかなり煩雑になります。これを解決するために、「With」命令を用います。

構文�@：
  With オブジェクト名
      処理
      ・・・
  End With

  「With オブジェクト名」と「End With」の間に記述する命令は、オブジェクト名を省略でき
  ます。

例：
  Worksheets("A").Cells(1, 1) = "ABC"
  Worksheets("A").Cells(2, 1) = "DEF"
  Worksheets("A").Cells(3, 1) = "GHI"

  このプログラムは、次のように書き換えることができます。

  With Worksheets("A")
      .Cells(1, 1) = "ABC"
      .Cells(2, 1) = "DEF"
  End With

※「With オブジェクト名 . . . End With」は、入れ子にすることができます。

構文�A：
  With オブジェクト名
      処理
      ・・・
      With オブジェクト名
          処理
          ・・・
      End With
  End With

例：
  Worksheets("A").Range("A1:C5").MergeCells = True
  Worksheets("A").Range("A1:C5").Borders(xlEdgeBottom).LineStyle = xlContinuous
  Worksheets("A").Range("A1:C5").Borders(xlEdgeBottom).Weight = xlThin
  Worksheets("A").Range("A1:C5").Borders(xlEdgeBottom).ColorIndex = xlAutomatic
  Worksheets("A").Range("A1:C5").Borders(xlEdgeRight).LineStyle = xlContinuous
  Worksheets("A").Range("A1:C5").Borders(xlEdgeRight).Weight = xlThin
  Worksheets("A").Range("A1:C5").Borders(xlEdgeRight).ColorIndex = xlAutomatic

  このプログラムは、次のように書き換えることができます。

  With Worksheets("A").Range("A1:C5")
      .MergeCells = True
      With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
  End With

このページの先頭へ

�A　セルに値を格納（代入）する。

　セルに変数、定数、式などによる値を代入するには、代入演算子「=」を用います。

構文：
  .Cells(行, 列) = 定数 / 変数 / 式 / 関数

このページの先頭へ

�B　セルを結合する。「MergeCells」プロパティ

　指定した範囲のセルを結合するには、「MergeCells」プロパティを用います。

構文：
  .Range("セル範囲").MergeCells = True

このページの先頭へ

�C　セルに罫線を引く。「Borders」プロパティ

　セルに罫線を引くには、「Borders」プロパティを用います。

構文：
  .Range("セル範囲").Borders(位置の定数）.LineStyle = 線種の定数
  .Range("セル範囲").Borders(位置の定数）.Weight = 線の太さの定数
  .Range("セル範囲").Borders(位置の定数）.ColorIndex = 色の定数

  ●位置の定数には、下図のような値があります。値を直接記述することもできます。
  

  ●線種の定数には、次のような値があります。値を直接記述することもできます。
      xlContinuous     値=1       実線
      xlDash           値=-4115   破線
      xlDashDot        値=4       一点鎖線
      xlDashDotDot     値=5       二点鎖線
      xlDot            値=-4118   点線
      xlDouble         値=-4119   二重線
      xlSlantDashDot   値=13      斜め一点鎖線
      xlLineStyleNone  値=-4142   なし

  ●線の太さの定数には、次のような値があります。値を直接記述することもできます。
      xlHairline       値=1       細線
      xlThin           値=2       標準
      xlMedium         値=-4138   太線
      xlthick          値=4       極太線

  ●色の定数には、カラーパレットの色番号を指定しますが、xlAutomatic(自動)を
    指定すると、通常は黒になります。

このページの先頭へ

�D　セルの背景色を設定する。「Interior」プロパティ

　セルの背景色を設定するには、「Interior」プロパティを用います。

構文：
  .Range("セル範囲").Interior.ColorIndex = 色の定数
  .Range("セル範囲").Interior.Pattern = パターンの定数

  ●色の定数には、カラーパレットの色番号を指定します。

  ●パターンの定数は、模様を指定する値が用意されています。「xlSolid」を指定すると、
    塗りつぶされます。

このページの先頭へ

�E　セル内の文字の配置を設定する。「HorizontalAlignment/
　　　VerticalAlignment」プロパティ

　セル内の文字の配置を設定するには、「HorizontalAlignment(横方向)/VerticalAlignment
(縦方向)」プロパティを用います。

構文�@（横方法）：
  .Range("セル範囲").HorizontalAlignment = 横方向配置の定数
  .Cells(行, 列).HorizontalAlignment = 横方向配置の定数

  ●横方向配置の定数には、次のような値があります。値を直接記述することもできます。

      xlCenter                 値=-4108       中央揃え
      xlLeft                   値=-4131       左揃え
      xlRight                  値=-4152       右揃え
      xlJustify                値=-4130       両端揃え
      xlCenterAcrossSelection  値=7           選択範囲内で中央
      xlDistributed            値=-4117       均等割り付け

構文�A（縦方法）：
  .Range("セル範囲").VerticalAlignment = 縦方向配置の定数
  .Cells(行, 列).VerticalAlignment = 縦方向配置の定数

  ●縦方向配置の定数には、次のような値があります。値を直接記述することもできます。

      xlBottom                 値=-4107       中央揃え
      xlCenter                 値=-4108       左揃え
      xlTop                    値=-4160       右揃え
      xlJustify                値=-4130       両端揃え
      xlDistributed            値=-4117       均等割り付け

このページの先頭へ

�F　セル内の文字の表示形式を設定する。「NumberFormatLocal」プロパティ

　セル内の文字の表示形式を設定するには、「NumberFormatLocal」プロパティを用います。

構文：
  .Cells(行, 列).NumberFormatLocal = 表示形式の文字列

例：
  .Cells(3, 1).NumberFormatLocal = "#,##0_ "
    カンマ付の数字で表示されます。

（次回に続く）

このページの先頭へ

★　次回：「最終回　会社の売上げを集計する�HExcelVBAその７」は、 店舗別のシートに記録された日々の売上金額を品目別に集計して、「月間店別売上集計」シート上に集計金額を表示します。

トップページへ