Office TANAKA - Excel VBA Tips[連想配列を使う]

プログラミングの仕組みに連想配列という機能があります。たとえば、

Japan	日本
USA	アメリカ
France	フランス

のようにデータを格納しておいて、「Japan」というキーで「日本」を探し出す機能です。
「VBAで連想配列は使えますか？」とか「VBAには連想配列がないからな～」などという話をたまに聞きますが、Excel VBA使いとしては「？？」という気がしてなりません。だって、いつもワークシート上で普通にやってることですから。

Excel VBAに連想配列という機能はありませんが、データを変数ではなくワークシート上のセルに格納することで、連想配列のような使い方ができます。ちなみに、かなり高速ですよ。20,000件のデータをランダムに並べ、20,000件目のデータを検索するのに要した時間は20ミリ秒でした。CPUは1GHzでメモリは512MBの環境です。

セルを連想配列のように使うコードは、サンプルブックをご覧ください。一応、検索するだけでなく、存在を確認したり、新しいデータを追加したり、既存データを削除する関数なども書いておきました。ひとつだけマクロ例を提示しておきますね。

Sub Sample1()
    ''データの検索
    Dim buf As String
    buf = InputBox("キーは？")
    If buf = "" Then Exit Sub
    If CheckData(buf) <> "" Then    ''存在チェック
        MsgBox GetData(buf)
    Else
        MsgBox buf & "は存在しません"
    End If
End Sub

Function GetData(htKey As String)   ''既存データの検索
    Dim ST
    ST = GetTickCount
    On Error Resume Next
    GetData = Application.WorksheetFunction.VLookup(htKey, Sheets(1).Range("A1:B20000"), 2, False)
End Function

Function CheckData(htKey As String) ''存在確認
    On Error Resume Next
    CheckData = Application.WorksheetFunction.Match(htKey, Sheets(1).Range("A1:A20000"), 0)
End Function

GetDataとかCheckDataが、セルを連想配列のように使う関数です。サンプルブックでは、確認のためにGetDataだけミリ秒単位の時間を計測するようにしています。

関数側で受け取る引数の整合性などはチェックしていません。また、データの件数は20,000件で固定しています。こんなのは、運用で簡単にカバーできる部分ですから、あえて書いていません。参照するセル範囲の大きさを可変にするなんて、VBAでは初歩的なテクニックですからね。

サンプルブックのダウンロード「sample52.xls(1,511,936byte)」
リンクを右クリックして[ファイルに保存する]などしてください。
なお、サイズが1MBを超えていますので、同じものを圧縮したファイルも用意しておきます。
圧縮ファイルのダウンロード「sample52.lzh(16,037byte)」
内容は、上のsample52.xlsと同じファイルです。