初心者のためのExcel エクセルマクロVBA入門-実践：プログラムを整理するリファクタリング（発注書マクロ）
デバッグのデバッグ
前回で、大体完成と言いましたがまだ落とし穴がありました。それがデバッグで追加したプログラムの「デバッグ」です。修正した内容にも「落とし穴」があるかもしれないのです。実際にこのプログラムで何度かテストをするとわかりますが、最終行を確保するところで、元々の発注リストに商品がない場合は、表のヘッダが消えてしまうのです。
こんな風ですね。当然、デバッグで落とし穴を埋めたこの部分・・・
shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents
ここに落とし穴がありました。リストにデータがある場合に、クリア処理をする。という方がよさそうです。
そのように変えてみると・・・
If shtOrderTool.Range("B11").Value <> "" Then
' 発注リストの出力エリアをクリアする
shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents
End If
とこのようになりました。このようにどんどん、デバッグしてできるだけ「落とし穴」埋めていきます。
先を考えるのならリファクタリングするべき
しかし、これでももしかしたら不十分かもしれません。もし、何かこれは落とし穴なのでは？というのがあれば、気軽にコメントいただければと思います。
さて、このままでも一応落とし穴はないという前提なのでプログラムは「ちゃんと」動きます。ですが、これではまだ80%なのです。これを100%にするにはプログラムを「きれいに」整理しなければいけません。このプログラムをきれいにする作業のことを「リファクタリング」と呼んでいます。
リファクタリングをすると何が良いのか？
- 保守性が上がる
- 可読性が上がる
- 拡張性が上がる
などのメリットがあります。
保守性があがると改修やエラー検知が楽になる
リファクタリングをするとプログラムが綺麗になるので、もし何か間違いがあっても見つけやすくなります。また影響範囲も限定的に抑えることができます。
可読性が上がると、他の人が見てもわかりやすい
当然綺麗にしているので、他の人が見ても、読みやすいプログラムとなります。また自身も当然読みやすいので、忘れた後でプログラムを見ても思い出すのも早いです。
拡張性が上がると、バージョンアップや仕様変更が楽になる
リファクタリングをすると仕様変更に対応しやすくなります。ぐちゃぐちゃのプログラムを直すより、整理されているプログラムを直す方が楽ですよね？
このようにリファクタリングをすることでプログラムを作った後が格段に楽になるのです。ではどのようにすれば整理できるのでしょうか？
処理を分ける
処理をわけるとは1つの長いプロシージャをいくつかにまとめてプロシージャを増やします。理由は簡単で、「処理を分けると可読性が上がるから」です。1つのメソッドに100行くらいあるとすると読むのが非常に大変です。しかし、30行が3つくらいなら何とか読める気がしませんか？
同じ処理をまとめる
似たようなことをやっているところをまとめてしまうと楽です。同じ処理が沢山あると、その処理に変更がある度に、複数のプロシージャに変更を加えなければいけません。
さっそく、やってみましょう。前回のコードです。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResultFirst As Range ' 1回目の検索結果を保持用 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 Dim outputRow As Long ' 発注リストの出力行 outputRow = 11 ' 発注リストの出力エリアをクリアする shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents ' 最初の検索結果を取得する Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) Set rngFindResultFirst = rngFindResult ' 検索結果があればさっきの処理をする If Not rngFindResultFirst Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If Else MsgBox "この会社の商品はありません。", vbOKOnly + vbExclamation Exit Sub End If ' 繰り返しをスタートする Do ' 在庫表シートの仕入れ先の列から会社名を検索（検索結果の次のセルから） Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole, After:=rngFindResult) ' 検索結果があれば・・・ If Not rngFindResult Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If End If ' 検索結果が最初のRangeオブジェクトになるまでずっと Loop Until rngFindResult.Address = rngFindResultFirst.Address ' 発注する製品がない場合にメッセージを表示する If shtOrderTool.Range("B11").Value = "" Then MsgBox "発注に必要な商品はありません", vbOKOnly End If End Sub
さて、似たところがありますよね？それは・・・
' 検索結果があればさっきの処理をする
If Not rngFindResultFirst Is Nothing Then
' 検索結果の行の商品在庫と必要在庫数を比較
lngStock = shtStock.Cells(rngFindResult.Row, "D").Value
lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value
' 在庫 <= 必要在庫数
If lngStock <= lngNeedOrder Then
' その行で必要な情報を発注リスト表へ出力する
' No
shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value
' 商品名
shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value
' 現在庫
shtOrderTool.Cells(outputRow, "D").Value = lngStock
' 仕入れ値＝価格*0.7の切り捨て
shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7)
outputRow = outputRow + 1
End If
Else
MsgBox "この会社の商品はありません。", vbOKOnly + vbExclamation
Exit Sub
End If
End Sub
と
' 検索結果があれば・・・
If Not rngFindResult Is Nothing Then
' 検索結果の行の商品在庫と必要在庫数を比較
lngStock = shtStock.Cells(rngFindResult.Row, "D").Value
lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value
' 在庫 <= 必要在庫数
If lngStock <= lngNeedOrder Then
' その行で必要な情報を発注リスト表へ出力する
' No
shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value
' 商品名
shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value
' 現在庫
shtOrderTool.Cells(outputRow, "D").Value = lngStock
' 仕入れ値＝価格*0.7の切り捨て
shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7)
outputRow = outputRow + 1
End If
End If
これです。どちらも検索結果に対しての処理になりますので、これをまとめてしまいます。どうまとめるか？というと・・・
処理をプロシージャにしてまとめてしまう。
これを別のプロシージャとして作成するのです。こんな風にしてみました。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 検索結果から発注が必要な商品を出力する ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub OutputProduct(ByVal rngFindRet As Range) Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindRet.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindRet.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindRet.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindRet.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindRet.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If End Sub
検索結果を引数に持った新たなプロシージャです。検索結果を元に在庫表を調べて、発注リストへ出力します。こうすると、その前の部分では、検索結果を引数に入れてこのプロシージャをコールすれば良いのです。つまり・・・
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResultFirst As Range ' 1回目の検索結果を保持用 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 outputRow = 11 If shtOrderTool.Range("B11").Value <> "" Then ' 発注リストの出力エリアをクリアする shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents End If ' 最初の検索結果を取得する Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) Set rngFindResultFirst = rngFindResult ' 検索結果があればさっきの処理をする If Not rngFindResultFirst Is Nothing Then Call OutputProduct(rngFindResultFirst) Else MsgBox "この会社の商品はありません。", vbOKOnly + vbExclamation Exit Sub End If ' 繰り返しをスタートする Do ' 在庫表シートの仕入れ先の列から会社名を検索（検索結果の次のセルから） Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole, After:=rngFindResult) ' 検索結果があれば・・・ If Not rngFindResult Is Nothing Then Call OutputProduct(rngFindResult) End If ' 検索結果が最初のRangeオブジェクトになるまでずっと Loop Until rngFindResult.Address = rngFindResultFirst.Address ' 発注する製品がない場合にメッセージを表示する If shtOrderTool.Range("B11").Value = "" Then MsgBox "発注に必要な商品はありません", vbOKOnly End If End Sub
いかがでしょうか？Callの箇所が2か所ありますよね？処理が同じところをまとめたことで、こちらの処理がかなりすっきりしました。
グローバル変数を上手に使う
さて、ここで注目してほしいのですが、変数outputRowです。この変数の宣言は以前はoutputOrderProductプロシージャにありましたが、今はなくなっています。どこへ行ったのかというと・・・
Option Explicit ''''''''''''''''''''''''''''''''''''''''' ' メイン処理 ''''''''''''''''''''''''''''''''''''''''' Public outputRow As Long ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) ・・・・ ・・・・ ・・・・
とこのように「外出し」しています。こうするとoutputOrderProductでも、OutputProductでも使えるわけです。こうするとメインの処理でこの変数を「初期化」し、各プロシージャでも利用できるのです。このように複数のプロシージャや、やりたい処理全般にわたって必要なデータはグローバル変数にすると便利です。
さて、これで本当に終わりでしょうか？
もっと分けられないか？整理できないか？
もう少し考えを進めて、もうちょっと変えてみましょう。今度は・・・
' 検索結果があればさっきの処理をする
If Not rngFindResultFirst Is Nothing Then
に注目して、これもプロシージャへ移動できないか？と考えました。そして、こんな風にしてみました。一旦全部のコードを載せておきます。
Option Explicit ''''''''''''''''''''''''''''''''''''''''' ' メイン処理 ''''''''''''''''''''''''''''''''''''''''' Public outputRow As Long ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResultFirst As Range ' 1回目の検索結果を保持用 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 Dim retFunction As Boolean outputRow = 11 If shtOrderTool.Range("B11").Value <> "" Then ' 発注リストの出力エリアをクリアする shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents End If ' 最初の検索結果を取得する Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) Set rngFindResultFirst = rngFindResult ' 検索結果があればさっきの処理をする If OutputProduct(rngFindResultFirst) = False Then MsgBox "この会社の商品はありません。", vbOKOnly + vbExclamation Exit Sub End If ' 繰り返しをスタートする Do ' 在庫表シートの仕入れ先の列から会社名を検索（検索結果の次のセルから） Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole, After:=rngFindResult) retFunction = OutputProduct(rngFindResult) ' 検索結果が最初のRangeオブジェクトになるまでずっと Loop Until rngFindResult.Address = rngFindResultFirst.Address ' 発注する製品がない場合にメッセージを表示する If shtOrderTool.Range("B11").Value = "" Then MsgBox "発注に必要な商品はありません", vbOKOnly End If End Sub ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 検索結果から発注が必要な商品を出力する ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function OutputProduct(ByVal rngFindRet As Range) Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 ' 検索結果があればさっきの処理をする If Not rngFindResultFirst Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindRet.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindRet.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindRet.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindRet.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindRet.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If OutputProduct = True Exit Function Else OutputProduct = False Exit Function End If End Function
どうでしょうか？わかりましたでしょうか？後で作ったプロシージャがFunctionになって、戻り値を持つようになっています。それによってメインの方で、結果によって処理を分けたりしています。これで大体の形はできましたね。これでひとまず、発注書ツールマクロの発注リストの出力部分は完成です。
( ﾟдﾟ)・・・長い！
はい、すいません。長いです。ですが、一つの処理を作るというのはこういうことだというのを体感して欲しいです。たった一つのやりたいことを日本語にした結果、こうなりました。
とりあえず、実現したいこと
- 会社を選択したら・・・
- 在庫表からその会社の商品をすべて検索して
- 発注が必要な商品をリストに出力する
たった、3行の日本語ですが、これを翻訳していくと、上のようなコードになるのです。すごく大変でしたが、これができれば後はそんなに大変ではないと思います。あとやりたいことは・・・
全体を通してしたいこと
- 自動でCSV在庫表を取込たい
- 発注が必要な商品リストが見たい。
- 商品は取引先ごとに見たい
- 自分で数を入力して発注書を印刷したい
- できればFAXも自動でしたい
でしたよね。次回から「自分で数字を入れて発注書を印刷する。」へ行きたいと思います。これができたら、ぶっちゃけベータ版として担当者に使ってもらえるレベルにまで行けます！楽しみですね！！
今日はここまで！
かしこ。
