当サイトは整備中です。一部リンクが切れている可能性もありますのでご了承ください。

エクセルで作った収支計算表の入力をVBAで簡単にしてみた

プログラミング

どうも、未経験からSESの道を歩んでいるおこめです。

今回は収支計算表をVBAを駆使して作ってみたので、備忘録として作り方を書き残しておこうと思います。

ちょっとでも楽に家計簿をつけたいな~と思っていた方や、収支計算をしないと死んでしまう方はぜひとも参考にしてみて下さい。

※今回紹介する収支計算表はプログラムに触り始めて1か月くらいのやつが作っております

エクセルで作った収支計算表の入力をVBAで簡単にしてみた

  1. 全期間の合計収入と合計支出を出力。SUM関数で出力。
  2. 曜日ごとの収入と支出を記入。収入は黒文字支出は赤文字で表記
  3.  月ごとにジャンル毎の合計使用金額を算出。SUMIFS関数にて出力。
  4.  月ごとの合計値を算出。SUM関数で出力。

まずは、今回作った収支計算表の機能は上記の通り。

f:id:kometika:20190601211724j:plain

①で全期間合計収入と支出の計算。

②は日付ごとの収入と支出

③は備考に書いたジャンルごとの合計支出金額

④は月ごとの支出-収入

ってな感じです。

この収支計算表ですが、もしVBAを使わずに手入力するとなると、けっこう面倒くさいことになるんですよ……。

手入力時の欠点①:毎回日付を書かないといけない

f:id:kometika:20190601212643j:plain

まずは日付の記入。

パッとどこで~円のものを買ったと書きたいのに、日付を入力しないといけません。

手入力時の欠点②:わざわざ線をひかないといけない

f:id:kometika:20190601212827j:plain

入力し終わったらわざわざ下線も引かなきゃいけません(別に無くてもいいんですが見にくいので)

それくらい我慢しろよって思うかもしれませんが、30秒も1年になると10950秒。

約3時間のロスとなります。そう考えるとけっこうでかいですよね。

この作業だけでもなんとか減らしたい!

そこで、VBAの登場です。

というわけで今回簡単にする作業は以下の通り

f:id:kometika:20200412224620j:plain

さあ、それでは始めて参りましょう!

VBA実装方法

f:id:kometika:20190601213745j:plain
それではVBA実装方法です。

まずは開発環境が整っていない方のために、「開発」タブを表示する所から解説していきますね。

エクセルを開いたら「ファイル」タブをクリックします。

f:id:kometika:20190601213908j:plain

ファイルタブが開かれたら、一番下にある「オプション」をクリック。

f:id:kometika:20190601213957j:plain

リボンのユーザー設定」をクリックして、メインタブの所にある「開発」にチェックを入れます。

これでVBAを利用する準備は整いました。

f:id:kometika:20190601214128j:plain

エクセルの方に戻ると「開発」タブが表示されているので、そこをクリックして「挿入」を選択します。

f:id:kometika:20190601214250j:plain

赤枠で囲まれている部品(ボタン)を選択して、シートの好きな所にボタンを作ります。

f:id:kometika:20190601214358j:plain
f:id:kometika:20190601214612j:plain

すると、このような画面になると思いますので保存先を「作業中のブック」にして「新規作成」をクリックします。

f:id:kometika:20190601215112j:plain

新規作成をクリックすると、このようにコードが入力できる画面に遷移します。

それでは1つ目のコードです。

コード①

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Year(Now) & “/” & Month(Now) & “/” & Day(Now) & “(” & WeekdayName(Weekday(Date), True) & “)”

If Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0) = “a” Then

Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).ClearContents

End If

UserForm1.Show

上のコードをClickとEnd Subと書かれている間の部分に書き込みます。

f:id:kometika:20190601215425j:plain

書き込んだら、「挿入」と書かれた部分を選択し、「ユーザーフォーム」をクリックします。

