Excel VBAのVLOOKUP関数の処理時間を高速化する方法

ExcelVBAではVLOOKUP関数が利用できますが、Application.WorksheetFunction.VLookupは参照範囲が大きくなると処理速度が落ちてしまうのが難点です。

この記事では、Excel VBAでVLOOKUPの処理時間を高速化する方法について解説していきます。

この記事の内容

1 処理時間の検証用プログラム
2 方法1:ワークシート関数を使う
3 方法2:VLOOKUP関数の数式を直接設定する
4 方法3：Dictionaryを使う
5 まとめ

処理時間の検証用プログラム

10368行の参照範囲を検索する3つのVBAプログラムを用意して、処理時間を比較してみます。

方法1:ワークシート関数を使う

まずは、VBAでVLOOKUP関数を呼び出すApplication.WorksheetFunction.VLookupを使った方法で実行してみます。

*Application.WorksheetFunction.VLookupの使い方は以下の記事にも記載しています。

startTime, endTimeではTimer関数を設定してプログラム実行時間を測定できるようにしています。コードのサンプルは次の通りです。

Sub method_1()

    Dim LookupArray As Variant

    Dim MaxRow      As Long

    Dim i           As Long

    Dim SearchKey       As String

    '時間計測用

    Dim startTime As Double

    Dim endTime As Double

    Dim processTime As Double

　

    startTime = Timer '開始

　

    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得

    LookupArray = Range(Cells(3, 4), Cells(MaxRow, 5)) '出力用のセルを配列として格納

　

    '検索

    For i = 1 To UBound(LookupArray)

　

        SearchKey = LookupArray(i, 1) '検索キーを格納

　

        LookupArray(i, 2) = _

        Application.WorksheetFunction.VLookup(SearchKey, Range(Cells(3, 1), Cells(MaxRow, 2)), 2, False)

　

    Next i

　

    '結果出力

    Range(Cells(3, 4), Cells(MaxRow, 5)) = LookupArray

　

    endTime = Timer

    Cells(3, 8) = endTime - startTime

　

End Sub

計測結果は次のとおり、約43秒で処理が完了しました。

待てない程の処理時間ではないですが、VBAでなくExcelで式をコピーした方が速い気がしますね。

方法2:VLOOKUP関数の数式を直接設定する

検索結果用のセルに、以下のように記述してVBAで直接VLOOKUP関数の数式を設定する方法です。

LookupArray(i, 2) = "=VLOOKUP(D" & i + 2 & ",$A$3:$B$10370,2,FALSE)"

コードのサンプルです。

Sub method_2()

　

    Dim LookupArray As Variant

    Dim MaxRow      As Long

    Dim i           As Long

    Dim SearchKey       As String

    '時間計測用

    Dim startTime As Double

    Dim endTime As Double

    Dim processTime As Double

　

    startTime = Timer '開始

　

    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得

    LookupArray = Range(Cells(3, 4), Cells(MaxRow, 5)) '出力用のセルを配列として格納

　

    '検索

    For i = 1 To UBound(LookupArray)

　

        LookupArray(i, 2) = "=VLOOKUP(D" & i + 2 & ",$A$3:$B$10370,2,FALSE)"

　

    Next i

　

    '結果出力

    Range(Cells(3, 4), Cells(MaxRow, 5)) = LookupArray

　

    endTime = Timer

    Cells(3, 8) = endTime - startTime

　

End Sub

数式を直接設定した場合の処理時間は約1.8秒でした。

方法1と比較すると大幅に速度が改善していることがわかります。

方法3：Dictionaryを使う

方法3は、VLOOKUPを使わずにVBAのDictionary（連想配列）を使用して同じ処理を実現する方法です。キーと値をセットで格納します。

参照範囲をAddを用いて検索キー、値のセットで格納します。

辞書.Add「参照範囲の検索キー」, 「参照範囲の値」

格納された値を取り出す時は、検索キーを引数に入れます。

値 = 辞書(検索キー)

コードのサンプルは次の通りです。

Sub method_3()

　

    Dim LookupArray As Variant

    Dim RefArray    As Variant

    Dim KeyValue    As String

    Dim ItemValue   As Long

    Dim MaxRow      As Long

    Dim i           As Long

    Dim n           As Long

    Dim SearchKey   As String

    Dim Dictionary  As Object

    '時間計測用

    Dim startTime As Double

    Dim endTime As Double

    Dim processTime As Double

　

    startTime = Timer '開始

　

    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得

    LookupArray = Range(Cells(3, 4), Cells(MaxRow, 5)) '出力用のセルを配列として格納

    RefArray = Range(Cells(3, 1), Cells(MaxRow, 2)) '参照範囲のセルを配列として格納

　

    Set Dictionary = CreateObject("Scripting.Dictionary")

　

    '参照用の配列から辞書作成

    For n = 1 To UBound(RefArray)

　

        KeyValue = RefArray(n, 1)

        ItemValue = RefArray(n, 2)

　

        Dictionary.Add KeyValue, ItemValue

　

    Next n

　

    '辞書から検索

    For i = 1 To UBound(LookupArray)

　

        SearchKey = LookupArray(i, 1)

        LookupArray(i, 2) = Dictionary(SearchKey)

　

    Next i

　

    '結果出力

    Range(Cells(3, 4), Cells(MaxRow, 5)) = LookupArray

    Set Dictionary = Nothing '辞書は空にしておく

　

    endTime = Timer

    Cells(3, 8) = endTime - startTime

　

End Sub