報告書の見本1:延命でよい例(請求書マクロ)
古い請求書マクロを、
読み解くとこうなる。
架空の古いマクロを1本、Excelマクロ解析サービスにかけたときの報告書です。お客様にお渡しする形のまま、短くしてお見せします。
お預かりしたファイル
請求書作成.xlsm ― 標準モジュール Module1
前任者が作り、5年ほど使われてきた、という想定です。毎月末に「請求書作成」のボタンを押すと、受注の一覧から請求書を作って PDF で保存します。コメントも資料もありません。
請求書作成.xlsm / Module1
1Sub 請求書作成()2 On Error Resume Next①3 Application.ScreenUpdating = False4 Workbooks.Open "C:\Users\tanaka\Desktop\請求\単価表.xlsx"③5 ThisWorkbook.Activate6 Sheets("Sheet3").Select④7 r = 28 For i = 2 To 500⑤9 If Cells(i, 1) = "" Then Exit For10 If Cells(i, 9) <> "済" Then11 tanka = Application.VLookup(Cells(i, 3), _①12 Workbooks("単価表.xlsx").Sheets(1).Range("A:B"), 2, False)13 kin = Cells(i, 4) * tanka①14 If kin > 300000 Then kin = Int(kin * 0.97)15 Sheets("請求").Cells(r, 1) = Cells(i, 2)16 Sheets("請求").Cells(r, 2) = Cells(i, 3)17 Sheets("請求").Cells(r, 3) = kin18 Sheets("請求").Cells(r, 4) = Round(kin * 0.1)②19 Cells(i, 9) = "済"20 r = r + 121 End If22 Next23 Workbooks("単価表.xlsx").Close False24 Sheets("請求").ExportAsFixedFormat 0, _25 "C:\Users\tanaka\Desktop\請求\" & Format(Date, "yyyymm") & ".pdf"⑥26 Application.ScreenUpdating = True27End Sub
報告書
1. 業務の仕様書 ― 何を、どんな決まりで
報告書 1ページ目
動きと書き出しの決まり
| 項目 | 決まり | コードの場所 |
|---|---|---|
| 読む範囲 | 受注のシート(名前は「Sheet3」)の2行目から、A列が空になるまで。ただし500行目まで | 6・8〜9行 |
| 処理する行 | I列が「済」になっていない行だけ | 10行 |
| 単価 | C列の品番で、別のファイル「単価表.xlsx」のA列を探し、B列の単価を使う | 4・11〜12行 |
| 金額 | D列(数量)× 単価。30万円を超えたら3%引きにして、1円未満は切り捨て | 13〜14行 |
| 消費税 | 金額の10%。Round で丸める(四捨五入ではない。壊れやすい箇所②) | 18行 |
| 書き出し | 「請求」シートに、B列(顧客)・品番・金額・消費税を1行ずつ | 15〜18行 |
| 後始末 | 元の行のI列に「済」を付ける。最後に請求シートを「年月.pdf」で保存 | 19・24〜25行 |
2. 処理の流れ ― 動き始めてから終わるまで
- 単価表のファイルを開く
- 受注のシートを上から読む
- 「済」が付いていない行だけ、単価を探して金額と消費税を出す
- 請求シートに書き出し、元の行に「済」を付ける
- 単価表を閉じる
- 請求シートを PDF で保存する
読むファイル:このファイルの受注のシート(Sheet3)と、別のファイル「単価表.xlsx」。書き換えるもの:請求シート、受注のシートのI列、PDF。
3. 壊れやすい箇所 ― 今は動いていても
①重さ:高
単価表に無い品番があると、前の行の金額で請求される
単価が見つからないと、13行目の計算が失敗します。ところが2行目の「エラーを無視する」設定のため止まらず、金額には前の行の値が残ったまま請求シートに書かれ、「済」も付きます。画面上は正常に終わったように見えます。
②重さ:高
消費税の端数が、四捨五入になっていない
VBA の Round は、ちょうど .5 のときに偶数の側へ丸めます(2.5 は 2、3.5 は 4)。金額によって、消費税が四捨五入より1円少なくなる行が出ます。社内の決まりが「四捨五入」か「切り捨て」かを確かめてから直します。
③重さ:中
単価表の場所が、前任者のデスクトップに固定されている
ほかのパソコンでは単価表が開けません。エラーは無視されるので、金額が0円や前の行の値のまま、最後まで進んでしまいます。
④重さ:中
シートの名前「Sheet3」を探している
シートの名前を変えると、受注のシートを選べないまま、そのとき開いていた別のシートを読み進めます。
⑤重さ:中
500行目より下は処理されない
受注が500行を超えた月は、501行目から先が請求書に載りません。「済」も付かないので、翌月に気づくことになります。
⑥重さ:低
同じ月に2回動かすと、PDF が上書きされる
月の途中で作り直すと、先に出した PDF が黙って置き換わります。
4. このまま使うか、作り直すか
判断:作り直しは要りません。①と②を先に直せば、当面このまま使えます。
- ①は、請求の金額そのものが間違うおそれがあり、しかも気づけません。単価が見つからない行で止めて知らせる形に直します。
- ②は、社内の端数の決まりを確かめてから、丸め方を合わせます。
- ③〜⑤は、単価表の置き場所を共有のフォルダに移すときに、まとめて直すのが効率的です。
- 受注や単価をほかのシステムへ移す予定があるなら、そのときに作り直しを検討してください。この報告書の仕様書が、見積もりの材料になります。
実際の報告書では、このほかに、隠しシートやほかのファイルとのつながりの一覧、動作を確かめた架空のデータと結果も付けます。
御社の Excel でも、この形でお返しします。
お預かりの前に、必ずオンラインでお打ち合わせをし、量を確かめて料金をお伝えします。
法人のお客様専用です。料金は1ファイルあたり1〜20万円(税別)です。