きなこSHOW

VBAと日々の戯言

Excel VBAで指定した範囲の値を配列に格納するサンプル

スポンサーリンク

セル範囲に入力されたデータを配列に格納するテクニックをご存知でしょうか。
VBA処理の高速化に役立つ技で、頻繁に使われています。
今回そいつを汎用的に使えるように関数化しました。
コピーしてそのまま使えるサンプルプログラムを公開します。

なぜ配列化?

セル範囲を配列に格納するテクニックは、多くのサイトで説明されています。
VBAの権威、ゴッドオブVBAの田中先生も仰ってますが、これ究極の方法です。

Office TANAKA - Excel VBA高速化テクニック[セルを配列に入れる]

何が良いかって、Excel Sheetから取得と貼り付けるだけなら各要素をループする必要がなく、個々のセルでの評価も発生しないため、高速処理が可能となるのです。

セルを配列に入れるサンプルコード

セル範囲のデータを配列に格納するにはまず、セルの範囲を特定してやる必要があります。
処理対象のセル範囲が常に一定であれば、Rangeプロパティを使用した次の構文で大丈夫です。

Dim vTestArray As Variant
vTestArray = Worksheets("Test").Range("A3:Z5000")

しかし実際の業務では、行数や時には列数が一定ではない場合も多くあることでしょう。
Rangeだけでは行・列を可変とすることはできないので、上の構文は使えません。
セル範囲の左上端と右下端を数値で指定する場合は、Cellsプロパティを使用して以下のように記述します。

Dim vTestArray As Variant
With Worksheets("Test")
     vTestArray = .Range(.Cells(先頭行, 先頭列), .Cells(最終行, 最終列))
End With

結局毎回これを繰り返し書いているので、私はこれを部品化して使いまわしています。
それが以下のプロシージャです。

しつこいかと思いますが、サンプルコードの使用はすべて自己責任でお願いいたしますね。

エラーは事前に必ずとらえて後続の処理に進ませないためにチェックはしつこくしています。
チェック部分が要らない場合は適宜削除してください。
まぁ、チェック部分をすべて削除してしまうと、RangeをVariantに格納するだけになるんで、関数化する必要もないんですけどね。

呼び出し例

この関数の使い方をサンプルでご紹介します。
例えばこんな表があったとして、

サンプルデータ
この表のデータを配列に格納するには次のように記述します。

ExcelのRangeオブジェクトをVariant型の変数に代入すると、二次元配列の形式で格納されます。扱い方が手軽な上に、処理も高速です。
行数や列数が可変の場合は、範囲を取得するために最終行と最終列を特定する必要がありますので、前回紹介した関数を呼び出しています。
行数や列数が固定の場合は固定値を与えてやればよいので、サブプロシージャの呼び出しは省略可能です。
返された配列の最小の要素は行・列ともにゼロなんだけど、Excelの行番号、列番号に合わせて要素番号1から値が入ってきますので、そこだけは注意が必要です。

上のサンプルコードで表示された結果が以下。

結果

このサンプルではイミディエイトウィンドウに表示しています。
もうひとつの短いサンプルの方は、Variant変数に格納されている配列をExcel Sheetのセル範囲に代入する例です。
たしか、左上端の1セルだけ指定しても配列のすべての値が一括で貼り付けできますが、貼り付け範囲を正確にコントロールしたければ、貼り付け先の行数、列数を指定した方が良いでしょう。
多くの用途で使えるテクニックなので、業務や用途に合わせて使ってみてください。
これまでご紹介したデータの最終行、最終列の取得と、今日紹介した範囲配列取得関数の合わせ技で、簡単楽チンにExcelデータを取得&貼り付けできるサンプルコードでした。





使ってる中で不具合や対応しきれてなかったバグが見つかったら、その都度直していきますね。
そんでは!