大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、「VLookup関数」を使って「別ブック」を参照するには「Workbooks」を使います。「WorksheetFunction」を使う場合は別ブックを「開いておく」必要があります。「埋め込み数式」の場合は別ブックを「閉じたまま」参照できるので高速です。

はじめに

この記事では、VBAで「VLookup関数」を使って「別ブック」を参照する方法についてご紹介します。

「別ブック」を参照するには「Workbooks」を使います。

「VLookup関数」をVBAで使うには「WorksheetFunction」と「数式埋め込み」を使う方法があります。

「WorksheetFunction」を使う場合は別ブックを「開いておく」必要があります。

「数式埋め込み」を使えば別ブックを「閉じたまま」参照することができます。

別ブックを「高速」で参照したいに場合に使えます。

では、VBAで「VLookup関数」を使って「別ブック」を参照する方法について、解説していきます。

この記事で紹介すること

  • VBAで「VLookup関数」を使って「別ブック」を参照する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAコードまとめ

「VLookup関数」を使って「別ブック」を参照するVBAコードについて、まとめています。

VBAコードだけ確認したい場合に、ご活用ください。

'■WorksheetFunctionを使う

'別ブックを参照(別ブックは開いておく)
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", Workbooks("TEST.xlsx").Worksheets("Sheet1").Range("A2:B10"), 2, False)

'■「数式埋め込み」を使う

'数式埋め込みでVLookup関数を使って、別ブックを参照(別ブックは開いておく)
ThisWorkbook.Worksheets("Sheet1").Range("A2") = "=VLOOKUP(""D"",[TEST.xlsx]Sheet1!$A$2:$B$10,2,FALSE)"

'数式埋め込みで、別ブックを「フルパス」で参照(別ブックを閉じたまま参照できる)
ThisWorkbook.Worksheets("Sheet1").Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"

では、解説していきます。

WorksheetFunctionを使ってVLookup関数で別ブックを参照する

「WorksheetFunction」を使ってVLookup関数で別ブックを参照してみます。

「Workbooks」を指定して別ブックを参照する

「WorkhsheetFunction」を使う場合、「Workbooks」を指定して別ブックを参照します。

「Workbooks」を指定する

Sub TEST1()
    
    'VLookup関数で、別ブックを参照
    ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", Workbooks("TEST.xlsx").Worksheets("Sheet1").Range("A2:B10"), 2, False)
    
End Sub

ちょっと長いので「With」でくくっておきます。

「With」でくくる

Sub TEST2()
    
    '「With」でくくる
    With Workbooks("TEST.xlsx").Worksheets("Sheet1")
        ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
    End With
    
End Sub

という感じになります。

ポイントは、「別ブックを開いておく」ということです。

別ブックを開いておく

別ブックを開いておく

では、VBAコードを実行してみます。

VLookup関数で「別ブック」を参照

VLookup関数で「別ブック」を参照

VLookup関数で「別ブック」を参照できました。

別ブックを開いておく必要がある

「WorksheetFunction」を使う場合は、別ブックを開いておく必要があります。

別ブックを閉じた状態で実行するとエラーとなってしまいます。

ちょっとやってみます。

Sub TEST3()
    
    '別ブックを開いていない場合は、エラーとなる
    With Workbooks("TEST.xlsx").Worksheets("Sheet1")
        ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
    End With
    
End Sub

別ブックを閉じた状態にしておきます。

別ブックを閉じた状態

別ブックを閉じた状態

VBAコードを実行してみます。

エラーになる

エラーになる
エラーになる

エラーとなっちゃいましたね。

「WorksheetFunction」を使う場合は「別ブック」を開いた状態で実行しましょう。

別ブックを開いて参照する

別ブックを開く操作がめんどくさい場合は、「別ブックを開く」というVBAコードも追加してあげると便利です。

「別ブックを開く」のと「閉じる」VBAコードを追加してみます。

Sub TEST4()
    
    '別ブックを開く
    Workbooks.Open "C:\TEST\TEST.xlsx"
    
    '別ブックを、VLookup関数で参照
    With Workbooks("TEST.xlsx").Worksheets("Sheet1")
        ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
    End With
    
    '別ブックを閉じる
    Workbooks("TEST.xlsx").Close
    
End Sub

別ブックは閉じた状態にしておきます。

別ブックは閉じた状態

別ブックは閉じた状態

では、VBAコードを実行してみます。

別ブックが開かれます。

別ブックが開かれる

別ブックが開かれる

別ブックを参照して、閉じられます。

別ブックを参照して閉じる

別ブックを参照して閉じる

別ブックを参照して閉じました。

こんな感じで、「別ブックを開いて閉じる」VBAコードを追加すると便利です。

埋め込み数式を使ってVLookup関数で別ブックを参照

次は、「埋め込み数式」を使ってVLookup関数で「別ブック」を参照してみます。

