VBA 编码模式：范围循环读取

让我们看看 VBA 编码模式中的 Range Loop-Read。

概括

范围循环是一种在读取和处理使用 VBA 代码在 Excel 工作表中输入的数据时经常使用的编码模式。下面介绍从起始单元到最后一个单元顺序读取的基本模式和一次性读取而不是顺序读取的性能改进模式，并进行性能比较。

测试数据、测试VBA代码

为了便于说明，示例数据创建为 100,000 行、10 列、总共 100 万个单元格，每个单元格的值设置为（行、列）的坐标值。

在下面的 github 文件链接中，您可以检查示例数据、编码模式和执行结果，探索从“B2”单元格开始的 10 行（100 个单元格）到 100,000 行（100 万个单元格）的范围。

文件链接：github DAToolset/VBACode/VBA 编码模式-范围循环（读取）.xlsm

范围循环-读取基本模式-嵌套循环

基本模式在行的循环语法内使用列的循环语法，同时根据起始单元格增加行和列的偏移量。详细代码如下。

'기본 패턴
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 循环代码是作为读取 Cell 值的最简单代码示例编写的。

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

使用单个循环的模式 - 单循环(1)，文本用法

这是一种用等于列数的语句代替内部循环for columns的语法，详细代码如下。文本用于检索范围的值。

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

使用单个循环的模式 - 单循环(2)，使用 Value2 而不是 Text

保留了单循环语法，并在取出 Range 的值时使用 Value2 代替 Text。性能比较写在底部。

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

这是一种确定读取数据范围并一次性读取为二维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 的值作为其原始值（不带格式）。如果要从中检索值的范围包含多行和多列，则该值将以二维变体数组的形式检索。因此，上面的代码将整个读取范围一次性读取到内存中。

性能比较

当行数从 10 增加到 100,000 时，每个方法的执行时间总结如下。
（运行环境：2019 LG Gram，CPU i5，Ram 16GB，256GB SSD）

执行性能可以总结如下：

• 嵌套循环和单循环迭代语句本身的执行性能差异不大。
• 从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