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

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

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

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

前回、【配列】を使うための準備（定義・代入・貼り付け）について説明しました。

ただ概念や基本を聞いただけでは実際にどのように使ったらいいのかのイメージが湧かないかと思いますので、
今回はいよいよ、ダウンロードしてきた明細書を基に、おおよそでリストに記入できるようにしていくマクロの中に配列を組み込んでいき、実際にどうやって配列を使っていくのかについて説明していこうと思います。

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

【本記事の目標】

配列にデータを入れていこう

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

どこで配列を使うかイメージ
実際にプログラミングしていく

どこで配列を使うかイメージ

そもそも今回のマクロのどこで配列を使ったらいいのか？

一番配列の動きをイメージしやすく、
配列の効果を実感しやすく、
今後配列を使っていく際に汎用性が高い部分は、
下記で説明している『ダウンロードした明細書から自動入力させる』の部分です。

ここでは、
　１．ダウンロードしてきた明細書を開く
　２．明細書の内容をコピーして所定のシートに貼り付ける
　３．シートに記載されていた関数を基に勘定科目を判定
　４．判定した結果を『入力』のシートに貼り付ける
という手順でマクロを組みました。

この中で配列を使える部分は　２．～４．です。
ほとんど全部ですね。

この部分を配列にしてしまうことで、
　①　マクロの動きが早くなる（配列の操作はバックグラウンドで処理されるため）
　②　勘定科目を判定する仮貼り付けシートの準備が不要
　③　判定用の複雑な関数も不要
といったメリットが出てきます。

この部分に配列を使用した場合の動きのイメージ図を描いてみると、下記のようになります。

イメージがあると、実際にプログラミングをする際も書きやすくなりますので、
まずはしっかりイメージしましょう。

実際にプログラミングしていく

それでは実際に、上記のイメージで配列を組み込んでいってみましょう。

下記が、以前の記事で記載した、カード明細を読み込んで『入力』のシートに貼り付けるプログラムのプロシージャです。
今回はこれを大きく変更していくことになります。

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

Dim FileName1 As String

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Set sh1 = ThisWorkbook.Worksheets("入力")
Set sh2 = ThisWorkbook.Worksheets("カード明細用")
Set sh3 = ThisWorkbook.Worksheets("銀行明細用")

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

'ファイルのデータを所定のシートに貼り付ける
ActiveWorkbook.Sheets(1).Range("A:C").Copy
sh2.Range("A:C").PasteSpecial
Application.CutCopyMode = False

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

'===========オートフィルター部分==============

Dim SearchYear1 As Integer
Dim SearchMonth1 As Integer
Dim EndRow1 As Integer
Dim FilterString1 As String     'フィルター条件の文字列

  
With sh1
    SearchYear1 = .Range("M4").Value
    SearchMonth1 = .Range("O4").Value
    FilterString1 = SearchYear1 & "." & SearchMonth1 & "*"
End With
  
