今回は VBA のなかでも最も強力な機能の１つを解説します。それは、たった１行のコードで セル範囲を丸ごと２次元配列変数に放り込むというスペシャルな技です。まずは２次元配列変数について簡単に説明しておきます。
【VBA】2次元配列変数にテーブルデータを格納します
２次元配列変数とは次のように２つの変数で指定されるデータのことです。たとえばエクセルで地名、よみ（ひらがな）、よみ（ローマ字）が次のように並んでいると仮定します。今回使用するデータですので、必要な人はシートに貼り付けてテーブルに変換し、テーブル名を「地名」としてください（≫ 表をテーブルに変換する方法はこちらのページに載っています）。
| 地名 | よみ | ローマ字 |
|---|---|---|
| 京北宮 | けいほくみや | Keihokumiya |
| 御立中 | みたちなか | Mitachinaka |
| 水沢 | みずさわ | Mizusawa |
| 長良宮口 | ながらみやぐち | Nagaramiyaguchi |
| 古川新栄 | ふるかわしんさかえ | Furukawashinsakae |
このデータを読み込んで２次元配列変数に放り込むと、各データには次のような２つの番号が付されることになります。
| 地名 | よみ | ローマ字 |
|---|---|---|
| 京北宮 (1, 1) | けいほくみや (1, 2) | Keihokumiya (1, 3) |
| 御立中 (2, 1) | みたちなか (2, 2) | Mitachinaka (2, 3) |
| 水沢 (3, 1) | みずさわ (3, 2) | Mizusawa (3, 3) |
| 長良宮口 (4, 1) | ながらみやぐち (4, 2) | Nagaramiyaguchi (4, 3) |
| 古川新栄 (5, 1) | ふるかわしんさかえ (5, 2) | Furukawashinsakae (5, 3) |
要するに２次元配列変数は、普段見慣れているエクセルのワークシートのように、平面上に縦横に並んだデータをイメージすればよいのです。
ワークシートのテーブルデータを配列変数に放り込むのはとても簡単です。
'[VBA] テーブルデータを配列変数に格納するSubプロシージャ
Option Base 1
Sub Table_to_Array_1()
Dim mydata As Variant
'データを配列変数に入れます
mydata = ActiveSheet.ListObjects("地名").DataBodyRange
Debug.Print mydata(2, 2)
End Sub
たったこれだけです。まずモジュールの一番上に
Option Base 1
と書いておきます。これは通常は mydata(0,0) からふられる番号の起点を mydata(1,1) に直しておくものです。たぶんこのほうが分かりやすいし、ミスも少なくなります。配列変数 mydata は Variant 型で宣言し、
mydata = ActiveSheet.ListObjects("地名").DataBodyRange
という記述で、テーブルの見出しを除いたデータ部分を mydata に格納しています。
Debug.Print mydata(2, 2)
は mydata(2,2) に入っているデータをイミディエイトウィンドウに表示するコードで、実際に上のマクロを実行すると２行２列目にある「みたちなか」という文字が表示されるはずです。
【VBA】ローマ字だけを全部表示させるマクロ
先ほどのマクロを少し変えて、３列目のローマ字だけを表示させるマクロを書いてみますね。
'[VBA] ローマ字だけを表示するSubプロシージャ
Option Base 1
Sub Table_to_Array_2()
Dim mydata As Variant
Dim i As Integer, dmr As Integer
mydata = ActiveSheet.ListObjects("地名").DataBodyRange
'次元要素数を入れます
dmr = UBound(mydata, 1)
For i = 1 To dmr
Debug.Print mydata(i, 3)
Next i
End Sub
'実行結果：
'Keihokumiya
'Mitachinaka
'Mizusawa
'Nagaramiyaguchi
'Furukawashinsakae
今使っている練習用データは要素数が少ないので、行数が 5 であることはすぐにわかりますが、もっと大きなデータを扱うときのことを考えて、配列の上限値を求める UBound 関数を用いて、
dmr = UBound(mydata, 1)
として、mydata の「１つめの要素数」を変数 dmr に入れています。「１つめの要素数」というと何だか分かりにくいのですが、２次元変数 mydata (i,j) における i の上限値、すなわちワークシート上の「行数」を表しています。
データを配列変数に格納する最大のメリットは処理速度が大幅に向上するということです。特に For ステートメントなどで大量のデータをループさせる場合などには、ワークシート上のデータを扱うよりも、配列データを処理するほうが断然に速いのです。したがって VBA で大がかりなデータベースシステムを構築するときには必ずといってよいほどこの配列処理が用いられます。データが少ないうちはなかなか実感できませんけど、ご自身でマクロを作っているときに「どうも処理が遅いなあ」と感じたときは、配列を用いることを検討してみてくださいね。
コメント