きなこSHOW

寝ながらあれこれ考える

Excel VBAで指定した範囲の値を配列に格納する関数を作ってみました

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

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

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

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

セル範囲のデータを配列に格納するにはまず、セルの範囲を特定してやる必要があります。
処理対象のセル範囲が常に一定であれば、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に格納するだけになるんで、関数化する必要もないんですけどね。
今回も掲載の都合上「On Error ~」の「E」を全角にしてありますので、使うときはお手数ですが半角に直してくださいね。※この問題、解決しました!

呼び出し例

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

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

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

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

結果

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





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

※↓この方の記事を読んで、はてなキーワードリンクを外す方法を知りました。
激しく感謝しております!


はてなブログでSyntaxHighlighterを利用する時は「data-unlink」をお忘れなく - altere5's blog