ExcelのRangeオブジェクトをVariant型の変数に代入すると、二次元配列の形式で格納されます。
扱い方が手軽な上に、処理も高速です。
今回そいつを汎用的に使えるように関数化しました。
コピーしてそのまま使えるサンプルプログラムを公開します。
なぜ配列化?
セル範囲を配列に格納するテクニックは、多くのサイトで説明されています。
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
部品化したのが以下のプロシージャです。
Public Function FncGetDataArray(ByVal objSheet As Worksheet, _
ByVal RowHead As Long, _
ByRef ArrRange As Variant, _
Optional ByRef Msg As String = "") As Boolean
On Error GoTo Err_Sec
Dim bRet As Boolean
Dim sMsg As String
Dim lColMax As Long
Dim lRowMax As Long
Dim lWork As Long
Dim i As Long
'--- 初期値セット ---
bRet = False
sMsg = ""
lColMax = 0
lRowMax = 0
With objSheet
'--- 見出し行の最大列数を取得 ---
lColMax = .Cells(RowHead, .Columns.Count).End(xlToLeft).Column
'--- 最大行を取得 ---
For i = 1 To lColMax
lWork = .Cells(.Rows.Count, i).End(xlUp).Row
If lRowMax < lWork Then
lRowMax = lWork
End If
Next
'--- 配列に格納する ---
ArrRange = .Range(.Cells(1, 1), .Cells(lRowMax, lColMax)).Value
End With
bRet = IsArray(ArrRange)
Exit_Sec:
Msg = sMsg
FncGetDataArray = bRet
Exit Function
Err_Sec:
sMsg = "エラー番号:" & Err.Number & " エラー内容:" & Err.Description
Resume Exit_Sec
End Function
第1引数「objSheet」・・・配列に格納したい値が入力されているシートを指定します。
第2引数「RowHead」・・・見出し行番号を指定します。
第3引数「ArrRange」・・・Variant型の配列を格納する返却用の引数です。
最大行が定まらないケースを考慮して、見出し行の先頭列から最終列の最大行を順に取得して、その中でいちばん多い行を最終行と判断しています。
呼び出し例
この関数の使い方をサンプルでご紹介します。
例えばこんな表があったとして、
この表のデータを配列に格納するには次のように記述します。
Public Function FncTest() As Boolean
Dim sMsg As String
Dim vArray As Variant
Dim i As Long, j As Long
If FncGetDataArray(ThisWorkbook.Worksheets("公安局刑事課"), 1, vArray, sMsg) Then
If IsArray(vArray) Then
For i = 1 To UBound(vArray, 1)
For j = 1 To UBound(vArray, 2)
Debug.Print vArray(i, j), ;
Next j
Debug.Print ""
Next i
End If
End If
End Function
上のサンプルコードで表示された結果が以下。
このサンプルではイミディエイトウィンドウに表示しています。
配列をセル範囲に出力するサンプル
もうひとつ、Variant変数に格納されている配列をExcel Sheetのセル範囲に貼り付ける短いサンプルを紹介します。
Set objSheet = ThisWorkbook.Worksheets("Sheet1")
With objSheet
'--- 明細出力 ---
.Range(.Cells(llRowHead + 1, 1), .Cells(llRowHead + llRecordCNT, llFieldsCNT)) = vvArray
End With
左上端の1セルだけ指定しても配列のすべての値が一括で貼り付けできますが、貼り付け範囲を正確にコントロールしたければ、貼り付け先の行数、列数を指定した方が良いでしょう。
さいごに
多くの用途で使えるテクニックなので、業務や用途に合わせて使ってみてください。
ご紹介した範囲配列取得のテクニックで、簡単楽チンにExcelデータを取得&貼り付けできるサンプルコードでした。
お役に立てれば。