f:id:kometika:20190601215543j:plain

すると、ユーザーフォームが作られますので、画像のように「ラベル」「テキストボックス」「ボタン」を置いていって下さい。

f:id:kometika:20190601215701j:plain

ボタンが置けたら各項目の文字などを変えていきます。

文字の大きさや場所等はプロパティで変える事ができますが、ここでは詳しい説明を省きますね。

なんか適当にやってください(ニコッ)

調整が終わったら入力と書かれたボタンをダブルクリックします。

f:id:kometika:20190601215908j:plain

再びコードを入力する部分が現れますので

コード②

Dim x As Integer

Private Sub CommandButton1_Click()

If TextBox2.Text = “” And TextBox3.Text = “” Then

MsgBox (“収入か支出は必ず入力してください”)
Exit Sub
End If

If TextBox2.Text = “” Then

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = “-“
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = TextBox3.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Font.ColorIndex = 3
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

ElseIf TextBox3.Text = “” Then

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = TextBox2.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = “-“
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

Else

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = TextBox2.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = TextBox3.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Font.ColorIndex = 3
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

End If

TextBox1.Text = “”
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”

x = x + 1

End Sub

上記のようにコードを記述します。

注意点ですが、Dim x As Integerの部分はPrivate Sub CommandButton1_Click()の上に書くようにしてください(全体で利用する変数なので)。

f:id:kometika:20190601220119j:plain

入力が終わったら、UserForm1と書かれた部分をダブルクリックして、再びフォームが現れたら今度は「終了」と書かれたボタンをダブルクリックします。

f:id:kometika:20190601220248j:plain

またまたコードを書ける場所が出てきますので

コード③

Private Sub CommandButton2_Click()

If x = 0 And Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0) = “” Then

Cells(Rows.Count, 1).End(xlUp).Value= “”
Cells(Rows.Count, 2).End(xlUp).Offset(0, -1).Value= “a”

ElseIf x = 0 Then

Cells(Rows.Count, 1).End(xlUp).Value= “”

ElseIf x > 1 Then

Cells(Rows.Count, 2).End(xlUp).Offset(0, -1).Value= “a”

End If

Cells(Rows.Count, 1).End(xlUp).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).Borders(xlBottom).Weight = xlMedium

Unload UserForm1

End Sub

このように記述していきます。

f:id:kometika:20190601220448j:plain

これで実装は終了です。

エクセルに戻って最初に作成したボタンを押してみましょう。

f:id:kometika:20190601220910j:plain

ボタンを押すとこのように今日の日付が自動で記入されて、先ほど作成したユーザーフォームが現れます。

f:id:kometika:20190601221736j:plain

必要事項を入力してから「入力」ボタンを押すと

f:id:kometika:20190601221812j:plain

このように記入されます。

f:id:kometika:20190601221836j:plain

記入が終わってから「終了」ボタンを押すと

f:id:kometika:20190601221840j:plain

ユーザーフォームが閉じられ、入力した下の部分に下線が引かれます。

やりたかった事の自動化成功です。

各コードの説明

ここからは、軽くですが記載したコードの説明をしておきます。

説明というか書いておかないとぼくが忘れそうなので記録しておきます(笑)

 コード①の説明

①Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Year(Now) & “/” & Month(Now) & “/” & Day(Now) & “(” & WeekdayName(Weekday(Date), True) & “)”

②If Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0) = “a” Then

Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).ClearContents

End If

③UserForm1.Show

まずは、一番初めに記載したこちらのコード。

このコードでは具体的に3つの操作をしています。

①は一列目の一番最後に書かれたセルの下に現在の日付を入力するというコードです。

ここでの肝は一番最後に書かれたセルの一つ下のセルを指定する方法

なんですが……

それはこちらの記事に詳しくまとめられておりましたのでよかったら参考にしてみてください(笑)

