ExcelVBAで家計簿アプリを作ろう　その10-4

家計簿アプリを、より使いやすく自分仕様に改造していきましょう！

以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）

ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。

前回までで、データ処理をしたいデータを【配列】に入れるところまで説明しました。

今回はその続きで、【配列】に入ったデータを処理していき、処理した結果を貼り付けられるようにしていきたいと思います。

いよいよ実際に【配列】を使ったマクロを書いていきます。
最初は難しいかと思いますが、頑張って【配列】を使いこなし、
ご自身のプログラミングスキルをステージアップさせていきましょう！

【本記事の目標】

配列のデータを処理していこう①

簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
また、前回までの記事については下記をご確認ください。

前回までの状況
データの判定
1. 明細書に記載の内容が、事前準備リストに記載されているか。

前回までの状況

前回までで、
　内容を判定するチェックリスト（事前準備リスト）のデータ（CheckAry1）と、
　ダウンロードした明細書のデータ（MeisaiAry1）
がそれぞれの配列に入っています。

今回はこれら２つのデータを照合し、結果を　ReslutAry1　の配列に入れていき、
最終的に『入力』シートに貼り付けていきます。

現時点でのマクロは下記のようになっています。

Sub CardMeisaiCopy()
'カード明細サンプルを貼り付けるマクロ

Dim FileName1 As String

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Set sh1 = ThisWorkbook.Worksheets("入力")
Set sh2 = ThisWorkbook.Worksheets("カード明細用")
Set sh3 = ThisWorkbook.Worksheets("銀行明細用")
Set sh4 = ThisWorkbook.Worksheets("事前準備リスト")

'配列の宣言
Dim MeisaiAry1() As Variant
Dim ResultAry1() As Variant
Dim CheckAry1() As Variant

'最終行・最終列の変数
Dim LastR1 As Integer
Dim LastC1 As Integer

'チェックリストのデータを配列に入れる
LastR1 = sh4.Cells(2, 1).End(xlDown).Row
LastC1 = sh4.Cells(2, 1).End(xlToRight).Column

CheckAry1 = sh4.Range(sh4.Cells(2, 1), sh4.Cells(LastR1, LastC1)).Value

'ファイルを開く
FileName1 = Application.GetOpenFilename
Workbooks.Open Filename:=FileName1

'明細書のデータを配列に入れる
With ActiveWorkbook.Sheets(1)
    LastR1 = .Cells(2, 1).End(xlDown).Row
    LastC1 = .Cells(2, 1).End(xlToRight).Column

    MeisaiAry1 = .Range(.Cells(2, 1), .Cells(LastR1, LastC1)).Value
End With

'ファイルを閉じる
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

また、内容を判定するチェックリスト（事前準備リスト）のデータ（CheckAry1）と、
ダウンロードした明細書のデータ（MeisaiAry1）の配列には、それぞれ下記のようなデータが入っているはずです。

これを念頭に置きながら進めていきましょう。

データの判定

ダウンロードした明細書のデータに対してどういった確認をしていくかですが、
前回配列を使わずに実施した確認・分別を行った内容を、そのままトレースしていきます。

前回配列を使わずに行った動きについては、下記記事をご確認ください。

具体的な確認項目としては２点です。

　１．明細書に記載の内容が、事前準備リストに記載されているか。
　　　記載されていれば、対応する勘定科目を表示

　２．明細書に記載の日付はいつか。今回反映させたい月のデータがどうか。

それぞれ詳しく説明していきます。

明細書に記載の内容が、事前準備リストに記載されているか。

まずは明細書の「内容」もしくは「支払先」欄に記載の内容が、事前準備リストに記載されている項目なのかを確認していきます。

人が確認するときの動きをイメージすると、

　①　明細書の１つ目の「支払先」を確認する
　②　同じ名前が『事前準備リスト』の「内容」の欄に記載されているかどうか、
　　　リストの上から順に確認する
　③　同じ「内容」が記載されていたら、
　　　その行の「勘定科目」の欄のデータを所定の場所に記載する。
　　　それ以外のデータ（金額、内容、日付）も所定の場所に記載する。
　④　明細書の次の行について、１．～３．を繰り返す

という流れで確認していくと思います。
ですので、同じことをプログラムで組んであげればOKです。

実際にこの部分だけのプログラムを組むと、下記のようになります。

Dim i As Integer    '繰り返し用記号
Dim j As Integer    '繰り返し用記号

'結果を入れるResultAry1を準備する。
'結果に入れるのは、1行目：勘定科目、2行目：金額、3行目：内容、4行目：日付、の4項目
ReDim ResultAry1(1 To 4, 1 To 1)

