きなこSHOW

自作プログラム置き場です。

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

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データを取得&貼り付けできるサンプルコードでした。

お役に立てれば。