モノまね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

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

zenfone5+OCNモバイルONEで格安スマホ

2019girl

  日本では、都会は別として、地方では繁華街でさえも電話ボックスを探してもなかなか見つけることができなくなってきた。もう、駅ぐらいしか、見当たらない。やむをえず、スマホとして、ASUSのzenhoneを購入。SIMカードはOCNモバイルONE 音声通話を選んでみた。
　zenhone、OCNのSIMカードもインターネットで購入。電話ができるようになるまでの手続きは、すべて、インターネット。自分でやらないといけないので、大変だったけど、わからないときは、日中なら、コールセンターが電話対応してくれたので、大丈夫だった。
　最近、読んだ宮部みゆきの「模倣犯」。「ごく普通の人のごく普通の言葉、態度、生き方の在りように、いずまいを正さずにはいられないような気持ちになることもある。」
（摸倣犯上巻 p240）豆腐屋の義男さん。こういう人物が登場すると物語がグーッと深くなるというか、ピリッとしまる。さすがです。凄い。うますぎる。ところで、犯人の描写ではロバート・マキャモンみたいな「ホラー」を感じた。

windows8.2pro+Hyper-Vでdebian8xfceを動かしたときに仮想スイッチにはまるもナントカ抜け出たぜぃ

　会社ではやむなくwindowsを使わされているが、linuxでやれば効率よく、やれそうなことがある。そこで、お仕着せのwindows8.2proにHyper-Vをインストールし、VMとしてdebian8を入れてみた。ネット接続のために「仮想スイッチ」を作る訳だが、「内部」にするのか、「外部」にするか、プライベートネットワークにするのかときかれたので、会社のプロキシーを通すため、内部にするしかないだろうとやってみた。ところが、これだけでは接続はできなかった。いろいろとぐぐっているうちに、とあるページでプライベートIPアドレスの設定が必要だったことがわかり、そのとおりにしてみると、見事にネット接続できてしまった。仮想スイッチのIPv4のプロパティの画面で、固定IPアドレスを指定してやる。プライベートIPアドレスならば、どれでもOKだが、このときは192.168.137.1を指定した。そして、DNSサーバもその環境でのDNSサーバのIPアドレスを指定する。そして、VM上のDebian Linuxのネットワーク設定でも、先ほどの192.168.137.1を指定すれば、いいのだ。ちやん、ちゃん。これで、つながるよ。ブリジッジの設定とかしなくていいんだよ。
　さくらももこが亡くなった。いろいろな偲ぶ記事を読み、「神のちから」（小学館）が品切れ、「ちびしかくちゃん」（集英社）では自ら作り出したキャラクターをパロッていたという。凄すぎる。

EXCEL VBAよりrubyは早くて実にシンプルかつ高速

　仕事ではやむを得ず、EXCEL VBAを使うことが多い。EXCELシートにあるデータを簡単に使えるからだ。でも、とても遅い。そこで、rubyで書き換えてみた。
　その手法は、以下のとおり。
１）EXCELシートにあるデータは、Win32OLEのADOで、SELECTで1行ずつ、取ってくる。
２）VBAにはない、「ハッシュ」（連想配列）を、ユニークなキーごとの集計などに活用し、高速化する。
３）集計結果などはEXCELシートに書かず、CSVとして、出力する。これもまた、高速化のためである。
　今回、一番、手間がかかったのが、小数点第一位での四捨五入の書き方である。
　rubyは、型宣言をしないため、どのように記述すれば、実現できるのか、試行錯誤の連続だった。答えは、「割り算のメソッドとして、.divではなく.quoを使う」ということだ。divは結果が整数となる（つまり、例えば1.5が2にならず、1になってしまうのだ）が、quo は浮動小数になるのだ（だから、1.5はちゃんと2になるのだ）。わからにくいぜよ。
　ちなみに、くだんのEXCEL VBAのプログラムは40秒もかかっていたが、書き換えたrubyでは1秒もかからず、一瞬で終わる。１秒としても40倍の速さだ。凄いね。
　さて、皆さん、EXCEL、いやMS　Officeは捨てて、rubyの街に出掛けよう。
　先日、星野　道夫さんの写真展を見た。アラスカの自然とそこで生きる人々。星野さんによれば、２つの自然があるという。身近のそれと遠く、訪れることないそれ。まったく、そのとおりだ。記念として、霜で白くフチどられたカラフルな葉っぱが並んだポストカードを購入。

IE自動運転でIEの開発者向けF12キー活用でWebページをカンタンに解析する

maygirl

  見えているものに気がつかず、遠回りをして、苦労することがある。IEの開発者向けF12キーのことだ。そして、最近やっと気が付いたのだが、そこにある「検索窓」。役割がないアイテムはない。     「物語に銃が出てきたら、撃たれなけらばならない。」
  そ、そうなんですよ。
  その「検索窓」に「INPUT」って打ち込むと、そのWebページにあるINPUT＜タグ＞をすべて、手繰ることができる。検索ボタンについている「名前」もわかる。なんでもわかるンだよね。
　原田マハの「風のマジム」には南大東島のさとうきびで作るラム酒がでてくる。ラムには２つの種類がある。さとうきびのしぼり汁に水を加えてそのまま発酵させる、アグリコール ・ラムと、さとうきびの絞り汁から糖を採取後のしぼり汁を発酵させる、インダルトリアル・ラムだ。説明を聞いただけで、アグリコール・ラムを試してみたいと思った。
後日、アグリコール・ラムを求めて色々な酒屋を巡ったのだが、ついぞ、その姿を見ることができないでいる。

gentooのカーネル設定make menuconfigで改めて気がついた文字列サーチの/

見えている筈なのに気がつかなかった。gentooのカーネル設定(make menuconfig)で文字列サーチができる。メニューにでてるのに。

</> for Searchとね。
そこで/と叩いてみたら。









そしてwacomと打ち、どこにあるのじゃろときいてみたら。

なるほどです。

　村上　春樹の「騎士団長殺し」を読んだ。とても心に響いたフレーズがあったので自分のために残しておく。 下巻２３ページにある。「時間が奪っていくものもあれば、時間が与えてくれるものもある。時間を味方につけることが大事な仕事になる。」
　そうだ。そのとおり。まるで好きな人の前で話したいことがふっと消えてしまい、無口になるように。