家計簿アプリを、より使いやすく自分仕様に改造していきましょう！
以前の投稿で、家計簿アプリとして最低限の機能を持たせることができました。
（その６までをご確認いただいていない方は、まずは下記まとめをご確認ください。）
ただ、これは本当に最低限の機能しかなく、勘定科目も『固定費』と『変動費』しかないので、
各家庭に合った勘定科目を自由に追加できるように、
このマクロのデコレーションをしていく必要があります。
前回までで、配列を使ってデータの取得・操作・貼り付けをする方法について説明しました。
今回はそのおさらいも兼ねて、完成したプログラムを１行ずつ確認し、その動きや内容を解読・把握していきたいと思います。
【配列】も使えるようになって、他の人が作ったプログラムを確認・解読することが出来るようになれば、もう十分中級者の仲間入りです♪
あとはどんどん他の人のプログラムを見ていき、
自分がやりたいことの参考になれば、どんどん取り込んでいき、
ご自身のプログラミングスキルをレベルアップさせていきましょう！
【本記事の目標】
他の人が作ったプログラムを解読してみよう
簡単なイメージは下記記事に記載していますので、イメージづくりや今回以降の流れの参考に。
また、前回までの記事については下記をご確認ください。
まずは全体像を確認しよう
今回確認するのは、前回までに作った『カード明細のデータから自動入力』をするマクロとほぼ同じ動きをする、『銀行明細のデータから自動入力』をするマクロです。
ただ、全く同じですと練習にならないので、あえて細かい部分を変えてあります。
実際に作成したプログラムは下記の通りです。
以降でこのプログラムについて少しずつ解説をしていきますが、
もし練習してみたい方は、以降の解説を見る前に、このプログラムを見て自分なりに解読を指定見ていただくと良いと思います。
Sub BankMeisaiCopy()
'銀行明細サンプルを貼り付けるマクロ
'=========事前準備===========
'各種変数の定義
Dim FileName1 As String
Dim i As Integer '繰り返し用記号
Dim j As Integer '繰り返し用記号
Dim SearchYear1 As Integer
Dim SearchMonth1 As Integer
'最終行・最終列の変数
Dim LastR1 As Integer
Dim LastC1 As Integer
'シートの定義
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 CheckAry2() As Variant
'チェックリストのデータを配列に入れる
With sh4
LastR1 = .Cells(2, 1).End(xlDown).Row
LastC1 = .Cells(2, 1).End(xlToRight).Column
CheckAry1 = .Range(.Cells(2, 1), .Cells(LastR1, LastC1)).Value
End With
'入力シートの年・月のデータを取得する
With sh1
SearchYear1 = .Range("M4").Value
SearchMonth1 = .Range("O4").Value
End With
'入力シートのI列・J列に記載の固定/変動と勘定科目の対応表を配列に入れる
With sh1
LastR1 = .Cells(10, 10).End(xlDown).Row
CheckAry2 = .Range(.Cells(10, 9), .Cells(LastR1, 10)).Value
End With
'=========データ取得===========
'ファイルを開く
FileName1 = Application.GetOpenFilename
Workbooks.Open Filename:=FileName1
'明細書のデータを配列に入れる
With ActiveWorkbook.Sheets(1)
LastR1 = .Cells(1, 1).End(xlDown).Row
LastC1 = .Cells(1, 1).End(xlToRight).Column
MeisaiAry1 = .Range(.Cells(2, 1), .Cells(LastR1, LastC1)).Value
End With
'ファイルを閉じる
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
'=========データの整理===========
'結果を入れるResultAry1を準備する。
'結果に入れるのは、1行目：収支、2行目：固定費/変動費、3行目：勘定科目、
'4行目：金額、5行目：内容、6行目：日付、の6項目
ReDim ResultAry1(1 To 6, 1 To 1)
'明細書のデータを1行ずつ繰り返しチェックしていく
For i = LBound(MeisaiAry1, 1) To UBound(MeisaiAry1, 1)
If MeisaiAry1(i, 1) Like SearchYear1 & "." & SearchMonth1 & ".*" Then
'事前準備リストに同名の記載があるか繰り返しチェックしていく
For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)
'「支払先」と「内容」が一致するか確認
If MeisaiAry1(i, 2) = CheckAry1(j, 1) Then
'一致した場合
'結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 6, 1 To UBound(ResultAry1, 2) + 1)
'勘定科目のデータを入れていく
ResultAry1(3, UBound(ResultAry1, 2) - 1) = CheckAry1(j, 2) '勘定科目
Exit For
'一致しなくてCheckAry1の最後まで見終わった場合
ElseIf j = UBound(CheckAry1, 1) Then
'結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 6, 1 To UBound(ResultAry1, 2) + 1)
'勘定科目を入力しないので、何もせずに終了
Exit For
End If
Next j
'残ったデータを入力していく（内容・日付・金額）
ResultAry1(5, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 2) '内容
ResultAry1(6, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 1) '日付
'金額が、収入が3列目、支出が4列目のため、どちらの列にデータがあるか確認して入れていく。
If MeisaiAry1(i, 3) <> "" Then
ResultAry1(4, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 3) '金額
ElseIf MeisaiAry1(i, 4) <> "" Then
ResultAry1(4, UBound(ResultAry1, 2) - 1) = MeisaiAry1(i, 4) '金額
End If
'勘定科目を確認して、収支と固定費/変動費がどれかを確認する。
'勘定科目が空欄の場合はチェックしない
If ResultAry1(3, i) <> "" Then
For j = LBound(CheckAry2, 1) To UBound(CheckAry2, 1)
'「勘定科目」が一致するか確認
If ResultAry1(3, i) = CheckAry2(j, 2) Then
'一致した場合
'固定費/変動費のデータを入れていく
ResultAry1(2, UBound(ResultAry1, 2) - 1) = CheckAry2(j, 1)
'収支データを入れていく
If CheckAry2(j, 1) = "" Then
ResultAry1(1, UBound(ResultAry1, 2) - 1) = "収入"
Else
ResultAry1(1, UBound(ResultAry1, 2) - 1) = "支出"
End If
Exit For
End If
Next j
End If
End If
Next i
ResultAry1 = Application.WorksheetFunction.Transpose(ResultAry1)
'貼り付け先の最終行を探し、結果を貼り付ける
With sh1
LastR1 = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range(.Cells(LastR1 + 1, 2), .Cells(LastR1 + 1 + _
(UBound(ResultAry1, 1) - LBound(ResultAry1, 1)) _
, 2 + _
(UBound(ResultAry1, 2) - LBound(ResultAry1, 2)) _
)) = ResultAry1
End With
End Sub
内容の解説
それではこのプログラムの内容について、少しずつ解説をしていきます。
事前準備部分
‘各種変数の定義
Dim FileName1 As String
Dim i As Integer ‘繰り返し用記号
Dim j As Integer ‘繰り返し用記号
Dim SearchYear1 As Integer
Dim SearchMonth1 As Integer
‘最終行・最終列の変数
Dim LastR1 As Integer
Dim LastC1 As Integer
この部分で、このプログラムで使う各種変数の定義をしています。
「As String」：文字列変数として定義
「As Integer」：整数の数値変数として定義
‘シートの定義
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(“事前準備リスト”)
プログラム中でワークシートを操作する際
ThisWorkbook.Worksheets(～)
という形で各種操作ができるようになるのですが、毎回この文言を入力するのは手間なので、
上記のようにワークシート自体を変数に代入してあげることで、
上記の長い文言を使わずに、定義した変数名で代用して操作することが出来ます。
プログラミングの時短化・可読性アップのテクニックです。
Dim □ As Worksheet：変数を定義。変数の定義だけなので中身は空っぽ。
Set □ = ThisWorkbook.Worksheets(“○○”)：定義した変数に指定したワークシートを代入。
‘配列の定義
Dim MeisaiAry1() As Variant
Dim ResultAry1() As Variant
Dim CheckAry1() As Variant
Dim CheckAry2() As Variant
このプログラムで使う配列を定義しています。
配列のサイズや入力されるデータの内容も未定（文字列？数値？）なので、
動的配列かつVariant型で定義しています。
‘チェックリストのデータを配列に入れる
With sh4
LastR1 = .Cells(2, 1).End(xlDown).Row
LastC1 = .Cells(2, 1).End(xlToRight).Column
CheckAry1 = .Range(.Cells(2, 1), .Cells(LastR1, LastC1)).Value
End With
事前準備リストのシートに記載の内容を【CheckAry1】の配列に代入しています。
事前準備リストは下記図のような表になっており、どこまでデータが記載されているか確認するため、
.Cells(2, 1).End(xlDown).Row：
”A2”セルで「Ctrl＋↓」を押したときに移動した先のカーソルの行数
.Cells(2, 1).End(xlToRight).Column：
”A2”セルで「Ctrl＋→」を押したときに移動した先のカーソルの列数
を使って表の一番端の部分を探索しています。
（今回は”A2”セルから移動させましたが、”A1”セルからでも問題ないです）
”A2”セルから、表の一番端の部分までのデータを、CheckAry1の配列に代入しています。
‘入力シートの年・月のデータを取得する
With sh1
SearchYear1 = .Range(“M4”).Value
SearchMonth1 = .Range(“O4”).Value
End With
明細のデータの中から入力シートに記載の年/月のデータだけを抽出できるようにするため、
入力シートの年/月の情報を変数に代入しています。
‘入力シートのI列・J列に記載の固定/変動と勘定科目の対応表を配列に入れる
With sh1
LastR1 = .Cells(10, 10).End(xlDown).Row
CheckAry2 = .Range(.Cells(10, 9), .Cells(LastR1, 10)).Value
End With
勘定科目は事前準備リストの表を使ってチェックができますが、
その勘定科目が『収入？支出？』『固定費？変動費？』というのは分からない。
そこで、その判定をするため入力シートにある下記図の部分のデータについても【CheckAry2】の配列に代入しています。
”J10”セルで「Ctrl＋↓」を押したときに移動した先のカーソルの行数を確認し、
”I10”セルからJ列の一番端のセルまでの情報を【CheckAry2】の配列に代入しています。
データ取得部分
‘ファイルを開く
FileName1 = Application.GetOpenFilename
Workbooks.Open Filename:=FileName1
データの事前準備が終わったので、いよいよ明細のデータを開いてデータを取得していきます。
Application.GetOpenFilename：ファイル名を取得するダイアログを開くコマンド。
ダイアログを開くだけでファイルの操作をするわけではないが、
ダイアログで選択したファイルのファイル名・ファイルパスを取得できる。
Workbooks.Open：指定したファイル名のファイルを開く
‘明細書のデータを配列に入れる
With ActiveWorkbook.Sheets(1)
LastR1 = .Cells(1, 1).End(xlDown).Row
LastC1 = .Cells(1, 1).End(xlToRight).Column
MeisaiAry1 = .Range(.Cells(2, 1), .Cells(LastR1, LastC1)).Value
End With
開いたファイルに記載されている内容を、【MeisaiAry1】の配列に代入する。
配列に代入するときは、ファイルに記載されている内容を全て配列に代入する必要があるため、
.End のコマンドを使ってデータの一番端を確認したうえで、配列に代入する。
‘ファイルを閉じる
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
データさえ配列に入ってしまえば、明細のデータのファイルは不要になるため、
ファイルを閉じる必要がある。
ファイルを閉じる際は ActiveWorkbook.Close を入力すれば、一番直近で開いて操作していたアクティブ状態のファイルを閉じることが出来ます。
ただファイルを閉じるとき、もしわずかでも編集していたら「保存しますか？」というアラートが出てきてしまい、プログラムが止まってしまう懸念があります。
このアラートを一時的に出ないようにするのが、
Application.DisplayAlerts = False
です。
このコマンドの以降のプログラムではアラートが一切出なくなります。
重要なアラートも出てこなくなるため、上記で一旦解除した後は必ず
Application.DisplayAlerts = True
を使ってアラートが出てくる設定に戻すことを忘れずに。
データの整理部分
‘結果を入れるResultAry1を準備する。
‘結果に入れるのは、1行目：収支、2行目：固定費/変動費、3行目：勘定科目、
‘4行目：金額、5行目：内容、6行目：日付、の6項目
ReDim ResultAry1(1 To 6, 1 To 1)
最終的に『入力シート』に貼り付けるためのデータを入れる配列としてResultAry1を定義していましたが、配列のサイズまで定義できていなかったので、ここで配列のサイズを再定義しています。
『入力シート』に貼り付けるときは、
・ 収支
・ 勘定科目（固定費/変動費）
・ 詳細科目
・ 金額
・ 内容
・ 日付
の情報を入力する必要があるので、この6項目を入れられるように6行1列の配列を作ります。
結果貼り付け時は6列の配列として貼り付けるので、ここで再定義するときも1行6列にすることもできるのですが、この配列は明細書のデータ数に合わせてサイズを大きくしていく必要があり、2次元配列でデータを残しながらサイズを大きくする場合は列しか変更できないため、
6行1列の配列を、6行2列→6行3列→・・・と配列のサイズを変更しながらデータを入力していき、
最後に行列変換をすることでn行6列の配列として貼り付けられるようにしています。
‘明細書のデータを1行ずつ繰り返しチェックしていく
For i = LBound(MeisaiAry1, 1) To UBound(MeisaiAry1, 1)
結果を入れる配列も準備できたので、実際に明細書のデータを1行ずつ確認していきます。
明細書のデータはMeisaiAry1の配列に入っているので、その配列を1行目から最終行目まで繰り返し確認をします。
If MeisaiAry1(i, 1) Like SearchYear1 & “.” & SearchMonth1 & “.*” Then
『入力シート』に貼り付けるデータは、そのシートの年/月のデータだけなので、それ以外のデータは特に操作は不要です。
そのため、明細書のデータの中で『入力シート』の年/月と同じ日付があったときだけデータ整理をして、それ以外の場合は何もしないようにしています。
ここでは、明細書のデータの1列目に「2023.9.1」といった形式でデータが入っていると想定して、
「2023.9.*」に近い文字列が入っているかどうかを判定しています。
（*はワイルドカード（どんな文字列でもOK）です）
‘事前準備リストに同名の記載があるか繰り返しチェックしていく
For j = LBound(CheckAry1, 1) To UBound(CheckAry1, 1)
明細書のデータのを整理する際、まず初めにそのデータがどの勘定科目に相当するかチェックをします。
チェックの方法は、明細書のデータに記載の内容が、事前準備リストの文言と同じかどうかを、事前準備リストの1行目から最終行目までを1つずつ確認しながら見ていきます。
‘「支払先」と「内容」が一致するか確認
If MeisaiAry1(i, 2) = CheckAry1(j, 1) Then
’一致した場合
’結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 6, 1 To UBound(ResultAry1, 2) + 1)
’勘定科目のデータを入れていく
ResultAry1(3, UBound(ResultAry1, 2) – 1) = CheckAry1(j, 2) ‘勘定科目
Exit For
’一致しなくてCheckAry1の最後まで見終わった場合
ElseIf j = UBound(CheckAry1, 1) Then
’結果を入れるためにReslutAry1の列を１つ大きくする
ReDim Preserve ResultAry1(1 To 6, 1 To UBound(ResultAry1, 2) + 1)
’勘定科目を入力しないので、何もせずに終了
Exit For
事前準備リストを1行目から最終行目まで確認していき、明細書のデータに記載の内容と一致したとき、事前準備リストに記載されていた勘定科目をResultAry1の配列に入れるため、
ResultAry1の配列を1列大きくさせ、大きくさせた列の１つ前の列に勘定科目の情報を入れていきます。
もし事前準備リストを最終行目まで確認しても一致するデータがなかった場合、その明細書データに記載の内容は勘定科目の自動入力はできない、ということで、
勘定科目以外のデータが入れられるようにResultAry1の配列を1列大きくさせますが、データは入力せずに終了（Exit For）させています。
‘残ったデータを入力していく（内容・日付・金額）
ResultAry1(5, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 2) ‘内容
ResultAry1(6, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 1) ‘日付
勘定科目を入力できた・できなかったに関わらず、それ以外の「内容」や「日付」の情報はResultAry1の配列に入れておく必要があるので、勘定科目を入力する予定だった列と同じ列の別の行に「内容」と「日付」の情報を入れていく。
‘金額が、収入が3列目、支出が4列目のため、どちらの列にデータがあるか確認して入れていく。
If MeisaiAry1(i, 3) <> “” Then
ResultAry1(4, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 3) ‘金額
ElseIf MeisaiAry1(i, 4) <> “” Then
ResultAry1(4, UBound(ResultAry1, 2) – 1) = MeisaiAry1(i, 4) ‘金額
End If
カードの明細の場合は基本的に支出のみのため金額が入力されているのは1列だけですが、
銀行明細のデータは収入と支出があり、多くの場合（今回の例についても）は収入と支出は別の列に記載されているため、収入の列と支出の列のどちらに金額のデータが入っているかを確認しながら、金額のデータをResultAry1の配列に入れていきます。
‘勘定科目を確認して、収支と固定費/変動費がどれかを確認する。
‘勘定科目が空欄の場合はチェックしない
If ResultAry1(3, i) <> “” Then
ResultAry1の配列に入力された勘定科目を確認して、『収入か支出か』『固定費か変動費か』という点を確認していきます。
ただ、そもそも勘定科目が空欄だった場合は確認ができないため、何もしません。
For j = LBound(CheckAry2, 1) To UBound(CheckAry2, 1)
’「勘定科目」が一致するか確認
If ResultAry1(3, i) = CheckAry2(j, 2) Then
’一致した場合
’固定費/変動費のデータを入れていく
ResultAry1(2, UBound(ResultAry1, 2) – 1) = CheckAry2(j, 1)
’収支データを入れていく
If CheckAry2(j, 1) = “” Then
ResultAry1(1, UBound(ResultAry1, 2) – 1) = “収入”
Else
ResultAry1(1, UBound(ResultAry1, 2) – 1) = “支出”
End If
Exit For
End If
Next j
ResultAry1の配列に勘定科目が入力されていた場合、事前準備リストでチェックしたときと同じような流れで、【CheckAry2】の配列を1行目から最終行目まで確認していき、その勘定科目が【CheckAry2】の何行目と一致するか確認する。
一致する場所が見つかったら、同じ行の1列目にある固定費/変動費のデータをResultAry1の配列に入れていく。
また、固定費/変動費の記載がそもそもない場合、そのデータは『収入』のはずなので、ResultAry1の配列に『収入』という情報入れていく。
固定費/変動費の記載があった場合は、そのデータは『支出』のはずなので、ResultAry1の配列に『支出』という情報入れていく。
収入/支出の情報をResultAry1の配列に入力が終わったらチェックは終了。
結果の貼り付け部分
ResultAry1 = Application.WorksheetFunction.Transpose(ResultAry1)
明細のデータの全ての確認が終わったら、ResultAry1の配列に全ての結果が入力されているはずなので、その結果を貼り付けていきます。
まずは、現状では6行n列の配列になっているはずのものを行列変換してn行6列の配列に変換させています。n行6列の配列になっていれば、『入力シート』にそのまま貼り付けることが可能です。
配列の行列変換する方法はいくつかありますが、最も単純な方法はExcelのワークシート上で使用する【TRANSPOSE関数】を使用する方法です。
ワークシート上で使用する関数を呼び出す場合は
Application.WorksheetFunction.[関数名]
という形で使用できます。
ただTRANSPOSE関数では、引数の配列自体を行列変換させるわけではなく、引数の配列を行列変換させた結果を吐き出すだけですので、その結果を改めて別の配列もしくは引数と同じ配列に代入する必要があります。この点だけ要注意です。
‘貼り付け先の最終行を探し、結果を貼り付ける
With sh1
LastR1 = .Cells(.Rows.Count, 5).End(xlUp).Row
End With
配列を行列変換させれば、後はその配列を『入力シート』に貼り付けるだけです。
貼り付ける場所は、すでに入力されているデータの一番下に並べる形で貼り付けたいので、
現時点での最終行を確認するため、ExcelシートのE列の最終行（1048576行目）から Ctrl+↑をクリックしたときに移動した先のセルが何行目かを確認しています。
その次の行に貼り付けていきます。
.Range(.Cells(LastR1 + 1, 2), .Cells(LastR1 + 1 + _
(UBound(ResultAry1, 1) – LBound(ResultAry1, 1)) _
, 2 + _
(UBound(ResultAry1, 2) – LBound(ResultAry1, 2)) _
)) = ResultAry1
最後に配列を貼り付けます。
配列を貼り付ける際は、配列の大きさと同じ範囲を指定する必要があります。
配列の大きさは、
行：UBound(ResultAry1, 1) – LBound(ResultAry1, 1)
列：UBound(ResultAry1, 2) – LBound(ResultAry1, 2)
で求められるので、貼り付けたいセル（今回は B列のLastR1+1行目のセル）を左上にして、
そこから上記の行と列を移動した範囲を指定すればOKです。
まとめ
以上で説明は終了です。
なぜこのプログラムを記載しているのか
このプログラムでどのような動きをするのか
といった点を重点的に説明していきました。
このように他の人が作ったプログラムを見て、それがどんな動きをするのか、なぜその書き方をしているのか、という点が理解できるようになれば、「じゃあ、こんな書き方をすればこんな動きをするようになるんじゃないか」という考えに繋がり、プログラミングスキルの向上につながります。
今回までで、誰でも使う可能性がある家計簿を題材にして、プログラミング完全初心者向けにExcelマクロの初歩の初歩から説明していきました。
できる限り分かりやすくするためどうしても長文になってしまい、その０～その１０までの大シリーズになってしまいましたが、ここで一旦ひと段落させたいと思います。
今回で他の人が作ったプログラムを解読しながら模倣していく方法もお伝えしたので、
今後は実践編ということで、
がっつりマクロを使ったり
あえてマクロを使わなかったり
といった色々な便利アプリの実例を紹介していきながら、その中身を説明していき、
皆様のプログラミングスキルアップに貢献していけたらと思います。
コメント