With sh2
    'オートフィルターを実施している部分
    EndRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range(.Cells(1, 1), .Cells(EndRow1, 6)).AutoFilter Field:=1, Criteria1:=FilterString1, Operator:=xlFilterValues
    
    
    'フィルターをかけた領域をコピーし、「入力」シートに貼り付けていく
    .Range(.Cells(2, 1), .Cells(EndRow1, 1)).Copy
    sh1.Range(sh1.Cells(8, 7), sh1.Cells(8 + EndRow1 - 1, 7)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    .Range(.Cells(2, 2), .Cells(EndRow1, 2)).Copy
    sh1.Range(sh1.Cells(8, 6), sh1.Cells(8 + EndRow1 - 1, 6)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    .Range(.Cells(2, 3), .Cells(EndRow1, 3)).Copy
    sh1.Range(sh1.Cells(8, 5), sh1.Cells(8 + EndRow1 - 1, 5)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    .Range(.Cells(2, 6), .Cells(EndRow1, 6)).Copy
    sh1.Range(sh1.Cells(8, 4), sh1.Cells(8 + EndRow1 - 1, 4)).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    
    .Range(.Cells(1, 1), .Cells(EndRow1, 6)).AutoFilter 'オートフィルターの解除
    
    
Dim i As Integer
Dim j As Integer
Dim InKamoku As String
Dim CheckKamoku As String
For i = 8 To (8 + EndRow1 - 1)
    InKamoku = sh1.Cells(i, 4).Value
    If InKamoku <> "" Then
    For j = 10 To 18
        CheckKamoku = sh1.Cells(j, 10).Value
        If InKamoku = CheckKamoku Then
            If sh1.Cells(j, 9).Value <> "" Then
            sh1.Cells(i, 3).Value = sh1.Cells(j, 9).Value
            sh1.Cells(i, 2).Value = "支出"
            ElseIf sh1.Cells(j, 9).Value = "" Then
                sh1.Cells(i, 2).Value = "収入"
            End If
        End If
    Next j
    End If
Next i
    
End With

End Sub

まずは必要な配列の宣言と定義

用意する配列

今回のマクロで使用する配列は３つだけです。

１つは、ダウンロードした明細書の情報を入れるための配列です。
もう１つは、『入力』シートに結果を貼り付けるための配列です。
これらは上記のイメージ図でも記載しているので、比較的イメージしやすいと思います。

最後の１つは、『事前準備リスト』のシートに記載している、内容と勘定科目を判定するためのリストを入れる配列です。

配列を使うことで、データの処理がバックグラウンドで処理できるようになり、マクロの動きが格段に速くなります。
しかしそれは使うデータが全て配列になっている場合です。
処理に使うデータが一部でも配列の外（ワークシート上）にあると、バックグラウンドだけで完結した処理にならず、マクロの動きが早くならなくなってしまいます。
そのため全てバックグラウンドで処理させるには、処理するデータだけでなく、照合するためのデータに関しても配列もしくは変数に入れておく必要があるのです。

配列の宣言と定義

今回作る２つの配列ですが、１つ目の「明細書の情報を入れるための配列」についてはデータ量がファイルを開くまで分からないので、動的配列を使う必要があります。
もう１つの「判定リストを入れる配列」ですが、こちらについては固定ですので静的配列を使っても良いのですが、内容と勘定項目のリストを自由に追加できるようにするため、動的配列にしておいた方が良いです。

静的配列にした場合、配列のサイズを変更するためにはマクロから修正する必要があります。

しかし動的配列にしておいて、そのサイズをワークシートの情報を基に変更するように設定しておけば、ワークシートを変更するだけで配列のサイズを自由に変更することが出来ます。

勝手に変更されたくない、もしくは不用意に変更されないようにしておきたい、という場合は静的配列にしておいた方が良いですが、
ある程度可変性を持たせておきたい場合は、動的配列にしておいた方が良いです。

どちらも動的配列、かつ基本的には文字列のデータですが、数値のデータも含みますので、Variant型として定義します。

それぞれの配列名を下記として宣言しておきます。
　１．明細書の情報を入れるための配列
　　　　MeisaiAry1
　２．結果を入れるための配列
　　　　ResultAry1
　３．判定リストを入れるための配列
　　　　CheckAry1

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

チェックリストのデータを配列に入れる

まずはチェックリストのデータを配列に入れていきます。
チェックリストは『事前準備リスト』のシートに下記のように入っています。

このデータを配列に入れるためには、この領域のサイズを確認する必要があります。

サイズを確認する、と言っても今見えているぐらいの範囲であれば
　9行、2列
というのがすぐに分かります。
ただもしデータ数が多くなってきたとき、データの端を探す必要があります。
データの端を探すとき、ワークシート上では
　「Ctrl」＋「↑」
　「Ctrl」＋「↓」
　「Ctrl」＋「→」
　「Ctrl」＋「←」
というショートカットキーを使って探すと思います。
（※もしご存じの無い方は、非常に便利なショートカットキーですので、
　　ぜひ覚えておいてください。）

このショートカットキーと同じ動きをマクロ上でさせる場合は、『End』を使います。
これは基本的に、RangeもしくはCellsを使って１つのセルを選択した後に使用し、
どちら側に動かすかを引数として指定します。
動かす方向を引数で指定する場合、それぞれ下記内容を入力します。
　「Ctrl」＋「↑」：xlUP
　「Ctrl」＋「↓」：xlDown
　「Ctrl」＋「→」：xlToRight
　「Ctrl」＋「←」：xlToLeft

今回の場合、１行目はリストの題目のためデータ処理には不要ですので、A2セル（２行目、１列目のセル）から
　下に行き（xlDown）
　右に行き（xlToRight）
したところまでのデータを配列に入れていきます。

この時、
　一番最後の行の番号を入れるための変数を　LastR1
　一番最後の列の番号を入れるための変数を　LastC1
としておくと、上記のEndを使って下記のように入力することでそれぞれ取得することが出来ます。

　LastR1 = sh4.Cells(2, 1).End(xlDown).Row
　LastC1 = sh4.Cells(2, 1).End(xlToRight).Column
　（※sh4は『事前準備リスト』のシートをセットしている変数です）

上記のコマンドで最終行・最終列の番号を取得できましたので、配列に入れたいデータの範囲は
　Cells(2,1)　～　Cells(LastR1,LastC1)
の範囲になります。

後はこの範囲のデータをCheckAry1の配列に入れるだけですが、
　配列が動的配列である
　配列がVariant型で定義されている
という条件の場合、Excelシートのデータを配列に入れるのは非常に簡単で、
　配列＝Rangeで指定したワークシートの範囲
だけでデータを配列に入れることが出来ます。

実際に今回のデータを配列に入れる場合、下記の書き方になります。

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

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

今回はA2セルから下端、右端のデータを探しましたが、
別の方法として、
　ワークシートの一番最終行から上に行く
　ワークシートの一番最終列から左に行く
という方法でもデータの端を探すことが出来ます。

この場合、ワークシートの一番最終行・最終列を知っておかないといけないです。
ですが最終行・最終列は覚えておかなくても
　最終行：worksheet.Rows.Count
　最終列：worksheet.Columns.Count
というコマンドを使えば、最終行・最終列を使うことが出来ます。

実際にこのコマンドを使って上記のマクロを記載すると、下記のようになります。
どちらも同じ結果になりますが、シートの状態（空欄がある、他の部分にデータがある等）によっては結果が異なる場合があるので、両方の方法を知っておいた方が良いと思います。

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

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

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

明細書のデータを配列に入れる

ダウンロードした明細書のデータも配列に入れていきます。
とはいえ、やり方はチェックリストのデータを入れたときと基本的には同じです。

ダウンロードした明細書のデータを配列に入れる場合は、まずファイルを開く必要があるので、
もともと記載していた内容をそのまま使います。
　FileName1 = Application.GetOpenFilename
　Workbooks.Open Filename:=FileName1

あとはチェックリストのデータを入れたときと同じです。
明細書のデータは下記のようになっており、チェックリストのデータの時と同様、1行目のデータは配列に入れる必要が無いので、A2セルから右下の範囲を配列に入れていきます。

この時、チェックリストのデータを入れる際に使った　LastR1、LastC1　の変数は、
再利用して上書きして使っても大丈夫です。

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

これで　MeisaiAry1　CheckAry1　それぞれに配列データが入りました。
後はここに入っているデータを処理していき、必要なデータを　ReslutAry1　に入れた後、
『入力』のシートに貼り付ければ完成です。

以降については次回に記載していきます。

次の記事へ