モノまねVBAで家計簿を作る〜その２ ユーザフォームでデータ入力する

  EXCEL VBAで作る「オレの家計簿」（別名、家計簿じゃが）は

• シートの１行を「１件のお買い物（支出）」や「１件の収入」に対応づける。

  豪華なメニュー画面はなく、家計簿シートの先頭２行に「データ入力」、「ピボットシート更新」、「毎月支払」という３つのボタンを配置しただけの物凄くシンプルなものだ。   その作り方は、以下の通りだ。

1. まずは、VBAのコントロールを利用し、ユーザフォームを作る。
2. そして、そのフォームからデータ入力をするところをVBAで書く。それだけだ。

  ユーザフォームを作るには、EXCELメニューの「開発」タブから、「Visual Basic」タブをクリックし、「挿入」タブから「ユーザーフォーム」を選択する。すると、Userform1という名前でユーザーフォームが追加されるので、ここにテキストボックスだの、ボタンだのを追加していくわけだ。
初めてやる場合、ここら辺は非常にわかりにく大変な作業になる。ネットを駆使して、頑張って作ろう！

• UserForm1のタイトルバー（Caption）は「家計簿への入力」としておこう。
• 「内容」というフィールドはテキストボックスで作る。変数名はTEXTBOX1とする。
• 「金額」というフィールドはテキストボックスで作る。変数名はTEXTBOX2とする。
• 「年月日」というフィールドはテキストボックスで作る。変数名はTEXTBOX3とする。日付の増減のためにスピンボタンを追加しよう。
• 「種別」というフィールドはラジオボタンで、支出と収入を選択できるようにしよう。
• 「クレジットカード支払」というチェックボックスを作ろう。変数名はCheckBox1とする。
• 「登録」というコマンドボタンを作り、変数名はCommandButton1とする。「登録」ボタンがクリックされたら、UserForm1で指定された「年月日」、「内容」、「費目」、「費目コード」、「金額」、「クレジット支払（フラグ）」がEXCELの「家計簿」というシートの最終行の次行のそれぞれのフィールドとして追加されるようなコードを書いておく。

UserForm1

  これがメインとなるコードだ。最終行の求め方はEXCELのバージョンによる影響があり、その度に見直している。今はこれがいいようだ。ネットの師匠に聞いたのだ。つまり、マネしてみだ。というか、コピペだ。

Dim 最終行 As Long
Sub CommandButton1_Click()
    Sheets("家計簿").Select