'明細書のデータを1行ずつ繰り返しチェックしていく
For i = LBound(MeisaiAry1, 1) To UBound(MeisaiAry1, 1)
    
    '事前準備リストに同名の記載があるか繰り返しチェックしていく
    For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)
        '「支払先」と「内容」が一致するか確認
        If MeisaiAry1(i, 2) = CheckAry1(j, 1) Then
            '一致した場合
            '結果を入れるためにReslutAry1の列を１つ大きくする
            ReDim Preserve ResultAry1(1 To 4, 1 To UBound(ResultAry1, 2) + 1)
            
            'データを入れていく
            ResultAry1(1, UBound(ResultAry1, 2) - 1) = CheckAry1(j, 2)  '勘定科目
            ResultAry1(2, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 3)  '金額
            ResultAry1(3, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 2)  '内容
            ResultAry1(4, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 1)  '日付
            
            Exit For
        
        '一致しなくてCheckAry1の最後まで見終わった場合
        ElseIf j = UBound(CheckAry1, 1) Then
            '結果を入れるためにReslutAry1の列を１つ大きくする
            ReDim Preserve ResultAry1(1 To 4, 1 To UBound(ResultAry1, 2) + 1)
            
            '勘定科目以外のデータを入れていく
            ResultAry1(2, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 3)  '金額
            ResultAry1(3, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 2)  '内容
            ResultAry1(4, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 1)  '日付
            
            Exit For
        End If
    Next j
Next i

複雑に見えますが、1行ずつ確認していけば、どんなことをしているのかが理解できると思います。
少しずつ説明していきますね。

使用する変数を定義

Dim i As Integer ‘繰り返し用記号
Dim j As Integer ‘繰り返し用記号

まず最初に、ここで使う変数を定義しています。
今回の動きでは2回の繰り返しがあります。
　　１回目：明細書のデータを1行ずつチェックしていく繰り返し
　　２回目：事前準備リストを上から順にチェックしていく繰り返し
そのため、繰り返しに使う記号も２つ定義しておく必要があります。

結果を入れるための配列を準備

‘結果を入れるResultAry1を準備する。
‘結果に入れるのは、1行目：勘定科目、2行目：金額、3行目：内容、4行目：日付、の4項目
ReDim ResultAry1(1 To 4, 1 To 1)

ResultAry1という配列はもともと定義済みですが、配列のサイズを指定できていなかったため、
データを入れていく前に指定しておく必要があります。
そのため、ここでReDimを使って再定義しています。
ReDimについては以前の記事もご確認ください。

今回結果として貼り付けたいデータは、
　　『勘定科目』『金額』『内容（支払先）』『日付』
の4項目ですので、4列の配列を作ればOKです。
ただ、結果として貼り付けたいデータの行数は現時点では分からないですよね。
そのため、データを入れていきながら、配列のサイズが足りなくなった時にサイズを大きくする、という動きをさせる必要があります。
ただ、配列の中のデータを残しつつ配列のサイズを変更する場合は、2次元配列の場合は「列」しか変更できません。
なので、上記の4項目を行に入れておき、明細書の各行の内容をResultAry1の各列に入れていき、
全てのデータを入れ終わってから行列変換すれば、もともと作りたかった4列の配列にすることが出来ます。

そのため、最初のResultAry1の定義では　4行1列　の配列として定義しています。

繰り返しをしていく

‘明細書のデータを1行ずつ繰り返しチェックしていく
For i = LBound(MeisaiAry1, 1) To UBound(MeisaiAry1, 1)

‘事前準備リストに同名の記載があるか繰り返しチェックしていく
For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)

ここから実際のチェック作業に入っていきます。

まずは明細書のデータを1行ずつ確認するための繰り返しをしていきます。
ただ、繰り返しをするには「何回繰り返せばいいのか」が分からないと、繰り返しを設定できませんよね。
今回の場合、明細書のデータが入っている配列を1行目から最終行目までを確認できれば良いので、配列の最終行が分かれば繰り返しの設定ができます。

配列の最初や最後の行・列を確認するためのコマンドが　LBound、UBound　です。
それぞれ
　LBound：最初の配列番号を取得する。引数には配列名と次元数（１：行、２：列）を指定
　　　　　　LBound（配列名, 次元数）
　UBound：最後の配列番号を取得する。引数には配列名と次元数（１：行、２：列）を指定
　　　　　　UBound（配列名, 次元数）
という使い方をします。

今回の場合、明細書のデータ（MeisaiAry1）の最初の行から最後の行までを確認していくため、
　For i = LBound(MeisaiAry1, 1) To UBound(MeisaiAry1, 1)
としています。

