初心者のためのExcel(エクセル)マクロVBA入門-成績表マクロの作成:一般エラーをキャッチする
どんどんやっていきます。まずは前回までのコードです。
Option Explicit Sub Main() '出席番号のエラーをキャッチする On Error GoTo shussekiErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim name As String ' 氏名 Dim sansu As Long ' 算数点数 Dim kokugo As Long ' 国語点数 Dim rika As Long ' 理科点数 Dim syakai As Long ' 社会点数 Dim eigo As Long ' 英語点数 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号を入力してもらう(ここで例外発生する!) shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1) ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If 'VLookupのエラーをキャッチする On Error GoTo vlookupErr ' 番号を使ってVlookUpで名前と点数を取得する(ここでも例外発生する!) name = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 2, False) sansu = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 3, False) kokugo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 4, False) rika = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 5, False) syakai = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 6, False) eigo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 7, False) ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = name Sheet1.Range("C19").Value = sansu Sheet1.Range("D19").Value = kokugo Sheet1.Range("E19").Value = rika Sheet1.Range("F19").Value = syakai Sheet1.Range("G19").Value = eigo 'マクロ終了 Exit Sub shussekiErr: 'エラーになった時の処理 MsgBox "出席番号が存在しません。処理を終了します。", vbOKOnly + vbExclamation, "エラー" 'マクロを終了する Exit Sub vlookupErr: MsgBox "出席番号が見つかりません。処理を終了します。", vbOKOnly + vbExclamation, "エラー" 'マクロを終了する Exit Sub End Sub
さて、ここからですが前回までで、想定できる例外についてはOn Error Gotoで指定した先ではなくて処理の中で順番にチェックしてその都度メッセージを表示して終了するとしてコードを修正しました。ここからさらに修正していきます。今度は想定してない場合のエラーが起きた場合には今度こそOn Error Gotoで指定した先でキャッチしてあげなければいけません。そのために、上のコードに以下の修正を加えていきます。
- エラーのラベルと飛ぶ先は1つにする。
- 今書いてあるエラーメッセージの部分は既に対応したので削除する
- さらに一般的なエラーの情報を表示して処理を終了するようにする
これらを加えて行きます。
Option Explicit Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim name As String ' 氏名 Dim sansu As Long ' 算数点数 Dim kokugo As Long ' 国語点数 Dim rika As Long ' 理科点数 Dim syakai As Long ' 社会点数 Dim eigo As Long ' 英語点数 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号を入力してもらう(ここで例外発生する!) shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1) ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 番号を使ってVlookUpで名前と点数を取得する(ここでも例外発生する!) name = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 2, False) sansu = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 3, False) kokugo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 4, False) rika = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 5, False) syakai = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 6, False) eigo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 7, False) ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = name Sheet1.Range("C19").Value = sansu Sheet1.Range("D19").Value = kokugo Sheet1.Range("E19").Value = rika Sheet1.Range("F19").Value = syakai Sheet1.Range("G19").Value = eigo 'マクロ終了 Exit Sub cmnErr: MsgBox "エラーが発生しました。" & vbCrLf & _ "エラー番号:" & Err.Number & vbCrLf & _ "エラーの種類:" & Err.Description, vbOKOnly + vbExclamation, _ mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End Sub
Errオブジェクトについて
上記のコードで一旦一般的なエラーはすべて拾うことができます。ここで新しいものが出てきました。Errオブジェクトです。Errオブジェクトは例外関係のオブジェクトであり、エラーに関する様々な情報を持っています。例えばエラーが発生した場合のエラーの種類はなんなのか?そのエラーの番号はなんなのか?などがあります。その一部を紹介します。
プロパティ/メソッド | 働き |
---|---|
Number プロパティ | エラーの種類を識別する番号 |
Description プロパティ | エラーに関する簡単な説明 |
Clear メソッド | エラーの情報を初期化する |
Raise メソッド | エラーを発生させる |
などがあります。プロパティは他にもあるのでヘルプで確認するといいでしょう。
ここでは、NumberプロパティとDescriptionプロパティを使っています。他にもちょっとわかりにくかもしれないのでもう少し説明すると、「vbCrLf」は改行コードと呼ばれるもので、メッセージボックス内で改行したいときに使います。「&」は演算子について書いたブログでも説明した通り文字列をつなげるための演算子です。
また、Gotoで指定した箇所を1か所消して、1番最初のOn Error Gotoのみ残しました。ラベル名もそれに伴って変更しています。
VlookUp関数での例外を処理する
これで大体終わりですがもう1つ残っています。それは、「成績表の点数におかしなデータが入って場合」です。これも前回やっている変数の型を改善して例外発生を回避して入力チェックをしましょう。各教科の点数を格納する変数の型をLongからString型へ変更し、さらに数字かどうかのチェックをします。数字じゃないならmdlDefineで定義してある点数が不正のメッセージを表示すればいいわけです。点数をチェックするのはまた別のメソッドを作成しましょう。点数をチェックするので「CheckScoreメソッド」とします。さっそく書いてみます。
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' 点数チェックのメソッド ' CheckScore ' 引数:score As String ' 戻り値:String ' エラーの場合はエラーメッセージを返す。 ' エラーがない場合は空文字を返す。 ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function CheckScore(ByVal score As String) As String '数字かどうかをチェックします。 If Not IsNumeric(score) Then ' 数字ではない場合「成績表の点数が不正です。処理を終了します。」 CheckScore = mdlDefine.ERROR_MSG3 Exit Function End If ' それ以外はエラーなしとして空文字「""」を返す CheckScore = "" End Function
あれ?これは良く見ると前回InputCheckメソッドでやったコードが入っていますね。同じような処理はまとめることができますが、とりあえず今はこのままいきましょう。
マクロを組む上で大切なこと。リファクタリングしよう!
まず動くこと!が大切です。そしてその後で、処理をまとめたり、見やすくしたりすればいいんです。これを「リファクタリング」と言います。これをしない人が本当に多いです。せっかく動いているマクロをまたいじって整理するなんて・・・と思いますが、効率的そしてパフォーマンスが良くかつ見やすいマクロを作成するのは絶対にリファクタリングしないとダメです。
これができるかどうか?で「ちゃんとマクロが組めるエクセルマスター」になれるかどうかが決まります。最初からきれいに書くことなんてできません。私でもできません。書いて動いて、それから、改めて直して、直して、直してやっときれいになるわけです。そうすることで汚いソースにある潜在的なエラーをなくしていくことができるのです。
ではこのメソッドをメインのマクロへ組み込んでみましょう。こうなります。
Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim name As String ' 氏名 Dim sansu As String ' 算数点数 Dim kokugo As String ' 国語点数 Dim rika As String ' 理科点数 Dim syakai As String ' 社会点数 Dim eigo As String ' 英語点数 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号を入力してもらう(ここで例外発生する!) shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1) ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 番号を使ってVlookUpで名前と点数を取得する(ここでも例外発生する!) name = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 2, False) '点数を取得して点数をチェックする sansu = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 3, False) strErrMsg = CheckScore(sansu) kokugo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 4, False) strErrMsg = CheckScore(kokugo) rika = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 5, False) strErrMsg = CheckScore(rika) syakai = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 6, False) strErrMsg = CheckScore(syakai) eigo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 7, False) strErrMsg = CheckScore(eigo) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = name Sheet1.Range("C19").Value = sansu Sheet1.Range("D19").Value = kokugo Sheet1.Range("E19").Value = rika Sheet1.Range("F19").Value = syakai Sheet1.Range("G19").Value = eigo 'マクロ終了 Exit Sub cmnErr: MsgBox "エラーが発生しました" & vbCrLf & _ "エラー番号:" & Err.Number & vbCrLf & _ "エラーの種類:" & Err.Description, vbOKOnly + vbExclamation, _ mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End Sub
変数がString型になっています。各教科の点数を取得しチェックをかけています。すべてチェックしてからエラーがあるかを調べています。はい!これでここまで完成です。
完成です。。。。
ここであれ?っと思った方は筋がいいです。そうこのマクロには致命的なバグが潜んでいます。次回はそれを直し、さらに構造体のお勉強をして、配列と組み合わせてさらに!効率的にかっこいいマクロにしていきます!
どこが間違いなのか?わかりますか?最後に今までに書いてあるマクロを整理して全部記載しておきます。どこが間違いなのか?探してみてくださいね。
標準モジュール:mdlMainのコード
Option Explicit Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim name As String ' 氏名 Dim sansu As String ' 算数点数 Dim kokugo As String ' 国語点数 Dim rika As String ' 理科点数 Dim syakai As String ' 社会点数 Dim eigo As String ' 英語点数 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号を入力してもらう(ここで例外発生する!) shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1) ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 番号を使ってVlookUpで名前と点数を取得する(ここでも例外発生する!) name = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 2, False) '点数を取得して点数をチェックする sansu = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 3, False) strErrMsg = CheckScore(sansu) kokugo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 4, False) strErrMsg = CheckScore(kokugo) rika = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 5, False) strErrMsg = CheckScore(rika) syakai = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 6, False) strErrMsg = CheckScore(syakai) eigo = Application.WorksheetFunction.VLookup(shussekiNumber, Sheet1.Range("A3:G13"), 7, False) strErrMsg = CheckScore(eigo) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = name Sheet1.Range("C19").Value = sansu Sheet1.Range("D19").Value = kokugo Sheet1.Range("E19").Value = rika Sheet1.Range("F19").Value = syakai Sheet1.Range("G19").Value = eigo 'マクロ終了 Exit Sub cmnErr: MsgBox "エラーが発生しました" & vbCrLf & _ "エラー番号:" & Err.Number & vbCrLf & _ "エラーの種類:" & Err.Description, vbOKOnly + vbExclamation, _ mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' 入力チェックのメソッド ' InputCheck ' 引数:shussekiNumber As String ' 戻り値:String ' エラーの場合はエラーメッセージを返す。 ' エラーがない場合は空文字を返す。 ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function InputCheck(ByVal shussekiNumber As String) As String '数字かどうかをチェックします。 If Not IsNumeric(shussekiNumber) Then ' 数字ではない場合「出席番号が存在しません。処理を終了します。」 InputCheck = mdlDefine.ERROR_MSG1 Exit Function End If '出席番号の中にあるかチェックします。 If CLng(shussekiNumber) < 0 And CLng(shussekiNumber) > 10 Then ' 出席番号が0以下または11以上の場合「出席番号が見つかりません。処理を終了します。」 InputCheck = mdlDefine.ERROR_MSG2 Exit Function End If ' それ以外はエラーなしとして空文字「""」を返す InputCheck = "" End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' 点数チェックのメソッド ' CheckScore ' 引数:score As String ' 戻り値:String ' エラーの場合はエラーメッセージを返す。 ' エラーがない場合は空文字を返す。 ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function CheckScore(ByVal score As String) As String '数字かどうかをチェックします。 If Not IsNumeric(score) Then ' 数字ではない場合「成績表の点数が不正です。処理を終了します。」 CheckScore = mdlDefine.ERROR_MSG3 Exit Function End If ' それ以外はエラーなしとして空文字「""」を返す CheckScore = "" End Function
標準モジュール:mdlDefineのコード
Option Explicit Public Const APP_TITLE = "成績表マクロ" Public Const ERROR_MSG1 = "出席番号が存在しません。処理を終了します。" Public Const ERROR_MSG2 = "出席番号が見つかりません。処理を終了します。" Public Const ERROR_MSG3 = "成績表の点数が不正です。処理を終了します。" Public Const ERROR_WINDOW_TITLE = "成績表マクロ:エラー"
今日はここまで!
かしこ