セル範囲を配列に、配列の要素をセル範囲に出力する処理は、配列を使う上で必ず行いますよね。この記事では、A列、B列に入っているデータを、セル範囲ー配列間でやり取りする方法を解説します。
ループ処理だとコーディングの効率が悪い
一番最初に思いつくのは、以下のように、セルをループ処理して配列に要素を格納する方法です。
１．セル範囲に合わせた配列を用意する
２．セル範囲と、用意した配列をFor Nextでアクセスし、配列にセルのデータを格納していく。
ループ処理のイメージ
コード
Sub ループ処理で配列に格納()
Dim Myrange(1 To 8, 1 To 2) As String
Dim i As Long, r As Long
For i = 1 To 8
For r = 1 To 2
Myrange(i, r) = Cells(i, r)
Next
Next
End Sub二重のループ処理にならざるを得ず、コードの可読性が下がりますし、処理の回数も必要に応じて変更しなければならず、コードの使い回しがしにくいです。そこで、今回は別のアプローチをご紹介します。
「バリアント型変数 = セル範囲」で二次元配列になる。
ループ処理を使わず、セル範囲を配列に格納する方法は、以下のコードで可能になります。
コード
sub 範囲を配列に格納()
Dim Myrange As Variant
Myrange = Range("A1:B8")
End Sub処理のイメージ
たったの2行で配列に格納できました。「え？これで格納出来てるの？」という声が聞こえて来そうなくらい簡単ですね。
Debug.printを使って、配列の要素にアクセスしてみます。
要素「A1」を取り出せました。配列にセル範囲が格納されていることが確認できました。
御覧のとおりループ処理は不要で、定義した変数にセル範囲を代入するだけです。変数が二次元配列へと自動的に変換されるので、①セル範囲に対応した二次元配列を用意して、②ループ処理にかけるという行程を省くことが出来ました。
配列に格納する範囲を指定する方法は色々
セル範囲を指定する方法には、Range(セル範囲)とする方法以外に、以下の三つの方法が存在します。
- バリアント型変数＝Range（Cells（左上のセル番地）,Cells（右下のセル番地））
- バリアント型変数＝ 起点セル.CurrentRegion
- バリアント型変数＝シートオブジェクト.UsedRange
バリアント型変数＝Range（Cells（左上のセル番地）,Cells（右下のセル番地））で柔軟に範囲を指定する方法
コード
Myrange = Range(Cells(1,1),Cells(8,2)) 'Range("A1:B8")と同じ意味Rangeの引数にCellsを使いました。第一引数に、セル範囲の左上、第二引数にセル範囲の右下のセル番地を記述することで、セル範囲を確定させています。
イメージ
Cellsを使うことから、変数をセル番地に入れることが出来るため、柔軟にセル番地を指定することが出来ます。
バリアント型変数＝ 起点セル.CurrentRegionでコーディングを楽にする方法
コード
Myrange = Range("A1").CurrentRegion一番コーディングが楽です＝実行時エラーが少ない方法です。
バリアント型変数＝シートオブジェクト.UsedRangeで漏れなく範囲指定する方法
コード
Myrange = ActiveSheet.UsedRangeコーディングが楽で、なおかつセルのデータを漏れなく配列に格納することが出来ます。
データが存在するセルが飛び石になっている場合は、空白まで配列に格納することになることに注意です。
二つの注意点
バリアント型配列＝セル範囲で配列に格納する方法には、覚えておくべき特徴が２点あります。
１．配列のインデックス番号は１から始まる
２．変数の型はVariant型にする必要がある。
注意点１．配列のインデックス番号は１から始まる
Debug.printで確認したとおり、セル範囲の左上の要素を取り出すのに、（1,1）としました。OptionBaseを１とせずとも、インデックス番号が１から開始されています。
注意点２．変数の型はVariant型にする必要がある。
セル範囲を格納する変数の型はVariant型にしました。
ここで、変数の型をString型に変更し、実行してみると、コンパイルエラーが発生し、「配列がありません」と表示されます。
なお、Range型にしても同じくコンパイルエラーになります。
配列からセル範囲への出力は、格納と逆の操作をするだけ
配列からセル範囲に出力する方法については、「バリアント型配列＝セル範囲」という考え方で出来ました。セル範囲への出力は単純に格納の逆で、「セル範囲＝バリアント型配列」とすれば良いので、ループ処理は不要になります。
ただ、出力する場合には、いちいちセル範囲を指定しなくても済むよう、便利な方法があります。
出力先のセル範囲のサイズを間違えるとエラーになる
セル範囲に配列からデータを転記するには、「セル範囲＝バリアント型配列」にすれば良いところ、セル範囲の設定を間違えた場合、実行時エラーが発生します。
具体的には、出力先のセル範囲のサイズ＜バリアント型配列のサイズとしてしまった場合です。
例えばこのデータに対して
次のようなコードを書いてしまった場合
Sub セル範囲のサイズを間違えた場合()
Dim MyArray As Variant
MyArray = Range("A1:B8")
Range("C1:D10") = MyArray'転記先のセル範囲を、配列より大きくしてしまった
End Sub実行結果
配列のサイズを超えた部分がエラーになりました。
エラーを防ぐために（間違えたコードを書かないために）便利なのが、Resizeです。
Resizeの挙動
Resizeを使えば、起点セルから指定した行数、列数のセル範囲を指定することが出来ます。
Resizeの使い方
- 起点セルは、Resizeの引数の起点になるセルです。このセルを基準に、範囲を拡張します。
- 起点セルを左上にした、行数第１引数、列数第２引数のセル範囲を返します。
引数にUboundを使うと範囲指定が楽になる
Resizeは引数に行数、列数を指定しますので、二次元配列の行数、列数を与えれば、出力先のセル範囲＝配列のサイズが指定出来ます。
AB列にデータ
コード
Sub ResizeにUboundを使う()
Dim MyArray As Variant
MyArray = Range("A1:B8")
Range("C1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray
End Sub実行結果
Uboundの詳しい挙動については、こちらを参考にしてください
コメント