VBAコーディングパターン：Range Loop - 読み取り（Read）

VBAコーディングパターンのうち、Range Loop-Read（Read）の内容を見てみましょう。

要約

Range Loopは、Excelシートに入力されているデータをVBAコードで読み取って処理するときによく使用されるコーディングパターンです。開始セルから最後のセルまで順次読み込む基本的なパターンと、順次読みずに一度に読み込む性能改善パターンを以下に紹介し、性能を比較する。

Test data, Test VBA Code

説明のために、例示データを１０万行、１０列、合計１００万セルで生成し、各セルの値を（行、列）の座標値に設定した。

以下のgithubファイルLinkでは、例データ、「B2」セルを始点として10行（100セル）から10万行（100万セル）まで、Rangeを探索するコーディングパターンと実行結果を確認することができる。

ファイルリンク：github DAToolset/VBACode/VBA コーディング パターン-Range Loop(Read).xlsm

Range Loop-読み取り(Read) 基本パターン-Nested Loop

基本パターンは、開始セルに基づいて行と列のオフセットを増やしながら、行のLoop（繰り返し）構文内の列のLoop（繰り返し）構文を使用します。詳細コードは次のとおりです。

'기본 패턴
Public Sub NestedLoop(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    For lRowOffset = 0 To aTargetRowCnt '읽는 행의 수(충분히 큰 수로 지정하고 Loop 내부에서 종료 조건 설정)
        If Trim(oBaseRange.Offset(lRowOffset, 0).Value) = "" Then Exit For 'Loop 종료 조건
        For lColOffset = 0 To 9 '읽는 열의 수
            'cell 단위로 읽고 처리하는 코드 예시
            'Debug.Print oBaseRange.Offset(lRowOffset, lColOffset).Value
            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text
        Next lColOffset
    Next lRowOffset
End Sub

内部For Loopの次のコードは、Cell値を読み取る最も簡単なコードを例として書いています。

            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text

単一ループを使用したパターン - Single Loop(1), Text を使用

列の内部反復文を列数と同じ文で置き換える構文で、詳細コードは次のとおりです。 Rangeの値を取り出すときにTextを使用しました。

'단일 Loop를 사용하는 패턴: Single Loop(1), Text  사용
Public Sub SingleLoop1(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count
    lRowCount = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        sVal = oBaseRange.Offset(lRowOffset, 0).Text
        sVal = oBaseRange.Offset(lRowOffset, 1).Text
        sVal = oBaseRange.Offset(lRowOffset, 2).Text
        sVal = oBaseRange.Offset(lRowOffset, 3).Text
        sVal = oBaseRange.Offset(lRowOffset, 4).Text
        sVal = oBaseRange.Offset(lRowOffset, 5).Text
        sVal = oBaseRange.Offset(lRowOffset, 6).Text
        sVal = oBaseRange.Offset(lRowOffset, 7).Text
        sVal = oBaseRange.Offset(lRowOffset, 8).Text
        sVal = oBaseRange.Offset(lRowOffset, 9).Text
    Next lRowOffset
End Sub

単一ループを使用するパターン - Single Loop(2), Text の代わりに Value2 を使用

Single Loop構文を保持してRangeの値を取り出すときは、Textの代わりにValue2を使用しました。性能比較は一番下に書いておいた。

' 행만 Loop, Text 대신 Value2 사용
Public Sub SingleLoop(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count
    lRowCount = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        sVal = oBaseRange.Offset(lRowOffset, 0).Value2
        sVal = oBaseRange.Offset(lRowOffset, 1).Value2
        sVal = oBaseRange.Offset(lRowOffset, 2).Value2
        sVal = oBaseRange.Offset(lRowOffset, 3).Value2
        sVal = oBaseRange.Offset(lRowOffset, 4).Value2
        sVal = oBaseRange.Offset(lRowOffset, 5).Value2
        sVal = oBaseRange.Offset(lRowOffset, 6).Value2
        sVal = oBaseRange.Offset(lRowOffset, 7).Value2
        sVal = oBaseRange.Offset(lRowOffset, 8).Value2
        sVal = oBaseRange.Offset(lRowOffset, 9).Value2
    Next lRowOffset
End Sub

データを一度にメモリに読み込む方法 - Variant Array の使用

データを読み取る範囲を決め、2次元Variant Arrayで一度に読み取る方法で詳細コードは次のようになる。

' 한번에 읽는 패턴
Public Sub VariantArray(aTargetRowCnt As Long)
    Dim vRngArr As Variant, oBaseRange As Range, oHeaderRange As Range, lColCount As Long, sVal As String
    Set oBaseRange = Range("B2")
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2
    Dim lCol As Long, lRow As Long
    For lRow = LBound(vRngArr, 1) To UBound(vRngArr, 1)
        For lCol = LBound(vRngArr, 2) To UBound(vRngArr, 2)
            'Debug.Print vRngArr(lRow, lCol)
            sVal = vRngArr(lRow, lCol)
        Next lCol
    Next lRow
End Sub

ここでキーは次のコードです。

    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2

Resizeは、開始範囲(Range)から指定した行、列の数だけ範囲を拡張して値を読み取る対象を設定する。 Value2は、Rangeの値を型なしで元の値に取り出す属性です。値を取り出す Range に複数の行と列が含まれている場合、その値は 2 次元 Variant Array で取り出されます。したがって、上記のコードは、読み取る範囲全体を一度にmemoryで読み取ることになる。

性能比較

10から10万までRow Countを増加させながら各方法の実行時間をまとめると次のようになる。
（実行環境：2019 LG Gram、CPU i5、Ram 16GB、256GB SSD）

実行性能は次のように要約することができる。

• Nested Loop、Single Loop 繰り返し構文自体の実行性能はほとんど差がない。
• Rangeから値を取り出すときにTextを使用すると遅くなり、Value2を使用すると高速です。
• Variant Arrayを使用すると、データが多くても非常に迅速に読み取ることができます。

ちなみに、Rangeから値を取り出すときに使用するText、Value、Value2属性の比較は、後で時間になると整理します。一度はgoogleで検索して見つけたstackoverflow.comの文をリンクしておく。
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2