【VBA】指定した範囲のデータを一括で取得して、2次元配列へ格納する

セルへのアクセスは
　・コストが高い
とされています！
※「セルへのアクセス」とは「Range等を使用した処理」のことです。

コストが高いため、セルへのアクセス回数が多いと

処理が重くなってしまいます！
※例えば「繰り返し処理でセルへ数百回アクセス」すると、重くなってしまいます。

よって
　・セルへのアクセス回数は少ない方が良い
です！

ここでは
　・指定した範囲のデータを一括で取得して
　・2次元配列へ格納する方法
を紹介します！
※「一括で取得」とは「セルへのアクセスが1回」ということです。
※セルへのアクセスが1回のため、処理が重くなりません。

一括で取得したデータは
　・2次元配列を使った繰り返し処理で使用
します！
※「配列を使う」とは「配列へのアクセス」であり、「メモリへのアクセス」です。
※メモリへのアクセスのため、高速に動作します(処理が重くなりません)。
※「繰り返し処理で配列へ数百回アクセス」しても、処理は重くなりません。

VBAコード
実行結果
参考①
参考②

VBAコード

ここでは例として、シート「sample」上の
　・セル「B3」～「E7」の範囲のデータを一括で取得して2次元配列へ格納し、
　・2次元配列を使った繰り返し処理のよりデータを表示
します。

Option Explicit

Sub sample()

    '1番右の列を定義
    Const DEPT_COLUMN As Integer = "5"
    
    Dim startRange As Range
    Dim endRow As Long
    Dim endRange As Range
    Dim arrData As Variant
    Dim i As Long
    
    '開始セルを取得 ※B列「ID」の最初の行のセルを取得
    Set startRange = Worksheets("sample").Range("B3")
    '最終行を取得
    endRow = startRange.End(xlDown).Row
    '最終セルを取得 ※E列「部署」の最終行のセルを取得
    Set endRange = Worksheets("sample").Cells(endRow, DEPT_COLUMN)
    
    '指定した範囲を一括で取得　※2次元配列として取得
    arrData = Range(startRange, endRange)
    
    '2次元配列の数だけ繰り返し
    For i = LBound(arrData) To UBound(arrData)
        
        '一括で取得した範囲を表示
        MsgBox i & "行目のデータ" & vbLf & _
               arrData(i, 1) & " | " & _
               arrData(i, 2) & " | " & _
               arrData(i, 3) & " | " & _
               arrData(i, 4)
    Next
    
End Sub

「1番右の列を示す定数」を定義します(6行目)。
※可読性を良くするためです。
※上記ではE列「部署」を示す「5列目」を定義しています。

「一括で取得した範囲のデータを格納するVariant型の変数」を定義します(11行目)。
※この「Variant型の変数」が「2次元配列」になります。

「開始セル」と「終了セル」を指定または取得します(15～19行目)。

指定した範囲を一括で取得して、「Variant型の変数」へ格納します(22行目)。
※「Applicationオブジェクト」の「Range」プロパティを使用して、一括で取得します。
※「Applicationオブジェクト」の「Range」プロパティは省略した記載が可能です。上記は省略しています。
※この「Variant型の変数」が「2次元配列」になります。

2次元配列を使って繰り返し処理をします(25～33行目)。
※「2次元配列へのアクセス」は「メモリへのアクセス」です。
※メモリへのアクセスのため高速に動作します(セルへのアクセスと違い処理が重くなりません)。