WorksheetFunction. VLookup 戻り値

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

    
   
    
   
    
   
    
   
    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)
    
   
    
    
   
    
   
    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)
    
   
    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つほど、例外となってエラーになる箇所が存在します。それは、どこでしょうか？

続きと答えは次回で。

今日はここまで！

かしこ