初心者のためのExcel(エクセル)マクロVBA入門-VLOOKUP関数を使う：WorksheetFunction.vlookup

さて、前回エラー処理を勉強したのでちゃんとこれからのサンプルにはエラー処理を載せていきたいと思います。今回は通常のエクセルでも良く使いさらにMOSでも頻繁に試験とかで出てくる「VLOOKUP関数」についてです。この関数は本当に便利なので普通の使い方もちゃんと覚えておくといいと思います。これはさすがにググれば解決する問題です。
今回はVLOOKUP関数自体の使い方には特に触れないと思うので、分からない人は事前に使い方をマスターしておきましょう。注意するのはせいぜい、検索範囲の対象になる列は必ず指定範囲の1列目ということくらいです。ではやってみましょう。

VBAでエクセルの関数の使う場合

一般的にエクセルの関数（SUMとかCOUNTとか）を使う場合は、基本としてはこんな感じです。

Application.WorksheetFunction.関数名(引数・・・)

戻り値を持つ関数を使う場合は、戻り値を格納する変数を使って代入式にします。
まぁ大体戻り値あるんですけど・・・

Dim ret As Variant ' 戻り値を格納する変数

ret = Application.WorksheetFunction.関数名(引数・・・)

変数はVariant型で宣言していますが、関数の種類によって変更しましょう。また、Applicationは省略できます。ここまでが基本的な書き方です。当然VLOOKUP関数も例外ではなく・・・

Dim ret As Variant ' 戻り値を格納する変数

ret = Application.WorksheetFunction.VLookup(検索値, 検索範囲, 取得列番号, 検索モード)

となりますね。

VLookupをVBAで使うと・・・

簡単な例でやってみましょう。例えば、成績表のようなシートがあって、番号の人の成績を出力するマクロです。こんなの直でセルに関数ぶち込めよ！と言われそうですが、勉強のためVBAを使いましょう。ちゃんとエラー処理も書きます。
まずは、対象のシートですね。

このようになっていて、下の方に指定した出席番号の人の名前と成績を出力するマクロです。順番にやっていきましょう。まずははじめと終わりを書きます。

Sub sample1()
On Error GoTo catchErr


    
    'マクロ終了はここに
    Exit Sub
    
catchErr:
    'エラーになった時の処理
    

End Sub

はい、これで出来ました。プロシージャー名のすぐ後にOn Error Gotoがあるのは前回やった通りです。Exit Subも前回やりました。では、それぞれの処理を箇条書きしてみましょう。

• 出席番号を入力してもらう
• 番号を使ってVlookUpで名前と点数を取得する
• 所定の場所へ出力する

大きく分けるとこんな感じですのでこれをそのままプログラムに書いておきます。後変数の宣言を強制するオプションも追加で書いておきましょう。

' 変数の宣言を強制するオプション
Option Explicit

Sub sample1()
On Error GoTo catchErr

    
    ' 出席番号を入力してもらう
    
    ' 番号を使ってVlookUpで名前と点数を取得する
    
    ' 所定の場所へ出力する
    
    'マクロ終了
    Exit Sub
    
catchErr:
    'エラーになった時の処理
    

End Sub

はい、あとは日本語で書いたことをコードにすればいいだけです。番号を入力してもらうにはこのブログの最初の方にちょっとだけで出て来たんですがInputBox関数を使います。覚えてますでしょうか・・・InputBox関数・・・出席番号を入力するフォームを用意してもいいですが、とりあえず今回はそこまでは気にせずとにかく作ってみます。
InputBox関数は入れてもらった入力値を返してくれます。したがってその値を入れる変数が必要ですね。また、名前や各教科の点数を格納するための変数も必要です。つまり・・・

• 出席番号を格納する変数
• 氏名
• 算数点数
• 国語点数
• 理科点数
• 社会点数
• 英語点数

以上を格納する変数も用意しましょう。

Option Explicit

Sub sample1()
On Error GoTo catchErr
    
    ' 必要な変数を作成する
    Dim shussekiNumber As Long      ' 出席番号を格納する変数
    Dim name As String              ' 氏名
    Dim sansu As Long               ' 算数点数
    Dim kokugo As Long              ' 国語点数
    Dim rika As Long                ' 理科点数
    Dim syakai As Long              ' 社会点数
    Dim eigo As Long                ' 英語点数
    
    ' 出席番号を入力してもらう
    shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1)
    
    ' 番号を使ってVlookUpで名前と点数を取得する
    
    
    ' 所定の場所へ出力する
    
    'マクロ終了
    Exit Sub
    
catchErr:
    'エラーになった時の処理
    

End Sub

はい、できました。後は、残りの番号を使ってVlookUpで値を取得して、出力するだけですね。では早速書いてみましょう。簡単です。それぞれ変数に入力して所定の場所へ出力するだけです。

Option Explicit

Sub sample1()
On Error GoTo catchErr
    
    ' 必要な変数を作成する
    Dim shussekiNumber As Long      ' 出席番号を格納する変数
    Dim name As String              ' 氏名
    Dim sansu As Long               ' 算数点数
    Dim kokugo As Long              ' 国語点数
    Dim rika As Long                ' 理科点数
    Dim syakai As Long              ' 社会点数
    Dim eigo As Long                ' 英語点数
    
    ' 出席番号を入力してもらう
    shussekiNumber = InputBox("出席番号を入力してください", "出席番号入力", 1)
    
    ' 番号を使って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
    
catchErr:
    'エラーになった時の処理
    

End Sub

ちょっと長いですが、すごくシンプルです。各教科の値を取得して、出力するだけです。これで、一旦は完成ですが、実はこのプログラムには2つほど、例外となってエラーになる箇所が存在します。それは、どこでしょうか？

続きと答えは次回で。

今日はここまで！

かしこ