「数式埋め込み」は別ブックを「閉じた状態」でも参照できるので便利です。

「ブック名」を入力して別ブックを参照

「埋め込み数式」を使う場合は「ブック名」までを入力して別ブックを参照します。

「[TEST.xlsx]Sheet1!$A$2:$B$10」という感じです。

Sub TEST5()
    
    '数式埋め込みでVLookup関数を使って、別ブックを参照
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A2") = "=VLOOKUP(""D"",[TEST.xlsx]Sheet1!$A$2:$B$10,2,FALSE)"
        .Range("A2").Value = .Range("A2").Value '値に変換
    End With
    
End Sub

別ブックを開いておきます。

別ブックを開いておく

別ブックを開いておく

では、VBAコードを実行してみます。

別ブックを参照

別ブックを参照

「埋め込み数式」で別ブックを参照できました。

VLookup関数をセルで使うときと同じですね。

セルに数式を入力してコピーして使う

「埋め込み数式」を使う場合は、セルに数式を入力して「コピー」して使うと簡単に使えます。

数式をコピーして使うと簡単

数式をコピーして使うと簡単

この数式をコピーして使います。

文字列を入力する必要があるので、手入力すると大変ですからね。

別ブックを閉じたまま参照できる

「埋め込み数式」を使うメリットは、別ブックを「閉じたまま」参照できるということです。

ポイントは、参照するセル範囲をフルパスで入力するということです。

こんな感じ⇒「'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10」ですね。

Sub TEST6()
    
    '数式埋め込みで、別ブックを「フルパス」で参照
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"
        .Range("A2").Value = .Range("A2").Value '値に変換
    End With
    
End Sub

では、別ブックを閉じた状態で実行してみます。

別ブックを閉じたまま参照できる

別ブックを閉じたまま参照できる

別ブックを閉じたまま参照できました。

「フルパスの数式」を作成する手順を簡単に解説します。

フルパスの数式を作成する手順

別ブックは、同じフォルダに保存しています。

同じフォルダに別ブックを保存している

別ブックを開いた状態でセルに数式を入力します。

問題なくVLookup関数で参照できたら、別ブックを閉じます。

別ブックを開いた状態でセルに数式を入力して別ブックを閉じる

別ブックを閉じると自動的に、セル範囲が「フルパス」になります。

セルのアドレスがフルパスになる

フルパスになった数式をコピーして、VBAコードにコピーです。

これで、別ブックを閉じたままで参照できます。

別ブックを閉じた状態で参照できる

こんな感じで、別ブックを閉じたままで参照できます。

別ブックを閉じた状態で参照すると高速

別ブックを閉じた状態で参照したいのは、「高速」だからです。

ちょっとためしてみます。

別ブックを開いて参照

別ブックを開いて参照するのを単純に「10回」繰り返してみます。

Sub TEST7()
    
    t = Timer
    
    '10回ループする
    For i = 1 To 10
        
        '別ブックを開く
        Workbooks.Open "C:\TEST\TEST.xlsx"
        
        '別ブックを、VLookup関数で参照
        With Workbooks("TEST.xlsx").Worksheets("Sheet1")
            ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
        End With
        
        '別ブックを閉じる
        Workbooks("TEST.xlsx").Close
        
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

VBAコードを実行してみます。

別ブックを開いて参照

10回だけ別ブックを開いて参照しました。

かかった時間は、「8.98 秒」です。

ちょっと遅いかなという感じです。

別ブックを閉じたまま参照

次は、別ブックを「閉じたまま」参照するのを「10回」繰り返してみます。

Sub TEST8()
    
    t = Timer
    
    '10回ループする
    For i = 1 To 10
        
        '数式埋め込みで、別ブックを「フルパス」で参照
        With ThisWorkbook.Worksheets("Sheet1")
            .Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"
            .Range("A2").Value = .Range("A2").Value '値に変換
        End With
        
    Next
    
    Debug.Print Timer - t & " 秒"
    
End Sub

では、VBAコードを実行してみます。

別ブックを閉じたまま参照

別ブックを閉じたまま10回だけ参照しました。

かかった時間は、「0.13 秒」です。

90倍ぐらい速くなりました。

「埋め込み数式」でVLookup関数を使うという方法も覚えておいて損はないです。

おわりに

この記事では、VBAで「VLookup関数」を使って「別ブック」を参照する方法についてご紹介しました。

「別ブック」を参照するには「Workbooks」を使います。

「VLookup関数」をVBAで使うには「WorksheetFunction」と「数式埋め込み」を使う方法があります。

「WorksheetFunction」を使う場合は別ブックを「開いておく」必要があります。

「数式埋め込み」を使えば別ブックを「閉じたまま」参照することができます。

別ブックを「高速」で参照したいに場合に使えます。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す