WorksheetFunction.Index関数
ExcelVBAのIndex関数は、範囲と行番号、列番号を指定するとそこにある値を返してくれます。基本的な使い方は以下の通りです。
値 = WorksheetFunction.Index(範囲,行番号,列番号)
行番号、列番号は1から始まる数で指定します。
今回は以下のようなシート上で動作を確かめます。
基本的な使い方
Debug.Print WorksheetFunction.Index(Range("B2:C6"), 1, 1) このコードでは、”B2:C6“の一行目、一列の値を返しますので「1」が返ります。
引数は、範囲、行、列の順番で指定しています。
引数を省略できるケース
もし範囲が一行または一列しかない場合は、3番目の引数を省略できます。
Debug.Print WorksheetFunction.Index(Range("B2:B6"), 2)”B2:B6″は一列なので、第三引数を省略しています。第二引数で2番目を指定して「2」が返ります。
同様に一行の場合も3番目の引数を省略してOKです。この場合は列数の指定になります。
Debug.Print WorksheetFunction.Index(Range("E2:I2"), 2)この場合は、２列目の要素である「22」が返ります。
範囲が複数ある場合
Index関数は、範囲が複数の場合にも対応しています。4番目の引数として何個目の範囲かを指定します。この場合は、前述の引数省略はできません。
Debug.Print WorksheetFunction.Index(Range("B2:C6,E2:I3"), 1, 1, 2) 複数の範囲は、B2:C6,E2:I3 のように指定します。
第４引数が２なので、E2:I3 の範囲。１行目１列なので、「21」が返ります。
二次配列の切り出しに使う
Index関数は、二次配列を一次配列に切り出すときにも使えます。
サンプルとして”B2:C6“の値を取り込み、横方向と縦方向で一次配列として切り出してみます。
行を取り込む
Sub 二次配列から一次配列_Row()
Dim arr2, arr1
arr2 = Range("B2:C6")
arr1 = WorksheetFunction.Index(arr2, 2) '2行目を一次配列に
Debug.Print arr1(1)
Stop
End Sub こうすることで、2行目（横方向）の値を一次配列として読み込んでくれます。
添え字は1からスタートします。Stopで止まるのでデバッガでどのような値が格納されるか確認できます。
以下のような感じで取り込んでいます。
列を取り込む
縦方向（列）で切り出すには、Transpose関数で列・行を変換させないと上手くいきません。その後、Index関数で切り出したい行を指定します。
Sub 二次配列から一次配列_Col()
Dim arr2, arr1
arr2 = Range("B2:C6")
arr2 = WorksheetFunction.Transpose(arr2） '行と列を入れ替え
arr1 = WorksheetFunction.Index(arr2, 2) '2行目を一次配列に
Debug.Print arr1(1)
Stop
End Sub縦方向（列）で切り出すには、Transposeが必要です。これで列が行に変換されますので、Index関数で切り出したい行を指定します。
切り出したイメージは以下のようになります。（プログラムとしては、行と列を入れ替えて行方向に切り出しています。）
ちなみに、
arr1 = WorksheetFunction.Index(arr2, 2)は、
arr1 = WorksheetFunction.Index(arr2, 2, 0)としてもOKで、0は列全体を指定したのと同じです。
実験
では、Transposeを使わずに、
arr1 = WorksheetFunction.Index(arr2, 0, 1)
として、直接一列目を切り出すことは可能でしょうか？
実験してみます。
Sub 実験_二次配列から一次配列_Row()
Dim arr2, arr1
arr2 = Range("B2:C6")
arr1 = WorksheetFunction.Index(arr2, 0, 1)
Debug.Print arr1(2, 1)
Stop
End Sub特にエラーとはならず、一列分を切り出していますが二次配列のような構造になっています。
これですと、arr1(2, 1) という形で値にアクセスすることになってしまいますので扱いづらいですね。という事で、Transposeが必要になります。
二次配列を一次配列にする場面としては、CurrentRegion を使って配列を得る場合が想定されます。VBAでは列方向に切り出す時は、Transpose が必要になると覚えておきましょう。