'    最終行 = Range("A3").End(xlDown).Row + 1 '
     With ActiveSheet.UsedRange
       最終行 = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row + 1
     End With
    If ComboBox1 = "" Then
      MsgBox "費目名が入っちょらんどー。"
      Exit Sub
    End If
    If TextBox2 = "" Then
      MsgBox "金額が入っちょらんどー。"
      Exit Sub
    End If
    Sheets("家計簿").Range("B" & 最終行).Value = TextBox1.Value
    Sheets("家計簿").Range("A" & 最終行).Value = TextBox3.Value
    Sheets("家計簿").Range("A" & 最終行).NumberFormatLocal = "yyyy/mm/dd ddd"
    If OptionButton1 Then '支出の場合'
      Sheets("家計簿").Range("F" & 最終行).Value = TextBox2.Value
      Sheets("家計簿").Range("E" & 最終行).Value = ""
    Else                  '収入の場合'
      Sheets("家計簿").Range("E" & 最終行).Value = TextBox2.Value
      Sheets("家計簿").Range("F" & 最終行).Value = ""
    End If
    If CheckBox1.Value Then
      Sheets("家計簿").Range("G" & 最終行).Value = 1
    Else
      Sheets("家計簿").Range("G" & 最終行).Value = 0
    End If
    Sheets("家計簿").Range("D" & 最終行).Value = ComboBox1.Value
    If OptionButton1 Then '支出の場合'
      Select Case ComboBox1
      Case "食費"
        Sheets("家計簿").Range("C" & 最終行).Value = 1201
      Case "住居"
        Sheets("家計簿").Range("C" & 最終行).Value = 1202
      Case "光熱・水道"
        Sheets("家計簿").Range("C" & 最終行).Value = 1203
      Case "被服"
        Sheets("家計簿").Range("C" & 最終行).Value = 1204
      Case "保健・医療"
        Sheets("家計簿").Range("C" & 最終行).Value = 1205
      Case "教育"
        Sheets("家計簿").Range("C" & 最終行).Value = 1206
      Case "教養・娯楽"
        Sheets("家計簿").Range("C" & 最終行).Value = 1207
      Case "交際"
        Sheets("家計簿").Range("C" & 最終行).Value = 1208
      Case "交通・通信"
        Sheets("家計簿").Range("C" & 最終行).Value = 1209
      Case "貯蓄"
        Sheets("家計簿").Range("C" & 最終行).Value = 1210
      Case "保険"
        Sheets("家計簿").Range("C" & 最終行).Value = 1211
      Case "税金"
        Sheets("家計簿").Range("C" & 最終行).Value = 1212
      Case "その他"
        Sheets("家計簿").Range("C" & 最終行).Value = 1213
      Case "定期代"
        Sheets("家計簿").Range("C" & 最終行).Value = 1214
      Case "薬代"
        Sheets("家計簿").Range("C" & 最終行).Value = 1215
      End Select
    Else '支出の場合'
      Select Case ComboBox1
      Case "給与"
        Sheets("家計簿").Range("C" & 最終行).Value = 1101
      Case "賞与"
        Sheets("家計簿").Range("C" & 最終行).Value = 1102
      Case "年金"
        Sheets("家計簿").Range("C" & 最終行).Value = 1103
      Case "雑収入"
        Sheets("家計簿").Range("C" & 最終行).Value = 1104
      Case "その他"
        Sheets("家計簿").Range("C" & 最終行).Value = 1105
      Case "前月繰越"
        Sheets("家計簿").Range("C" & 最終行).Value = 1106
      Case "パート代"
        Sheets("家計簿").Range("C" & 最終行).Value = 1107
      Case "アルバイト代"
        Sheets("家計簿").Range("C" & 最終行).Value = 1108
      End Select
    End If
' ピボットを自動更新'
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
' 月日で並べ替え'
    Call sort_by_date
End Sub

家計簿というシートに支出や収入を１件ずつ、追加していくのだが、追加される度に日付で昇順になるようにコードを書いておく。

Sub sort_by_date()
    Dim 最終行 As Long
    Sheets("家計簿").Select
 '   最終行 = Range("A3").End(xlDown).Row
    With ActiveSheet.UsedRange
      最終行 = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    End With
    Range("A2:G" & 最終行).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End Sub

支出と収入のラジオボタンがクリックされたら、それぞれに応じた費目がコンボボックスのリストに出るようにしておく。こんな感じだ。

Sub OptionButton1_Click()
  OptionButton2.Value = False
  OptionButton1.Value = True
  ComboBox1.List = Array("食費", "住居", "光熱・水道", "被服", "保健・医療", "教育", "教養・娯楽", "交際", "交通・通信", "貯蓄", "保険", "税金", "その他", "定期代", "薬代")
End Sub
Sub OptionButton2_Click()
  OptionButton1.Value = False
  OptionButton2.Value = True
  ComboBox1.List = Array("給与", "賞与", "年金", "雑収入", "その他", "前月繰越", "パート代", "アルバイト代")
End Sub

これは年月日の増減を行うスピンボタンのコードだ。

Sub SpinButton1_SpinDown()
  TextBox3.Value = CDate(TextBox3.Value) + 1
End Sub
Sub SpinButton1_SpinUp()
  TextBox3.Value = CDate(TextBox3.Value) - 1
End Sub

UserForm1が呼び出されたら、今日の日付と費目のリストをセットしておく。

Sub UserForm_Initialize()
  TextBox3.Value = FormatDateTime(Date, vbShortDate)
  If OptionButton1 Then '支出の場合'
    ComboBox1.List = Array("食費", "住居", "光熱・水道", "被服", "保健・医療", "教育", "教養・娯楽", "交際", "交通・通信", "貯蓄", "保険", "税金", "その他", "定期代", "薬代")
  Else '収入の場合
    ComboBox1.List = Array("給与", "賞与", "雑収入", "その他", "前月繰越", "パート代", "アルバイト代")
  End If
