VBAコーディングパターン：Range Loop - Write

VBAコーディングパターンのうち、Range Loop-Writeについて説明します。

要約

Variant Arrayを使用してExcelシートに多くのデータを書き込むときのパフォーマンスを向上させるコーディングパターンについて紹介します。

Excelシートから多くのデータを読み取るときのパフォーマンスを向上させる方法は、以下の記事ですでに説明しています。

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

一行の要約：LoopよりVariant Arrayで読む方法が比較できないほど速い。

今回は逆にVBAコード内でデータをExcelシートに書き込む方法を見てみましょう。各方法の性能は最下部と比較した。

テスト方法

次の方法でテストを実行します。

• Range（「B2」）に基づいて行と列を増やしながら各セルに値を書きます。
• cellに記録される値は「行番号、列番号」の形式とする。
• 列の数は固定（10列）です。
• 行数は10、100、500、1000から100,000まで増加しながら繰り返されます。
• セルの数は行数×10なので、最大100万セルまでテストします。

テストコードはgithubからダウンロードできます。

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

基本パターン-Nested Loop

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

詳細コードは次のとおりです。前の記事 VBAコーディングパターン：Range Loop - 読み取り（Read） とコードはほぼ同じで、読み取りを書き込みに変更しました。 (10行)

RangeオブジェクトのTextはRead-onlyプロパティなので値を入力できず、Rangeオブジェクトに値を直接入力した。

'* 기본 패턴(Nested 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 단위로 쓰는 코드 예시
            oBaseRange.Offset(lRowOffset, lColOffset) = CStr(lRowOffset + 1) + "," + CStr(lColOffset + 1)
        Next lColOffset
    Next lRowOffset
End Sub

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

列に対する内部繰り返し文を列数分の文章（11行～20行）に置き換えた。同様に、Rangeオブジェクトに値を直接入力しました。

'단일 Loop를 사용하는 패턴: Single Loop(1)
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 = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        oBaseRange.Offset(lRowOffset, 0) = CStr(lRowOffset + 1) + ",1"
        oBaseRange.Offset(lRowOffset, 1) = CStr(lRowOffset + 1) + ",2"
        oBaseRange.Offset(lRowOffset, 2) = CStr(lRowOffset + 1) + ",3"
        oBaseRange.Offset(lRowOffset, 3) = CStr(lRowOffset + 1) + ",4"
        oBaseRange.Offset(lRowOffset, 4) = CStr(lRowOffset + 1) + ",5"
        oBaseRange.Offset(lRowOffset, 5) = CStr(lRowOffset + 1) + ",6"
        oBaseRange.Offset(lRowOffset, 6) = CStr(lRowOffset + 1) + ",7"
        oBaseRange.Offset(lRowOffset, 7) = CStr(lRowOffset + 1) + ",8"
        oBaseRange.Offset(lRowOffset, 8) = CStr(lRowOffset + 1) + ",9"
        oBaseRange.Offset(lRowOffset, 9) = CStr(lRowOffset + 1) + ",10"
    Next lRowOffset
End Sub

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

Single Loop構文を保持し、Rangeに値を入力するときにValue2を使用しました。

'단일 Loop를 사용하는 패턴: Single Loop(2), Value2 사용
Public Sub SingleLoop2(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 = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        oBaseRange.Offset(lRowOffset, 0).Value2 = CStr(lRowOffset + 1) + ",1"
        oBaseRange.Offset(lRowOffset, 1).Value2 = CStr(lRowOffset + 1) + ",2"
        oBaseRange.Offset(lRowOffset, 2).Value2 = CStr(lRowOffset + 1) + ",3"
        oBaseRange.Offset(lRowOffset, 3).Value2 = CStr(lRowOffset + 1) + ",4"
        oBaseRange.Offset(lRowOffset, 4).Value2 = CStr(lRowOffset + 1) + ",5"
        oBaseRange.Offset(lRowOffset, 5).Value2 = CStr(lRowOffset + 1) + ",6"
        oBaseRange.Offset(lRowOffset, 6).Value2 = CStr(lRowOffset + 1) + ",7"
        oBaseRange.Offset(lRowOffset, 7).Value2 = CStr(lRowOffset + 1) + ",8"
        oBaseRange.Offset(lRowOffset, 8).Value2 = CStr(lRowOffset + 1) + ",9"
        oBaseRange.Offset(lRowOffset, 9).Value2 = CStr(lRowOffset + 1) + ",10"
    Next lRowOffset
End Sub

memoryのデータを一度にRangeに書き込む方法 - Variant Arrayを使用する

2次元Variant Arrayに格納されたデータを一度にRangeに書き込む方法で、詳細コードは次のようになる。

' 한번에 쓰는 패턴
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
    ReDim vRngArr(1 To aTargetRowCnt, 1 To lColCount)
    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)
            vRngArr(lRow, lCol) = CStr(lRow) + "," + CStr(lCol)
        Next lCol
    Next lRow
    oBaseRange.Resize(aTargetRowCnt, lColCount).Value2 = vRngArr
End Sub

コアコードは14行です。 RangeにVariant arrayを直接代入してデータを書き込みます。

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

VBAコーディングパターン：Range Loop - 読み取り（Read） で読んでいる次のコードと比較すると、左項と右港だけが変わり、同じことがわかる。

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

性能比較

レンジ書き込み性能の比較

VBAコーディングパターン：Range Loop - 読み取り（Read） 同様に10から10万までRow Countを増加させながら各方法の実行時間をまとめた。
（実行環境：2019 LG Gram、CPU i5、Ram 16GB、256GB SSD）

レンジ書き込み性能は次のように要約することができる。

• Value2を使用して値を入力する方法とRangeオブジェクトに値を直接入力する方法はほとんど違いがありません。
• Single LoopよりもNested Loopが少し速い結果が出ました。これは読書（Read）と反対なので、いつもより速いとは言い難いようです。
• Variant Arrayを利用してデータを書けば比較できないほど（最低10倍以上）に高速だ。

レンジ読み取り性能の比較

読み書き性能をすぐに比較できるように 読み取り性能の比較 内容を以下にコピーしておく。

---

Excelシートで大量のデータをVBAコードで読み書きするときにVariant arrayを活用するとパフォーマンスを大幅に向上させることができるので、必要に応じて適切に活用してほしい。