この回から、プログラムソースの内容を説明します。
プログラムソース
下記に示すのが、今回のサンプルプログラム「kitaoosaka_shokuhin.xls」のプログラムソースです。 前回説明した「Microsoft Visual Basic」エディタに記述されます。
※ 人が理解できるプログラム言語で記述されたものを「ソース(”源”の意)」と言います。コンピュータは、
プログラムソースを実行可能な形式に翻訳してから、実行します。
Option Explicit
Private Sub cmdShukei_Click()
'売上集計を実行する
'変数の宣言
Dim wCnt As Integer 'カウンタ用の変数
Dim wCnt2 As Integer 'カウンタ用の変数
Dim wTenpoSu As Integer '店舗マスターに登録された店舗の数
Dim wHinmokuSu As Long '品目マスターに登録された品目の数
Dim wTuki As Integer '対象月
Dim wNen As Integer '対象月の属する年
Dim wHiduke_S As Date '売上日の抽出範囲始め
Dim wHiduke_E As Date '売上日の抽出範囲終わり
Dim wDataSu As Long '各店舗の売上データ数
Dim wSentoGyo As Long '売上データの対象月先頭
Dim wSetSento As Byte 'wSentoGyoに値がセットされたら、1を立てる
Dim wSaishuGyo As Long '売上データの対象月最終行
Dim wGokeiKingaku As Currency '全社売上合計金額
Dim wRecNo As Long '品目コード検索結果のレコードNo
'配列の宣言
Dim wTenpoMei() As String '店舗名を格納する配列
Dim wHinmokuCd() As Long '品目コードを格納する配列
Dim wHinmokuMei() As String '品目名を格納する配列
Dim wKingaku() As Currency '店舗別、品目別売上集計金額を格納する配列
Dim wTenpoKingaku() As Currency '店舗別合計金額を格納する配列
Dim wHinmokuKingaku() As Currency '品目別合計金額を格納する配列
'対象月から抽出する売上日の範囲を算出する
If Len(Worksheets("月間店別売上集計").Cells(1, 1)) > 0 Then
If IsNumeric(Worksheets("月間店別売上集計").Cells(1, 1)) Then
If CInt(Worksheets("月間店別売上集計").Cells(1, 1)) >= 1
And CInt(Worksheets("月間店別売上集計").Cells(1, 1)) <= 12 Then
wTuki = CInt(Worksheets("月間店別売上集計").Cells(1, 1))
If wTuki > Month(Now) Then
wNen = Year(Now) - 1
Else
wNen = Year(Now)
End If
wHiduke_S = CDate(Format(wNen, "#") & "/" & Format(wTuki, "#") & "/1")
wHiduke_E = DateAdd("m", 1, wHiduke_S)
wHiduke_E = DateAdd("d", -1, wHiduke_E)
Else
MsgBox "正しい月を入力してください。", vbOKOnly, "エラー"
Exit Sub
End If
Else
MsgBox "正しい月を入力してください。", vbOKOnly, "エラー"
Exit Sub
End If
Else
MsgBox "対象月を入力してください。", vbOKOnly, "エラー"
Exit Sub
End If
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
'「月間店別売上集計」シートに店舗名と品目名をセットする
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
'各店舗の売上データを処理する
wGokeiKingaku = 0
ReDim wKingaku(wHinmokuSu, wTenpoSu)
ReDim wTenpoKingaku(wTenpoSu)
ReDim wHinmokuKingaku(wHinmokuSu)
For wCnt = 1 To wTenpoSu
With Worksheets(wTenpoMei(wCnt))
'売上データ数を取得
wDataSu = 0
wCnt2 = 4
Do Until Len(.Cells(wCnt2, 1)) = 0
wDataSu = wDataSu + 1
wCnt2 = wCnt2 + 1
Loop
'データがある場合に処理
If wDataSu > 0 Then
'データを日付順にソートする
.Range("A5:C" & Format(wDataSu + 3, "#")).Sort Key1:=.Range("A4"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod&:=xlPinYin
'対象月の先頭行と最終行を求める
wSetSento = 0
wSentoGyo = 0
wSaishuGyo = 0
For wCnt2 = 4 To wDataSu + 3
If wSetSento = 0 Then
If .Cells(wCnt2, 1) >= wHiduke_S Then
wSentoGyo = wCnt2
wSetSento = 1
End If
End If
If .Cells(wCnt2, 1) >= wHiduke_S And .Cells(wCnt2, 1) <= wHiduke_E Then
wSaishuGyo = wCnt2
ElseIf .Cells(wCnt2, 1) > wHiduke_E Then
Exit For
End If
Next wCnt2
If wSentoGyo > 0 Then
'対象月の売上データがある場合、品目別に集計する
For wCnt2 = 1 To wHinmokuSu
wKingaku(wCnt2, wCnt) = Application.WorksheetFunction.SumIf( _
.Range("B" & Format(wSentoGyo, "#") & ":B" & Format(wSaishuGyo, "#")), _
wHinmokuCd(wCnt2), .Range("H" & Format(wSentoGyo, "#") & ":H" _
& Format(wSaishuGyo, "#")))
wHinmokuKingaku(wCnt2) = wHinmokuKingaku(wCnt2) + wKingaku(wCnt2, wCnt)
wTenpoKingaku(wCnt) = wTenpoKingaku(wCnt) + wKingaku(wCnt2, wCnt)
wGokeiKingaku = wGokeiKingaku + wKingaku(wCnt2, wCnt)
Next wCnt2
End If
End If
End With
Next wCnt
'集計金額を「月間店別売上集計」シートにセットする
For wCnt = 1 To wHinmokuSu
For wCnt2 = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wCnt2 + 2) = wKingaku(wCnt, wCnt2)
Next wCnt2
Next wCnt
'品目別合計金額をセット
For wCnt = 1 To wHinmokuSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wTenpoSu + 3) = wHinmokuKingaku(wCnt)
Next wCnt
'店舗別合計金額をセット
For wCnt = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wCnt + 2) = wTenpoKingaku(wCnt)
Next wCnt
'全社合計金額
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wTenpoSu + 3) = wGokeiKingaku
End Sub
変数の宣言を強制する
プログラムソースを順に見て行きましょう。
Option Explicit
これは、「変数の宣言を強制する」という意味です。
① Microsoft Visual Basic エディタのメニューの「ツール(T)」→「オプション(O)」をクリックする
と「オプション」ダイアログボックスが表示されます。
② 「編集」タブの「変数の宣言を強制する(R)」にチェックを入れると、新しくVisual Basic エディ
タを開いたときに、「Option Explicit」が自動入力されます。
変数宣言は、次の項目で説明します。
※ なぜ、変数の宣言を強制するの？
VBAは、変数の宣言を強制しなくても実行することが可能なプログラム言語です。しかし、その場合、
誤った変数を記述したときもプログラムは実行されてしまいます。そうなると、誤った処理結果になる
恐れがあり、それに気付くことが難しくなります。変数の宣言を強制すると、宣言されていない変数が
使われているとプログラムはエラーになり、途中で実行が停止するので、プログラムの誤りを見つけ
ることが容易になるのです。
サブルーチン
Private Sub cmdShukei_Click()
'売上集計を実行する
End Sub
「Private Sub cmdShukei_Click()」～「End Sub」をサブルーチンといい、プログラムの実行単位です。
この中に、実行するプログラムのソースを記述します。
「cmdShukei」は、配置したコマンドボタンに付けた名前です。コマンドボタン名に続く「_Click()」は、
cmdShukeiをクリックしたときに実行されるという意味を持ちます。
※ 先頭に「'」が付いたプログラム文は、コメントといい、プログラム実行時に無視されます。
プログラムの説明に用います。プログラムソースの記述が長くなると、プログラムの流れが分かり
にくくなります。作者以外の人が見ても理解できるように、コメントを入れます。
コメントは、行の途中から入れることもできます。
変数、配列を宣言する
① 変数を宣言する
'変数の宣言
Dim wCnt As Integer 'カウンタ用の変数
Dim wCnt2 As Integer 'カウンタ用の変数
Dim wTenpoSu As Integer '店舗マスターに登録された店舗の数
Dim wHinmokuSu As Long '品目マスターに登録された品目の数
Dim wTuki As Integer '対象月
Dim wNen As Integer '対象月の属する年
Dim wHiduke_S As Date '売上日の抽出範囲始め
Dim wHiduke_E As Date '売上日の抽出範囲終わり
Dim wDataSu As Long '各店舗の売上データ数
Dim wSentoGyo As Long '売上データの対象月先頭
Dim wSetSento As Byte 'wSentoGyoに値がセットされたら、1を立てる
Dim wSaishuGyo As Long '売上データの対象月最終行
Dim wGokeiKingaku As Currency '全社売上合計金額
Dim wRecNo As Long '品目コード検索結果のレコードNo
サブルーチン「cmdShukei_Click()」の冒頭に、このサブルーチンで使用する変数を宣言しています。
変数というのは、プログラムで使用する値（数値、文字など）を一時的に格納するための容器だと考えられます。
「変数の宣言を強制する」の節で説明した「Option Explicit」を冒頭に記述すると、宣言された変数しか
プログラム中で使用することができません。変数の宣言を詳しく見ていきましょう。
構文：
Dim 変数名 As データ型
Dim 変数を宣言するための命令文です。
変数名 変数には、任意の名前をつけることができます。
As データ型を定義するためのキーワードです。
データ型 変数に格納するデータの種類です。
サンプルプログラムに出てくるデータ型：
Byte 正の整数(0～255)を格納できます。
Integer 整数型の数値(-32,768～32,767)を格納できます。
Long integerより大きな整数型の数値(-2,147,483,648～2,147,483,647)
を格納できます。
Date 日付(西暦100年1月1日～9999年12月31日)を格納できます。
Currency 整数部15桁、小数部4桁の固定少数点数(-922,337,203,685,477.5808～
922,337,203,685,477.5807)を格納できます。
String 文字列を格納できます。
② 配列を宣言する
'配列の宣言
Dim wTenpoMei() As String '店舗名を格納する配列
Dim wHinmokuCd() As Long '品目コードを格納する配列
Dim wHinmokuMei() As String '品目名を格納する配列
Dim wKingaku() As Currency '店舗別、品目別売上集計金額を格納する配列
Dim wTenpoKingaku() As Currency '店舗別合計金額を格納する配列
Dim wHinmokuKingaku() As Currency '品目別合計金額を格納する配列
配列は、複数のデータをまとめて格納できる変数です。
構文：
Dim 変数名(引数) As データ型
前回説明した変数の宣言と同じ構文ですが、変数名の後に引数を括弧で囲みます。
引数は、多次元の指定が可能です。次に配列の例を示します。
●一次元配列
Dim wFukenMei(47) As String '47個の都道府県名を格納することができる
wFukenMei(1) = "北海道"
wFukenMei(2) = "青森県"
wFukenMei(3) = "岩手県"
・・・・・・・・・・
●二次元配列
Dim wFukenMei(47, 185) As String '47×185個までの市町村名を格納することができる
wFukenMei(1, 1) = "北海道札幌市"
wFukenMei(1, 2) = "北海道函館市"
wFukenMei(1, 3) = "北海道小樽市"
・・・・・・・・・・
wFukenMei(2, 1) = "青森県青森市"
wFukenMei(2, 2) = "青森県弘前市"
wFukenMei(2, 3) = "青森県八戸市"
・・・・・・・・・・
引数は、上記のように固定で宣言することができますが、配列の大きさがデータ数で変動
する場合は、データ数を計算した後で、宣言することもできます。
この場合、変数名の後ろの括弧の中を空にします。
例：
Dim wTenpoMei() As String '店舗名を格納する配列
With Worksheets("マスター")
'「マスター」シートの1列目(A列)を順次読み込む
wCnt = 3
wTenpoSu = 0
Do Until Len(.Cells(wCnt, 1)) = 0
wTenpoSu = wTenpoSu + 1
wCnt = wCnt + 1
Loop
ReDim wTenpoMei(wTenpoSu)
・・・・・・・・・・
ここで宣言された変数、配列が、これ以降のプログラム中で使用されます。
（次回に続く）
★ 次回：「第６回 会社の売上げを集計する⑥ExcelVBAその４」は、 引き続きプログラムソースを見ていきます。Excelシートのセルの操作やデータの処理について詳しく説明します。