初心者のためのExcel エクセルマクロVBA入門-実践：エラーを除くデバッグ処理（発注書マクロ）
デバッグなしには完成はない
ほぼ出来ているプログラムですが、これで終わりではありません。エクセルマクロは・・・
ちゃんと動いてなんぼ
です。このように組み立てたプログラムですが、やっぱりまだ「落とし穴」が存在します。まだこのマクロはいろんなところが未熟なのです。これを磨いて、きれいして、きちんと「動く」ものに仕上げていきます。前回のプログラムをもう一度記載します。
発注書シートの会社名を選ぶと、その会社名の商品で発注が必要な商品を抜き出してリストとして出力するプログラムでしたね。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：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 ' 最初の検索結果を取得する 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 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 End Sub
まぁ、まずは実際に動かしてみましょう！すると・・・
( ﾟдﾟ)ふぁぁ！？
はい、いきなり躓きましたね。実際に何が起きたのか？を見て見ましょう。デバッグボタンを押してください。
エラーが出た箇所が黄色になっているはずです。この行で何が起きたのでしょうか？調べる為にまずはマウスをcorpNameの変数に当ててみてください。すると「商社7」などのちゃんと変数に入っている値が表示されます。さらにrngFindResultに当ててみます。すると上の画像のように「Nothing」と表示されましたね。これがどうやら原因です。つまり、
検索開始位置がNothingだと何処から検索していいのかわからん！
と言って、エクセルさんに怒られたわけです。でもなんでNothingになったのでしょうか？それは、、、
' 最初の検索結果を取得する
Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole)
この行に原因が潜んでいます。要するに最初に検索した時から結果がなかったということです。つまり会社によっては在庫表に一つもない会社が存在する。ということになりますね。まぁよく考えたら当たり前ですよね。以前は付き合いがあって仕入れていたけど最近は魅力ある仕入れ品がないので仕入れてない。なんてことは良くある話です。ですので、このように書き換える必要がありますね。また、日本語で書きますよ？
最初の検索で検索結果がなければ、ないというメッセージでプログラムが終わる。
ですね。さっそく翻訳して組み込みます。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：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 ' 最初の検索結果を取得する 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
前半部分だけ抜き出しています。変わったのは、Elseが付いたことですね。これで、検索結果がなければ、メッセージを表示して、処理を終了するということになります。
vbOKOnly + vbExclamation
MsgBox関数に指定したのは前回お話した「定数」です。これもわかりやすく言葉で定義してあります。今回は「検索結果がありませんでした」というメッセージを表示してOKボタンのみにしたい、かつ警告にしたいのでvbOKOnlyとvbExclamationを指定しています。
さて実行しましょう！設定したメッセージが出て後は何も起こらないことが確認できたでしょうか？さて、今度は実際に在庫表にある会社を指定して動かしてみます。さぁどうでしょうか？気が付きますか？
会社のリストを変更しても、なんか動きません。
( ﾟдﾟ)？
どうやら、一度セルを選択しないと動かないようです。これはなぜでしょうか？原因はここのどこかにあります。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 会社名が選択された時に処理されるイベント ' ここに具体的な処理は書かない ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' 変更されたのは会社名のセルなのか？ If Not Application.Intersect(Target, shtOrderTool.Range("CorprateName")) Is Nothing Then ' 変更されたのが会社名だったら・・・ ' 在庫表からその会社の商品をすべて検索して ' 発注が必要な商品をリストに出力する ' ような処理をここから呼ぶ。 Call outputOrderProduct(Target.Value) ' それ以外のセルの時は何もしないよ End If End Sub
正解は
Worksheet_SelectionChange
です。これは「セルの選択範囲が変わった時」に実行されるイベントプロシージャです。ですのでちゃんと「セルの値が変わった時に実行される」イベントプロシージャに書き換えます。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 会社名が選択された時に処理されるイベント ' ここに具体的な処理は書かない ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub Worksheet_Change(ByVal Target As Range) ' 変更されたのは会社名のセルなのか？ If Not Application.Intersect(Target, shtOrderTool.Range("CorprateName")) Is Nothing Then ' 変更されたのが会社名だったら・・・ ' 在庫表からその会社の商品をすべて検索して ' 発注が必要な商品をリストに出力する ' ような処理をここから呼ぶ。 Call outputOrderProduct(Target.Value) ' それ以外のセルの時は何もしないよ End If End Sub
セルの値が変わった時に実行されるのは「Worksheet_Change」プロシージャです。これで、ちゃんと会社名が変わったらイベントが実行されるようになりました。
( ﾟдﾟ)？
え？なんでこんなことになってるのか？ですか？それはですね。わ、ざ、と！ですよ。ちゃんと考えてこうやって「ミス」を仕込んでおいたのです！はははははは！（乾いた笑い）
さて、さらにさらに、色々と動かすと気が付くことがあります。
会社名を指定しても何も起こらない・・・
やりたい処理を日本語で書くだけは不完全
どうしたいのか？というのを日本語で書くということを前回紹介しましたが、これだけではやはり不完全なのです。なぜなら、やりたいことだけでは、見落としているところが沢山あるからです。今回であれば、毎回発注リストのエリアはクリアされないといけないということを見落としてますし、在庫表にリストがあっても、発注が必要ない会社の場合はどうしたらいいかという点を見落としています。このように「見落とし」をどんどん見つけて、プログラムを完成へと近づけるのがまさに「デバッグ」なのです！
さて、ではこの2つの見落としを直してみましょう。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：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
加えたはこの２つ
' 発注リストの出力エリアをクリアする
shtOrderTool.Range(shtOrderTool.Range("B11"), shtOrderTool.Cells(Rows.Count, "E").End(xlUp)).ClearContents
' 発注する製品がない場合にメッセージを表示する
If shtOrderTool.Range("B11").Value = "" Then
MsgBox "発注に必要な商品はありません", vbOKOnly
End If
まず、発注リストに前の内容が残っていては困るので、これを解消します。発注リストの出力エリアをクリアするわけですね。それをこの1文でやっています。ちょっと長いですが全然、難しくありません。
シート名.範囲.ClearContents
「発注リストシートのB11から発注リストシートの最終行から最後に入力してあるセルまでの値をクリアする」
と書かれています。範囲の指定の仕方はこちらにあるので見てください。
それと、発注する製品が何もない場合にはメッセージを表示します。これも、使う人への「優しさ」です。実際に色々とやってみるとこれもわかりますよね？会社名を指定したのにメッセージがないと「何も起きない」だけになってしまいます。ちゃんと発注する商品がないよ。ということを担当者に教えてあげないといけません。
「発注する商品がない」
↓
「発注リストに商品がない」
↓
「発注リストシートに1つ目が空欄なら商品がない」
↓
「If shtOrderTool.Range("B11").Value = "" Then」
という翻訳です。
さて、これでほぼ！完成です！ですが最後の仕上げが残っています。リファクタリングです。今度はこのプログラムを整理しましょう！
今日はここまで！
かしこ
