Sub TEST1()
Dim A
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
Dim B
'配列に格納
B = Range("A2:C7")
'辞書に登録
For i = 1 To UBound(B)
A.Add B(i, 1), i
Next
Dim C
C = A(Range("E2").Value) '何番目かを取得
Range("F2") = B(C, 2) '価格を取得
Range("G2") = B(C, 3) '数量を取得
End Sub
「B」を検索して、価格と数量を取得してみます。
「B」を検索して複数の項目を取得する
順を追って、実行する手順をみてみます。
では、VBAコードを実行してみます。
手順
最初に、値を配列に格納します。
アイテムは連番として、辞書にキーを登録していきます。
辞書で「B」を検索して、「2」を取得して、配列の2番目の値を取得します。
これで、辞書を検索して、複数アイテムを取得できます。
複数アイテムを取得できた
辞書を検索して、複数アイテムを取得できました。
Dictionaryを使えば高速化できる
Dictionaryを使うメリットは、何といってもVBAの処理を高速化できることになります。
どれぐらい高速にできるかをみてみます。
結論から言うと、ForとIfを使う方法と比較して、「100倍」ぐらい速くすることができます。
では、実際にやってみます。
ForとIfと配列を使って計測
次のように大量のデータを用意しました。
元データが17,577行あって、検索は、1000行分を検索してみます。
大量データを用意
まずは、FotとIfと配列を使って、計測してみます。
ForとIfと配列を使って、一致する値を取得
ForとIfと配列を使って、一致した値を取得するVBAコードになります。
Sub TEST2()
t = Timer
Dim A, B
A = Range("E2:G1001") '検索する値を配列に格納
B = Range("A2:C17577") 'データベースを配列に格納
For i = 1 To UBound(A)
For j = 1 To UBound(B)
'値が一致した場合
If A(i, 1) = B(j, 1) Then
A(i, 2) = B(j, 2) '価格を取得
A(i, 3) = B(j, 3) '数量を取得
End If
Next
Next
'配列をセルに入力
Range("E2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
Sub TEST3()
t = Timer
Dim A, B, C
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
B = Range("A2:C17577") 'データベースを配列に格納
C = Range("E2:G1001") '検索する値を配列に格納'辞書に登録
For i = 1 To UBound(B)
A.Add B(i, 1), i
Next
Dim D
For i = 1 To UBound(C)
D = A(C(i, 1)) '何番目かを取得
C(i, 2) = B(D, 2) '価格を取得
C(i, 3) = B(D, 3) '数量を取得
Next
'配列をセルに入力
Range("E2").Resize(UBound(C, 1), UBound(C, 2)) = C
Debug.Print Timer - t & " 秒"
End Sub
