スポンサーリンク
1次元配列と2次元配列
前回までの記事で、Excel VBAの配列の中で、1次元配列と呼ばれるものを扱った。
今回から、2次元配列を扱う。
1次元配列では
arr(100)
みたいに、カッコ内に数字を1つ入れたが、2次元配列では
arr(100 , 10)
みたいに、カンマ,で区切って2つの数字を入れる。3次元配列なら3つになるが、まあそれは使わないだろう。
さて、2次元配列にも基礎の説明は色々とあるにはあるが、2次元配列の最大の使い道は、Excelのセルとデータをやり取りすることだ。
面倒な説明は後にして、まずはその最大の使い道からサンプルで見てみる。
2次元配列でセルの値を取得・転記するサンプル
では、2次元配列を用いて、
「元データ」シートのA1セルから縦100行・横5列の値を、「転記」シートのA1セルに転記する
という簡単な処理をマクロに書いてみる。
|
1 2 3 4 5 6 7 8 9 10 |
Sub test() '「元データ」シートのA1セルから縦100行・横5列の値を '2次元配列を使って「転記」シートに転記する Dim arr As Variant 'まずセルの値を、2次元配列に代入 arr = Worksheets("元データ").Cells(1, 1).Resize(100, 5).Value '次は逆に、2次元配列の値をセルに代入 Worksheets("転記").Cells(1, 1).Resize(100, 5).Value = arr End Sub |
と、こういう書き方をすることで、自動的にarrは2次元配列として生成される。
この場合は、指定したセル範囲100×5の内容を全て含む2次元配列だ。
そして、2次元配列の中身をセルに書き込むのは、2次元配列arrを右辺にセットして
で良い。
これらの、セルと2次元配列とを相互にやり取りする方法は、VBAをやるなら必ず押さえよう。
面倒でもいったん2次元配列に格納する
この処理を、2次元配列を使わずにやるなら
の1行で完全に書けるには書ける。
それなのにソースコードでは、わざわざセルの値を2次元配列に格納してもう一度そこからセルに書き込むという面倒なことをやっているが、これは必要なことだ。
セルの値のやり取りは、2次元配列を介してやれば、何十倍にも早くなる。
私もどんなに面倒でも、限界ギリギリまでこの2次元配列を介する方法で処理するようにしている。
私が実際の業務において作るマクロも、今回サンプルで示したソースコードの延長的なマクロが大半だ。
途中で色々と計算などするにはするが、まずセルの値を2次元配列に代入し、転記用のシートにもう一回2次元配列から代入するという大枠の考え方は同じだということだ。
セルが単一の場合は注意
セルの値は2次元配列に格納できるわけだが、↓のように対象が単一セルだった場合は、2次元配列としてではなく単なるVariant変数として格納されることになる。
|
1 2 3 |
Dim arr as Variant '配列としては格納されず、単なるVariant変数arrとして格納される arr = Cells(3, 3).Value |
通常ならこういうことで間違うことも少ないだろうが、セル範囲を変数で扱う場合などは、状況次第でセル範囲が単一セルになってしまうことがある。
そういう場合に、2次元配列として生成されるつもりだったのが単なるVariant変数になると上手く行かないことがある。
2次元配列なら、その中の要素を
arr(r,c)
などと変数により取得できるが、単なるVariant変数だとそうはいかずarrという単一変数としてしか取り扱えないからだ。
状況次第でセル範囲が単一セルになり得るとかいう場合は、それだけを場合分けするなどして、2次元配列への格納には注意を払うようにしよう。
2次元配列にはセルの値や数式を格納できる
今回の例
で2次元配列に格納したのは、あくまでセルの値のみであって、フォントとかセル背景色とかいった諸プロパティは含まれない。
Valueを省略して
といった書き方をする例もたまに見かけるが、私としてはそれはお勧めしない。
どうもこういう書き方では、セルの何を2次元配列に格納しているのか不明確な感じで、値だけを格納していると強調するためにValueを書いておきたい。
ちなみにFormulaとかFormulaR1C1などを使って、セルの値だけでなく数式を2次元配列に格納することも、一応できる。
といった感じだ。
Formulaなら普通の
=A1+B1
といった式で、FormulaR1C1なら
=R1C1+R1C2
といったR1C1形式の数式が格納される。
ただ、この数式を2次元配列に格納する方法は、マクロの高速化効果は薄いと思う。
私がやってみたところ、セルのコピー→数式のみ貼り付けとした方がかえって処理が速いケースが多かった。
まあ、セルのコピーとかがスマートさに欠けるのでやりたくないとかいう場合は、2次元配列に数式を格納する方法も良いかとは思う。
セルの値から代入した2次元配列の性質
さて、配列の添字というものについて、通常は添字は0から始まるものだと何度か述べた。
これは2次元配列でも例外ではない。
しかし、セルの値から代入した2次元配列の添字は、必ず1から始まる。
これは非常に大事な性質だ。
つまり
として生成した2次元配列arrは、arr(1,1)から始まってarr(100,5)まで続くということだ。
例として、上図のA1:B5セルの値を2次元配列に格納するマクロを書くと、こんな感じになる。
|
1 2 3 4 5 6 7 8 9 10 11 |
Option Explicit Sub test() Dim arr As Variant 'A1:B5のセル範囲を2次元配列に格納する arr = Cells(1, 1).Resize(5, 2).Value 'arr(3, 2)がC2セルに該当するので '「C2」がイミディエイトウィンドウに出力される Debug.Print arr(3, 2) End Sub |
その結果生成された2次元配列arrの中身をローカルウィンドウで覗くと、下図のようになる。
添字が1から始まって、5×2の2次元から成る配列になっているのが分かるだろう。
配列の添字は無理してでも1開始に統一すべきと私は強調するが、前回述べたようにSplit関数などは強制的に添え字が0開始になり、セルの値から代入した2次元配列の添字は1開始になる。
幾つもの配列用変数を同時に扱う中で、こんな性質が違うものが混在していては必ず間違いに繋がる。
だから、添字を1開始に統一する癖は必ず付けておこう。
Cells記法とResizeメソッドが2次元配列と親和性が高い
Excelのセル範囲をVBAで表すには、記法が何通りかあって、人によって好みも分かれる。
Cells(1,1)
Range(Cells(1,1),Cells(100, 5))
Cells(1,1).Resize(100, 5)
といった感じだ。
そして私は専ら、赤の太字にした、CellsとResizeを使う方法ばかりを採用している。
その理由の1つが、2次元配列とのやり取りにおいて親和性があり、分かりやすいからだ。
まずCells(1,1)という記法は、Range(“A1”)記法と違い、行も列も数字で扱えるので、変数などにもはめやすくて分かりやすい。
そしてResizeメソッドは、要はセル範囲を数値指定して文字通りリサイズするものだ。
そしてこれを使えば、セル範囲を2次元配列のイメージそのままに扱うことができる。
というようにすれば、配列arrも
arr(1,1)から始まってarr(100,5)まで
の、Resizeメソッドで表現したそのままの2次元配列として生成されるのが便利なのだ。
スポンサーリンク
