VBA Coding Pattern: Range Loop - Write

Let's look at the contents of Range Loop-Write among VBA coding patterns.

summary

Introduces a coding pattern that improves performance when writing a lot of data in an Excel sheet using Variant Array.

How to improve performance when reading a lot of data from an Excel sheet has already been covered in the article below.

VBA Coding Pattern: Range Loop - Read

One-line summary: Reading from a Variant Array is incomparably faster than a Loop.

This time, we will look at how to write data to an Excel sheet from within VBA code. The performance of each method is compared at the bottom.

test method

Run the test in the following way.

• Values are written to each cell while increasing rows and columns based on Range(“B2”).
• The value recorded in the cell is in the format of “row number, column number”.
• The number of columns is set to fixed (10 columns).
• Repeat while increasing the number of rows from 10, 100, 500, 1000 to 100,000.
• Since the number of cells is the number of rows x 10, we test up to 1 million cells.

The test code can be downloaded from github.

github file link: DAToolset/VBACode/VBA Coding Patterns - Range Loop(Write).xlsm

Basic Pattern - Nested Loop

The basic pattern uses the Loop statement for the column inside the Loop statement for the row while increasing the offset of the row and column based on the starting cell.

The detailed code is as follows. previous post VBA Coding Pattern: Range Loop - Read and the code is almost the same, and read is changed to write. (line 10)

Since the Text of the Range object is a read-only property, a value cannot be entered, so the value is entered directly into the Range object.

'* 기본 패턴(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

Patterns using a single Loop - Single Loop(1)

The internal loop statement for the columns was replaced with as many statements as the number of columns (lines 11 to 20). Similarly, we entered values directly into the Range object.

'단일 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

Patterns using a single Loop - Single Loop(2)

Value2 was used when maintaining the Single Loop syntax and entering values into the Range.

'단일 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

How to write data in memory to Range at once - Using Variant Array

This is a method of writing data stored in a two-dimensional variant array to a range at once. The detailed code is as follows.

' 한번에 쓰는 패턴
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

The core code is line 14. Write data by directly assigning a Variant array to Range.

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

VBA Coding Pattern: Range Loop - Read Comparing with the following code read from

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

Performance Comparison

Range Write Performance Comparison

VBA Coding Pattern: Range Loop - Read Similarly, the execution time of each method was summarized while increasing the Row Count from 10 to 100,000.
(Execution environment: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)

Range write performance can be summarized as follows.

• There is almost no difference between the method of entering a value using Value2 and the method of entering a value directly into the Range object.
• Nested Loop was slightly faster than Single Loop. This is the opposite of Read, so it's hard to say that it's always faster.
• If you write data using Variant Array, it is incomparably faster (at least 10 times or more).

Range read performance comparison

For immediate comparison of read and write performance Read performance comparison Copy the contents below.

---

When reading and writing a large amount of data in an Excel sheet with VBA code, performance can be greatly improved by using a variant array, so please use it appropriately if necessary.