またその後、明細書の各行にある内容（支払先）が事前準備リストに記載されているか確認するため、
事前準備リストの上から確認する繰り返しも設定しています。
こちらも同じように、事前準備リストの最初の行から最後の行までを確認する必要があるため、
　For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)
としています。

内容（支払先）が事前準備リストに記載されているかチェック

‘「支払先」と「内容」が一致するか確認
If MeisaiAry1(i, 2) = CheckAry1(j, 1) Then

繰り返しを設定したので、あとはチェックする内容を記載していけばOKです。
今回は明細書に記載の「支払先」が、事前準備リストの「内容」に記載されているか確認していきます。
それぞれ、
　　明細書に記載の「支払先」はMeisaiAry1の配列の２列目
　　事前準備リストの「内容」はCheckAry1の配列の１列目
に記載されているため、これらが一致するかを判定すればいいのです。

記載されていた場合の動き

‘結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 4, 1 To UBound(ResultAry1, 2) + 1)

‘データを入れていく
ResultAry1(1, UBound(ResultAry1, 2) – 1) = CheckAry1(j, 2) ‘勘定科目
ResultAry1(2, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 3) ‘金額
ResultAry1(3, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 2) ‘内容
ResultAry1(4, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 1) ‘日付

明細書に記載の「支払先」が事前準備リストの「内容」に記載されていた場合、そこに記載の「勘定科目」を結果の配列（ResultAry1）に入力していきます。

ただ、ResultAry1は4行1列の配列になっているので、1行分のデータしか入れられませんよね。
そのため、今から繰り返しチェックをしてデータを入れていくのに、1行分だけ入れて終わってしまうと意味がないので、先にサイズを拡張しておきます。

ここでサイズを拡張する場合は、今後ResultAry1にデータが入った状態でサイズを拡張する可能性があるため、ReDim　ではなく　ReDim Preserve　でサイズ拡張をしています。

また、繰り返しをしていくと、ResultAry1の配列のサイズがどんどん大きくなるため、サイズ拡張の設定は単純に　最終列に１列追加する　という形にしておけば、配列がどのぐらい大きくなったかを気にしなくても問題なく動くようになります。
そのため、先ほど使った　UBound　を使って
　ReDim Preserve ResultAry1(1 To 4, 1 To UBound(ResultAry1, 2) + 1)
という形にしています。

後は単純にデータを入れていくだけですので、ResultAry1それぞれの行に、明細書のデータ（MeisaiAry1）や事前準備リスト（CheckAry1）の勘定科目を入力していきます。
注意点として、明細書の繰り返し記号は『i』、事前準備リストの繰り返し記号は『j』を使っていますので、どちらをどちらに使うのかを間違えないようにご注意ください。

チェックが終わったら事前準備リストの繰り返しを止めて、明細書の次の行へ

Exit For

チェックが終わったら、それ以降の事前準備リストを確認する必要はありませんよね。
すでに一致したものがあったので。

であれば、それ以降の繰り返しをせずに、明細書の次の行のチェックへ行った方が効率が良いですよね。

このように、繰り返し動作をそれ以降実行しないようにする、
要は繰り返しのループの外に出るためのコマンドが　Exit For　です。

今回の場合、

For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)
　～
Next j

この繰り返しの外に出ていきます。

Exit Forでは、今いる繰り返し動作の部分だけの外に出るため、繰り返しが複数連なっている場合も１つ分の繰り返しの外に出るだけです。

事前準備リストに一致する内容がなかった場合の動き

‘結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 4, 1 To UBound(ResultAry1, 2) + 1)

‘データを入れていく
ResultAry1(2, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 3) ‘金額
ResultAry1(3, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 2) ‘内容
ResultAry1(4, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 1) ‘日付

Exit For

事前準備リストに一致する「内容」が無かった場合にどうするのか。

一致しなかったとしても、勘定科目のデータがないだけで、他の「金額」「内容」「日付」のデータは結果の配列（ResultAry1）に入れておく必要がありますよね。
そのため、基本的には一致したときと同じコマンドを書いておいて、勘定科目を入力する部分だけ消しておけばOKです。

チェック済みの配列が完成

これで、明細書に記載の内容をチェックし、勘定科目を自動入力して結果の配列に入れていくことが出来ました。
あとはこの結果の配列を『入力シート』に貼り付けたらOKなのですが、現時点だと明細書に記載の全てのデータを貼り付けてしまっていますよね。
『入力シート』は月ごとに入力したいので、貼り付ける配列も『入力シート』の月と一致するデータだけに厳選しておきたいです。

そのため、次回以降で今回作った繰り返し動作の中に、日付を確認するプログラムも組み込んでいこうと思います。

次の記事へ