VBA Coding Pattern: Range Loop - Read

Among the VBA coding patterns, look at the contents of Range Loop-Read.

summary

Range Loop is a coding pattern often used when reading and processing data entered in an Excel sheet with VBA code. The basic pattern of sequentially reading from the start cell to the last cell and the performance improvement pattern of reading at once instead of sequentially are introduced below, and their performances are compared.

Test data, Test VBA Code

For explanation, the example data is created with 100,000 rows, 10 columns, and a total of 1 million cells, and the value of each cell is set as the coordinate value of (row, column).

In the github file link below, you can check the example data, “B2” cell as the starting point, and the coding pattern and execution result of exploring the range from 10 rows (100 cells) to 100,000 rows (1 million cells).

File Link: github DAToolset/VBACode/VBA Coding Patterns-Range Loop(Read).xlsm

Range Loop-Read Basic Pattern-Nested Loop

The basic pattern increases the offset of the row and column based on the starting cell, and uses the loop statement for the column inside the loop statement for the row. The detailed code is as follows.

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

The following code of the inner For Loop is written as an example of the simplest code to read a cell value.

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

Pattern using a single loop - use single loop(1), text

This is a syntax that replaces the internal loop statement for columns with statements as many as the number of columns. The detailed code is as follows. Text was used to retrieve the value of Range.

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

Pattern using Single Loop-Single Loop(2), Use Value2 instead of Text

When maintaining the Single Loop syntax and pulling out the value of Range, Value2 was used instead of Text. The performance comparison is written at the bottom.

' 행만 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

How to read data into memory at once - Using Variant Array

This is a method of setting the range to read data and reading it at once as a 2D Variant Array. 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
    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

The key here is the following code.

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

Resize expands the range from the starting range (Range) to the specified number of rows and columns to set the target to read values from. Value2 is a property that retrieves the value of Range as its original value without formatting. If the Range to retrieve a value includes several rows and columns, the value is retrieved as a 2-dimensional Variant Array. So, the code above reads the entire range to be read into memory at once.

Performance Comparison

The execution time of each method is summarized as follows while increasing the row count from 10 to 100,000.
(Execution environment: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)

Execution performance can be summarized as follows.

• There is almost no difference in the execution performance of Nested Loop and Single Loop repetition statements themselves.
• When extracting a value from Range, using Text is slow, and using Value2 is fast.
• Using Variant Array, even if there is a lot of data, it can be read very quickly.

For reference, the comparison of the properties of Text, Value, and Value2, which are used to retrieve values from the Range, will be organized later when the time is right. First of all, I link the article on stackoverflow.com that I found by searching on Google.
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2