End Sub

これがおれの家計簿だ。

oreno-kakeibo-menu

  EXCELシートの「家計簿」でユーザフォームUserForm1を呼出すには「データ入力」というボタンを作り、以下のコードを標準モジュールに記述し、myform1をマクロ登録で記述すれば良い。これで「オレの家計簿」の完成た。

Sub myform1()
    Dim 最終行 As Long
    UserForm1.Show (vbModeless)
    Sheets("家計簿").Select
End Sub

  毎月支払というボタンには、毎月支払というシートにあらかじめ作成した項目を一括追加するVBAコードをマクロ登録する。

Private Sub 毎月支払()
    Dim 最終行 As Long
    Sheets("毎月支払").Select
   '最終行 = Range("B1").End(xlDown).Row
    With ActiveSheet.UsedRange
      最終行 = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    End With
    Rows("2:" & 最終行).Select
    Selection.Copy
    Sheets("家計簿").Select
    '最終行 = Range("A3").End(xlDown).Row + 1
    With ActiveSheet.UsedRange
      最終行 = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row + 1
    End With
    ret = MsgBox("まこち、追加すっとか？(ctrl+zでん、元に戻らんどー)", vbYesNo)
    Select Case ret
    Case vbYes
      '処理を継続します
    Case vbNo
      '処理を終了します
      Exit Sub
    End Select
    Rows(最終行 & ":" & 最終行).Select
'    Selection.Insert Shift:=xlDown
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
' ピボットを自動更新
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
' 月日で並べ替え
    Call sort_by_date
End Sub

  毎月のデータを手作りで２つのピボットテーブルにしてみた。これで、収支管理はバッチリだ。そして、ピボットテーブルを最新化するコードは以下のとおり。これも使いやすくするため、ボタンでマクロ登録しておく。

Sub Update_Pivot_area()
    Sheets("家計簿").Select
    '最終行 = Range("A3").End(xlDown).Row
    With ActiveSheet.UsedRange
      最終行 = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    End With
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"
    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
    Worksheets("ピボット2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").RefreshTable
End Sub

るんるんモノマネEXCEL VBA 本〜仕事で覚えたEXCEL＆VBA小技 BOOK〜 キモの関数、そしてIEを自在に操るVBA。初めはモノマネが一番いい！に戻る

モノまねVBAで家計簿〜まずは、キホン設計！

  イチからすべて、考えるのは難しい。そこで、他人のコードをみてマネして習得する、「モノまねVBA」がいい。そのモノまねVBAを駆使してアプリを作る最初のステップは、基本設計をどうするかだ。即ち、どんな使い勝手のソフトにするかだ。基本設計のポイントとしてば、以下の３つとする。

• EXCELシートの１行で１件の支出または収入を表す。 
• その１行の項目は７つー日付、内容、費目コード 、費目、収入金額、支出金額、クレジット有無フラグだ。これらの項目を入力フォームで指定し、登録ボタンでシートへ追加する。費目はプルメニューにして、費目コード は自動で入るようにする。
• この1行の項目の要素、費目コードと費目も、金融広報中央委員会がかつて無償で提供していた幻の「みんなの家計簿」から引用させて頂く。１から考えるのは大変なので。

 

oreno-kakeibo

  あとは、ひたすらコーディングだ。
 
  ここでチョット一息のコーナーだ。

small eyes

  或る土曜日、街の名を冠したバルに出掛けた。小規模でかつ参加店が少ないため、人気の店には多くの客が長蛇の列。なんとかキャパのある店に滑り込め、少し腹ごしらえできた。殆どの店で客の数が店のキャパを超えている。これではタマラン。そこで、近くのコンビニやスーパー、パン屋で缶チュウハイ、紅茶、ボローニアサンドイッチを買い込み、川の土手で「勝手にバル」。川からのそよ風が心地良かった。こりゃ、いつでもバルはできるんだね。