セル範囲を配列に、配列の要素をセル範囲に出力する処理は、配列を使う上で必ず行いますよね。この記事では、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行で配列に格納できました。「え？これで格納出来てるの？」という声が聞こえて来そうなくらい簡単ですね。
配列の要素にアクセスしてみます。
要素「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型に変更し、実行してみると、コンパイルエラーが発生し、「配列がありません」と表示されます。
配列からセル範囲への出力は、格納と逆の操作をするだけ
配列からセル範囲に出力する方法については、「バリアント型配列＝セル範囲」という考え方で出来ました。セル範囲への出力は単純に格納の逆で、「セル範囲＝バリアント型配列」とすれば良いので、ループ処理は不要になります。
出力先のセル範囲のサイズを間違えるとエラーになる
セル範囲に配列からデータを転記するには、「セル範囲＝バリアント型配列」にすれば良いところ、セル範囲の設定を間違えた場合、実行時エラーが発生します。
具体的には、出力先のセル範囲のサイズ＜バリアント型配列のサイズとしてしまった場合です。
例えばこのデータに対して
次のようなコードを書いてしまった場合
Sub セル範囲のサイズを間違えた場合()
Dim MyArray As Variant
MyArray = Range("A1:B8")
Range("C1:D10") = MyArray'転記先のセル範囲を、配列より大きくしてしまった
End Sub実行結果
配列のサイズを超えた部分がエラーになりました。
エラーを防ぐために（間違えたコードを書かないために）便利なのが、Resizeです。
Resizeの挙動
Resizeを使えば、起点セルから指定した行数、列数のセル範囲を指定することが出来ます。
Resizeの使い方
- 起点セルは、Resizeの引数の起点になるセルです。このセルを基準に、範囲を拡張します。
- 起点セルを左上にした、行数第１引数、列数第２引数のセル範囲を返します。
引数にUboundを使うと範囲指定が楽になる
Resizeは引数に行数、列数を動的に指定出来ますので、出力先のセル範囲＝配列のサイズが指定が楽になります。
静的配列の場合は配列のサイズは宣言以降変わることはありませんが、動的配列の場合は、その都度サイズが変わる可能性があるので、「書き換えるコードを間違えた！」ということがなくなるので、特に有効です。
動的配列についてはこちら
AB列のデータを、CD列に転記する例を考えます
コード
Sub ResizeにUboundを使う()
Dim MyArray As Variant
MyArray = Range("A1:B8")
Range("C1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray
End Sub実行結果
Uboundの詳しい挙動については、こちらを参考にしてください
まとめ
今回ご紹介した方法を使えば、ループ処理を記述する必要がなくなり、効率的なコーディングが可能になります。
また、配列を経由することで、セルへのアクセスを最小限にし、実行速度の向上します。コーディング改善＋実行速度の向上の相乗効果で、ますますあなたのVBAは効率的になっていきます。
コメント