VBA 编码模式：范围循环写入

在VBA编码模式中，看一下Range Loop-Write的内容。

概括

介绍了使用变体数组在 Excel 工作表中写入大量数据时提高性能的编码模式。

下面的文章已经介绍了如何在从 Excel 工作表读取大量数据时提高性能。

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

一行总结：读入 Variant Array 的速度比 Loop 快得多。

相反，这一次，我们将了解如何在 VBA 代码中将数据写入 Excel 工作表。每种方法的性能在底部进行了比较。

测试方法

按以下方式运行测试。

• 值被写入每个单元格，同时根据范围（“B2”）增加行和列。
• 单元格中记录的值的格式为“行号、列号”。
• 列数保持固定（10 列）。
• 重复此操作，同时将行数从 10、100、500、1000 增加到 100,000。
• 由于单元格数量为行数 x 10，因此最多测试 100 万个单元格。

测试代码可以从github下载。

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

基本模式 - 嵌套循环

基本模式基于起始单元格增加行和列的偏移量，并在行的 Loop 语句内使用列的 Loop 语句。

详细代码如下。上一篇文章 VBA 编码模式：范围循环读取 而且代码几乎是一样的，只是read改成了write。 （第 10 行）

由于Range对象的Text是只读属性，无法输入值，所以直接将值输入到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)

Value2在维护单循环语法并向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

如何一次性将内存中的数据写入Range - 使用Variant Array

这是将2D 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行。直接用Variant数组代替Range来写入数据。

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

VBA 编码模式：范围循环读取 如果你将其与下面读取的代码进行比较，你会发现只有左右两侧发生了变化，而且它们是相同的。

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

性能比较

范围写入性能比较

VBA 编码模式：范围循环读取 同样，当行数从 10 增加到 100,000 时，总结了每个方法的执行时间。
（执行环境：2019 LG Gram、CPU i5、Ram 16GB、256GB SSD）

范围写入性能可总结如下。

• 使用 Value2 输入值与直接向 Range 对象输入值几乎没有区别。
• 嵌套循环产生的结果比单循环稍快。它与 Read 相反，因此很难说它总是更快。
• 使用 Variant Array 写入数据的速度无与伦比（至少快 10 倍）。

范围读取性能比较

这样就可以直接比较读写性能。 读取性能比较 复制下面的内容。

---

使用VBA代码在Excel工作表中读写大量数据时，变体数组可以大大提高性能，因此在必要时适当使用它。