Excel VBA事始めその4 配列前編

ExcelのVBAでは当然だけど配列が使える。そしてExcelのワークシートが表であるということは配列とは親和性が高いのでよく使うことになる。
ただし、ワークシートの範囲に配列を入れる、或いはワークシートの範囲を配列にするというのは基本的には2次元配列を使うことになる。
1次元配列は配列をワークシートの範囲に入れる際には利用可能だが逆はダメ、そして1行の範囲には入れることができるが1列の範囲には入れることができないというか、入れようとするとエラーにはならないが意図した動作にはならないので厄介。

配列について 1
(1次元)配列は、数を事前に決めて直線に並べた箱と考える。これは言語関係ないと思う。箱には0番からの番号が振られ、それぞれの箱に何らかの値を入れることができる。大抵の言語では配列の要素は0番からということになっているが、1からということもできるしExcel VBAでは意図せず1番からになることもにあってそれが困る。

配列について 2
配列を宣言するときは、要素の数(箱の数)を宣言する。要素数を宣言する際は、(要素は0から始まるので)指定したい数より1小さい数値にする。(Option Base 1を指定していない場合)
最初の要素を1から指定したいという場合はコードの最初(プロシージャ外)にOption Base 1を指定するか「1 to 指定したい数」のように指定する。なお、Option Baseは0か1しか指定できない。

配列について 3
2次元配列では箱が面方向に並ぶと考える。2次元配列の要素をExcelのワークシートのセルに例えると「(行,列)」のようになる。要素は0からは始まるので2次元配列の最初の要素は(0,0)になり、要素数を指定して宣言する際は「(指定したい行数 -1, 指定したい列数 -1)」のように指定する。
最初の要素を1から指定したいという場合はコードの最初(プロシージャ外)にOption Base 1を指定するか「(1 to 指定したい行数, 1 to 指定したい列数」のように指定する。

Sub test1()

    With ThisWorkbook.Sheets(1)

        '列 (これはNG)
        .Range("B2:B6").Value = Array("B2", "B3", "B4", "B5", "B6")

        '行
        .Range("D2:H2").Value = Array("D2", "E2", "F2", "G2", "H2")

    End With

End Sub



Sub test2()

    '二次元配列を作る
    Dim arrTest(4, 0) As String

    arrTest(0, 0) = "B11"
    arrTest(1, 0) = "B12"
    arrTest(2, 0) = "B13"
    arrTest(3, 0) = "B14"
    arrTest(4, 0) = "B15"

    '列 (これはOK)
     ThisWorkbook.Sheets(1).Range("B11:B15").Value = arrTest

End Sub

VBA4 配列 2
test1()を実行すると、オレンジと緑の枠に値が入る。これは要素5個の1次元配列を使った場合。
test2()を実行すると、青の枠に値が入る。これは1列5行の要素の2次元配列を使った場合。

上の例のように2次元配列をワークシートに入れる際には普通に宣言した配列(0番から始まる要素)を普通に使えるが、ワークシートから2次元配列に入れる場合は配列の宣言で(上の例の Dim arrTest(4, 0) のような)普通に0からの要素数を定義するのではなくarrTest(1 to 5, 1 to 1)のように要素が1から始まる指定を行う方が意図しない不具合は起きにくい。Excelの場合は何故か配列の要素の開始番号が0だったり1だったりワケわからんことになるので注意した方が良さそうです。