初心者のためのExcel(エクセル)マクロVBA入門-成績表マクロの作成：ユーザー定義型を使う
前回書いたユーザー定義型のコードについてもう少し詳しく説明します。
' 科目のユーザー定義型 Public Type Kamoku Name As String '科目名 Score As Variant '点数 End Type
と
' 科目の配列を作成します。 Dim arryKamoku() As Kamoku
でしたね。
ユーザー定義型はオリジナルの変数の型となる。
Publicは他のモジュールでも使いたいので付けています。Typeステートメントがこれからユーザー定義型を作るよ。という意味になり、その後にオリジナルの名前「Kamoku」があります。Typeの終わりはEnd Typeとなります。
その後、NameとScoreという2つの変数を作成していますね。
これで、KamokuはNameとScoreの２つの変数を扱うことができます。当然ですが、この中にはいくつでも変数を作成でき、Kamokuとして扱うことができるわけです。これで、
新しい変数の型をオリジナルで作った。
と考えてください。そして、配列としてarryKamokuという名前の配列を新しい変数の型「Kamoku」でMainプロシージャ内に作成しました。これはどういうことかと言うと、Kamoku型の変数がインデックス付きで複数存在するということになります。つまり・・・
arryKamoku(0).Name
arryKamoku(0).Score
arryKamoku(1).Name
arryKamoku(1).Score
arryKamoku(2).Name
arryKamoku(2).Score
・・・
・・・
・・・
というようにたくさんのKamokuがあるわけです。これで算数だったり、国語だったりを一つのarryKamokuとして扱うことができそうですね。
オブジェクトと考えてもいい
ユーザー定義型をオブジェクトと捉えることもできます。Kamokuオブジェクトですね。KamokuオブジェクトはNameプロパティとScoreプロパティという2つの属性を持ったオブジェクト。という考え方もできます。そして、オブジェクトを配列として作成した。インデックス付きのオブジェクト。という考え方です。
どちらでも、分かりやすい方で理解してください。
ユーザー定義型の使い方
ではさっそく、ユーザー定義型Kamokuの配列であるarryKamokuに科目名と、点数を代入しましょう。次のようにメインのプログラムを書き換えます。結構大きく変わるので全体で載せておきます。いらない部分はコメントアウトしています。
Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim i As Long ' 繰り返し用変数 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 arryKamoku() As Kamoku Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号をフォームから取得する shussekiNumber = Sheet1.txtSyussekiNumber.Value ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) ' 3列目（科目の始まり列）から科目名の最後まで For i = 3 To Sheet1.Range("XFD3").End(xlToLeft).Column ' 変数arryKamokuの配列の数を再定義する ReDim Preserve arryKamoku(i - 3) ' 出席番号を使ってVlookUpで名前と点数を取得する ' ' 科目名は3行目固定で、C列から arryKamoku(i - 3).Name = Sheet1.Cells(3, i).Value ' 順番にその科目の点数を取得 arryKamoku(i - 3).Score = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), i, False) Next strErrMsg = CheckScore(arryKamoku) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 名前を取得する Name = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), 2, False) ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = Name For i = 0 To UBound(arryKamoku) Sheet1.Cells(19, i + 3) = arryKamoku(i).Score Next ' 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
さらに、CheckScoreメソッドも書きか変えます。以下のように。
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' 点数チェックのメソッド ' CheckScore ' 引数：score As String ' 戻り値:String ' エラーの場合はエラーメッセージを返す。 ' エラーがない場合は空文字を返す。 ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function CheckScore(ByRef arryKamoku() As Kamoku) As String Dim i As Long ' 科目数分繰り返す For i = 0 To UBound(arryKamoku) '数字かどうかをチェックします。 If Not IsNumeric(arryKamoku(i).Score) Then ' 数字ではない場合「成績表の点数が不正です。処理を終了します。」 CheckScore = mdlDefine.ERROR_MSG3 Exit Function End If Next ' それ以外はエラーなしとして空文字「""」を返す CheckScore = "" End Function
このままでは、はっきり言ってよくわからないと思います。まず、各科目ごとの科目名と点数を格納する配列変数arryKamokuがあるので各教科の点数を格納する変数はすべてコメントアウトしています。さらに、前回の処理の順番通りに・・・
- 出席番号を取得する
- 出席番号と各科目の点数をチェックする
- エラーだったら終了
- 各科目の点数をシートに出力する
出席番号のチェックが終わった後に、各科目の科目名と点数を取得しています。For i = 3 To Sheet1.Range("AA3").End(xlToLeft).ColumnからNextまでの間です。ここでは、3列目の算数～最後の科目の列まで繰り返して、科目名と出席番号に合致した点数を取得しています。arryKamoku(i-3)は配列のインデックスが0から始まるからですね。ReDimステートメントは配列を再定義するのに使います。動的な配列の場合はPreserveステートメントも使って、すでに格納された値を保持したまま配列を拡張します。こうすることで過不足なく配列を作成できるのです。
他にもあったバグ（訂正）
実は他にも変わっているところがあるんです。
Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), 2, False)
です。CLng(shussekiNumber)ですね。文字列shussekiNumberをLong型にキャストしています。出席番号自体が数字なので、Long型へ変更して検索しているわけです。出席番号が文字列ならキャストは必要ありません。
CheckScoreスコアメソッドの引数を値渡しから参照渡しへ変えています。この参照渡しについては次回じっくりと説明します。ので今回は割愛します。
そして、最後にエラーメッセージの確認をしています。
こうすることで、VlookUpをたくさん書いたり他に変数も沢山用意する必要がなくなりました。これでもう完成でしょう！！！
と言いたいところですが、実はまだバグが潜んでいます。
そうです。このままでは出席番号に文字列を入れた時点でエラーが発生してしまいます。ですので、エラーメッセージのチェックはこのままではやっぱりもう1つ必要になります。つまり、InputCheckメソッドを実行した後に、If strErrMsg <> "" Thenが必要ということです。コメントアウトしたコードを削除し、この部分を追加すると・・・
Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim i As Long ' 繰り返し用変数 Dim Name As String ' 氏名 Dim arryKamoku() As Kamoku ' 科目の配列を作成します。 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号をフォームから取得する shussekiNumber = Sheet1.txtSyussekiNumber.Value ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 3列目（科目の始まり列）から科目名の最後まで For i = 3 To Sheet1.Range("XFD3").End(xlToLeft).Column ' 変数arryKamokuの配列の数を再定義する ReDim Preserve arryKamoku(i - 3) ' 出席番号を使ってVlookUpで名前と点数を取得する ' ' 科目名は3行目固定で、C列から arryKamoku(i - 3).Name = Sheet1.Cells(3, i).Value ' 順番にその科目の点数を取得 arryKamoku(i - 3).Score = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), i, False) Next strErrMsg = CheckScore(arryKamoku) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End If ' 名前を取得する Name = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), 2, False) ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = Name For i = 0 To UBound(arryKamoku) Sheet1.Cells(19, i + 3) = arryKamoku(i).Score Next 'マクロ終了 Exit Sub cmnErr: MsgBox "エラーが発生しました" & vbCrLf & _ "エラー番号:" & Err.Number & vbCrLf & _ "エラーの種類:" & Err.Description, vbOKOnly + vbExclamation, _ mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End Sub
となります。さらにさらに、同じ処理が2つありますよね？このままですとメインの処理でプログラムを終了する箇所が2つできてしまいます。これはわかりにくいコードですし、同じ処理はまとめましょう。エラーかどうかをチェックしてエラーなら終了するメソッドを作成します。メソッドCheckErrorを新設し、MainからはCallステートメントで呼び出すだけにします。
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' エラーチェックのメソッド ' CheckError ' 引数：strErrMsg As String ' 戻り値:なし ' エラーの場合はマクロを終了する ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub CheckError(ByVal strErrMsg As String) ' エラーだったらメッセージを表示して終了する If strErrMsg <> "" Then 'エラーになった時の処理 MsgBox strErrMsg, vbOKOnly + vbExclamation, mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する End End If End Sub
Mainプロシージャの方で・・・
Sub Main() '一般のエラーをキャッチする On Error GoTo cmnErr ' 必要な変数を作成する Dim shussekiNumber As String ' 出席番号を格納する変数 Dim i As Long ' 繰り返し用変数 Dim Name As String ' 氏名 Dim arryKamoku() As Kamoku ' 科目の配列を作成します。 Dim strErrMsg As String ' 入力チェックの結果を格納する変数 ' 出席番号をフォームから取得する shussekiNumber = Sheet1.txtSyussekiNumber.Value ' InputCheckメソッドを呼び出してエラーメッセージを格納する strErrMsg = InputCheck(shussekiNumber) ' エラーだったらメッセージを表示して終了する Call CheckError(strErrMsg) ' 3列目（科目の始まり列）から科目名の最後まで For i = 3 To Sheet1.Range("XFD3").End(xlToLeft).Column ' 変数arryKamokuの配列の数を再定義する ReDim Preserve arryKamoku(i - 3) ' 出席番号を使ってVlookUpで名前と点数を取得する ' ' 科目名は3行目固定で、C列から arryKamoku(i - 3).Name = Sheet1.Cells(3, i).Value ' 順番にその科目の点数を取得 arryKamoku(i - 3).Score = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), i, False) Next ' 各科目の点数をチェックする strErrMsg = CheckScore(arryKamoku) ' エラーだったらメッセージを表示して終了する Call CheckError(strErrMsg) ' 名前を取得する Name = Application.WorksheetFunction.VLookup(CLng(shussekiNumber), Sheet1.Range("A3:G13"), 2, False) ' 所定の場所へ出力する Sheet1.Range("A19").Value = shussekiNumber Sheet1.Range("B19").Value = Name For i = 0 To UBound(arryKamoku) Sheet1.Cells(19, i + 3) = arryKamoku(i).Score Next 'マクロ終了 Exit Sub cmnErr: MsgBox "エラーが発生しました" & vbCrLf & _ "エラー番号:" & Err.Number & vbCrLf & _ "エラーの種類:" & Err.Description, vbOKOnly + vbExclamation, _ mdlDefine.ERROR_WINDOW_TITLE 'マクロを終了する Exit Sub End Sub
これで、完成です。後は印刷処理やテンプレートファイルを作成してそちらに入力するような処理、などを入れてしまえばOKです。
いかがでしょうか？重複する処理はまとめることで自然とプロシージャを分けることができます。変数の捉え方を考えることで自然と構造体を考えることにより、さらにコードを効率的に書くことができました。
さて、他の改善点はあるでしょうか？実はまだまだあります。
- そもそもVlookUp使う必要ってあるの？（Findメソッドでいいじゃないか？）
- 科目を探す範囲って固定だけど科目が増えたら修正しないといけない
- つまり自動的に検索範囲って決められないかな？
- 名前とか出席番号も成績表っていう変数としてまとめればもっと楽じゃないかな？
などなど・・・こうしてマクロをもっと進化させていくわけです。
次回は、引数の値渡しと参照渡しについてがっつり説明し、その次の回でこの成績表マクロを一度ぶっ壊して！再設計してみたいと思います。
現在かなり、難しくなっていると思いますのでブログでわからない所はコメントなりTwitterなりで何なりと質問くださいませ。
私の説明はかなり文章量が多く、分かりにくいと思うので・・・汗
今日はここまで！
かしこ