②は一列目の最後に書かれたセルの一つ上のセルにaが記入されていたらそれを消すというコードです。

ここは悩んだんですよね……。

というのも、①のコードは最後に書かれたセルの一つ下のセルに文字を記入するというものなんですけど……。

f:id:kometika:20190602120119j:plain

もしこんな風に空白があるとですね

f:id:kometika:20190602120152j:plain

最後のセルの一つ下に記入されるので、思った所に日付が記入されないんです……。

この自称を防ぐために、線を引いてプログラムを終了すると同時にaという文字を記入しております。

③はユーザーフォームを開くコードになります。

ここは特に説明はいりませんかね。

コード②の説明

Dim x As Integer

Private Sub CommandButton1_Click()

①If TextBox2.Text = “” And TextBox3.Text = “” Then

MsgBox (“収入か支出は必ず入力してください”)
Exit Sub
End If

②If TextBox2.Text = “” Then

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = “-“
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = TextBox3.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Font.ColorIndex = 3
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

ElseIf TextBox3.Text = “” Then

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = TextBox2.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = “-“
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

Else

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = TextBox1.Text
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = TextBox2.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = TextBox3.Text
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Font.ColorIndex = 3
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = TextBox4.Text

End If

③TextBox1.Text = “”
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”

x = x + 1

End Sub

2つめに書いたコードは長いですが、ざっくりわけると3つの操作に分けられます。

①は収入か支出のどちらかが記入されなかったらメッセージボックスを表示するコード。

②は収入と支出が空白だった場合、またはすべてが記入されていた場合の記入方法です。

前述した通り仕様上空白があると困るので、空白には「ー」を入れる事で対応するようにしました。

③はテキストボックスを空にするコードです。

赤く示したxという変数の意味は最後のコード③の説明で書きますね。

コード③の説明

Private Sub CommandButton2_Click()

①If x = 0 And Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0) = “” Then

Cells(Rows.Count, 1).End(xlUp).Value = “”
Cells(Rows.Count, 2).End(xlUp).Offset(0, -1).Value = “a”

ElseIf x = 0 Then

Cells(Rows.Count, 1).End(xlUp).Value = “”

ElseIf x > 1 Then

Cells(Rows.Count, 2).End(xlUp).Offset(0, -1).Value = “a”

End If

②Cells(Rows.Count, 1).End(xlUp).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).Borders(xlBottom).Weight = xlMedium
Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).Borders(xlBottom).Weight = xlMedium

③Unload UserForm1

End Sub

最後3つめに書いたコードの説明です。

まず①なんですが、2つめのコードの時に使用した変数x

この値にどの数字が入っているか場合分けをして処理を行っています。

もしxの数字が0だった時。

xは登録ボタンが押されるたびに数字が増えていきますので、つまり登録ボタンが1回も押されなかった場合ですね。

この場合は最初に記入した日付が残っていたら困るので、それを消します。

そして上の数字にaが記入されていた場合それも消しているので復活させるという処理をやっています。

逆にxに2以上の数字が入っていた場合。

その場合は最後に書き込んだ摘要の隣のセルにaと記入する処理を行っています。

②はそれぞれのセルに下線を引く処理。

③はユーザーフォームを閉じて、プログラムを終了する処理となっています。

ざっくりとですが、以上が各種コードの説明でした(^^)/

今後も改善する予定です…。

というわけで以上、収支計算表の入力をVBAで簡単にしてみた…でした。

自分的にはかなり楽になったと思っているんですが、やっぱりまだまだ欠点が見られるんですよね~

なので今後も勉強がてら随時改善していって、どんどん楽にしていけたらいいなと思います。

何か参考になった情報が1つでもあれば嬉しい限りです(^^)/

それでは、今回はこの辺りで!

お相手は、最近パソコンの見過ぎで肩がイカれていくのを感じている男。

稲刈おこめでした~

コメント

タイトルとURLをコピーしました