ＶＢＡ練習 振替伝票シート自動作成
今回は、前回の記事投稿時点では完成している予定だった(笑)、振替伝票自動作成マクロです。
青色申告しているので、毎年毎月帳簿をつけている
タイマムシンさん、不動産賃貸業をやっているわけですが、事業収入があり、かつ青色申告をしている以上、複式簿記の帳簿を付ける必要があるわけです。
そのため、レシートとか通帳の記録とか請求書とかをとっておいて、年末が近づいてきたら次の月になったら先月分の振替伝票を作成するという仕事が発生するんです。
で、これがそこそこ手間なんですよね。１ヶ月分作るのにどうしても半日くらいはかかっちゃう訳です。僕くらいの規模でも。
具体的な作業手順は以下の通りです。
① 振替伝票作成ファイルを用意
② 事業用の銀行口座をチェック
③ 生活用の銀行口座のうち、事業に関わるものをチェック
④ 一ヶ月分の領収書・請求書等をチェック
⑤ ②～④の中で、支払いのあった日の分の振替伝票を作成（シートコピー）し、金額・仕訳・適用を入力する
この辺なんとか自動化して、少しでも手間を減らしたいなー、と常々思っていたんですが、ようやくその第一歩を踏み出したというわけですね。
というわけで、振替伝票を作る部分を一部自動化してみました。
振替伝票イメージ
※金額は実際のものではありません。
今回自動化したのは、上に上げた⑤のうちの半分「支払いのあった日付分の振替伝票を作成する（シートコピーする）」の部分です。これができると、シートを手でコピーしなくて良くなるのはもちろん、副次的効果として、日付ごとに入力シートを変えなくても、手に取った順番で入力ができるようになるんですよね。
このシートを自動作成できるようになるだけでも、おそらく全体の手間は３０％くらい減ると思います。計ってはいませんけど。そして計るつもりもありませんけど。
入力シートイメージ
※金額は実際のものではありません。
金額・仕訳・適用は今のところ手動入力しかありません。これを自動化しようとすると「スキャナーで取り込んだ画像から日付と金額を抜き出して、さらに他に書いてある日本語から仕訳と適用を判断する」って感じでしょうか。ＡＩを駆使すればできるんかな？(笑)
タイマムシンさん、もちろんＡＩなんか使えないので、それができるようになる見込みは皆無です。
コード内容
コードは以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
[code lng="vb"] Sub 振替伝票作成() Dim d As Long, n As Long, n2 As Long, No As Long 'n:入力シートの行番号，n2:振替え伝票シートの行番号, No:発行番号用 d = Cells(Rows.Count, 2).End(xlUp).Row '入力シートの最終行を取得 Dim sh As Variant '日時用変数 With ActiveSheet .Range("A6").Sort key1:=.Range("B3"), order1:=xlAscending, Header:=xlYes '入力シートのデータを日付順に並び替え End With No = 1 Worksheets("雛形").Copy after:=Worksheets("雛形") '最初の日付の振替伝票シート作成 Worksheets("入力シート").Select sh = Split(Cells(7, 2), "/") '日付欄を年,月,日に分割し、変数shに代入（この時点で、変数shは配列変数になります）。 Worksheets(3).Name = sh(1) & "." & sh(2) '配列変数shより月と日を抜き出し、シート名に代入 Worksheets(3).Range("D2").Value = sh(1) & "月" & sh(2) & "日" '配列変数shより月と日を抜き出し、振替え伝票シートのD2セルに代入 Worksheets("入力シート").Range("C7:G7").Copy Worksheets(3).Range("C7:G7") '最初の行を振替伝票シートにコピー Worksheets(3).Range("G2").Value = sh(1) & "-" & No '配列変数shより月を抜き出し、Noと組み合わせて、振替伝票シートのG2セルに発行番号を入力 No = No + 1 '発行番号繰り上げ n2 = 8 'n2に8を代入(7行目は既に入力済みなので) For n = 8 To d '(入力シートの8行目からd行目（最終行）まで繰り返し処理 Worksheets("入力シート").Select If Range("H" & n) <> 0 Then '日付順に並び替えたあと、H列で日付の変わり目を判定しています。 Worksheets("雛形").Copy after:=Worksheets("雛形") '日付の変わり目であれば、新しい振替え伝票シートを作成 Worksheets("入力シート").Select sh = Split(Cells(n, 2), "/") Worksheets(3).Name = sh(1) & "." & sh(2) Worksheets(3).Range("D2").Value = sh(1) & "月" & sh(2) & "日" Worksheets("入力シート").Range("C" & n & ":G" & n).Copy Worksheets(3).Range("C7:G7") '入力シートのn行を振替伝票シートの最初の行にコピー Worksheets(3).Range("G2").Value = sh(1) & "-" & No No = No + 1 '発行番号繰り上げ n2 = 8 'n2を8にリセット(新しい振替伝票シートを作ったので) Else Worksheets("入力シート").Range("C" & n & ":G" & n).Copy Worksheets(3).Range("C" & n2 & ":G" & n2) '入力シートのn行を振替伝票シートのn2行にコピー n2 = n2 + 1 End If Next End Sub [/code] |
動作手順としては、
①入力シートを日付順に並び替え
②最初の日付の振替伝票シートを作成（雛形シートをコピーし、シート名を月日に変える）
③入力シートのＨ列で日付の変わり目を判断
③－１ 日付が変わっていなければ、作成済みの振替伝票シートに入力シートの行を順次コピー
③－２ 日付が変わっていれば、新たな振替伝票シートを作成し、次の行をコピー
以下最終行まで繰り返し
となっております。
最終行がどの行かは、コードの３行目で判断してます。出席者・欠席者のくだりでも出てきた記述です。
見ての通り、そこそこ野暮ったいコードです。同じような記述の部分が２カ所あります（シート作成部分）。一応まとめてみようかと思ったんですけど、ほんの一部違う部分があるので、僕のスキルではまとめきれませんでした。
そしてコピーが１行ずつです。このせいでこのマクロ死ぬほど遅いです(笑)。まあ、手でシートコピーしてるよりは圧倒的に早いんですけど。
まあ動けばいっかー（←初心者あるある）
ちなみにドツボにハマっていたのは、この１行コピーの部分でした。最初RangeとCellsを組み合わせて動かそうとしてたんですけど、なぜか上手く動いてくれなかったんですよね。Rangeのみにしたら上手いこと動きました。
次の月を作成する時用に、リセット用マクロもそのうち作ってやろうと思ってます。
コピーを範囲指定にして、速度改善までできれば、次のネタになるかな？
あとは、ここで作成した振替伝票シートを、クラウド会計サイトに自動アップロードできれば、さらに手間が３０％減なんですけどねー。エクセルで完結するマクロでこんだけ苦労してたら、そっちはさらに手間がかかりそう(笑)
実際に動きを確かめてみたい方は、以下よりダウンロードしてください。ちなみに金額は変えてありますが、適用は実際のものです。あんまりツッコまないでね(笑)
編集後記
タイマムシンさん、男性としては割と高い声が出ます。代わりに低い声が出ません。なのでイケボからはほど遠いです。
そしてタイマムシンさん、カラオケ大好き人間なんです。
で、最近の男性ボーカルって、高い声の人が多いですよね。「俺の時代が来たぜぇ！」ってな感じで、ワンオクとかバックナンバーとかヒゲダンとか歌ってるわけですよ。イキって。
周りがどう思ってるかは知りません(笑)
で、この前イキりまくって、紅蓮華を歌ってみたんですよ。
お風呂で練習したた感じだと割と歌えそうだったんですが、スナックでお酒飲みながら歌ったら喉から血が出るかと思いました(笑)。なんとか歌いきったんですが、評価のほどは知らん。無理矢理歌ってるので多分低評価です。
でもなぜかＵＶＥＲｗｏｒｌｄは歌えないんですよねー。声出ないんですよ、高すぎて。LISAさんより高いとかどんなバケモンだよTAKUYA∞さん…
ほんではまた。
タイマムシンのオススメ本はこちら
