Patrón de codificación VBA: Bucle de rango - Escritura

Veamos el rango de escritura en bucle entre los patrones de codificación de VBA.

resumen

Este artículo presenta patrones de codificación que mejoran el rendimiento al escribir una gran cantidad de datos en una hoja de Excel usando Variant Array.

En el artículo siguiente ya se trata cómo mejorar el rendimiento al leer una gran cantidad de datos de una hoja de Excel.

Patrón de codificación VBA: bucle de rango - lectura

Resumen de una línea: leer con Variant Array es incomparablemente más rápido que Loop.

Esta vez, veremos cómo escribir datos en una hoja de Excel dentro del código VBA. El rendimiento de cada método se compara en la parte inferior.

como probar

Ejecute la prueba de la siguiente manera.

• Escriba valores en cada celda mientras aumenta filas y columnas según el Rango ("B2").
• El valor registrado en la celda está en el formato "número de fila, número de columna".
• El número de columnas es fijo (10 columnas).
• Repita a medida que el número de filas aumente de 10, 100, 500, 1000 a 100 000.
• Dado que el número de celdas es el número de filas x 10, se prueban hasta 1 millón de celdas.

El código de prueba se puede descargar desde github.

Enlace del archivo github: DAToolset/VBACode/VBA Codificación Patrón-Rango de bucle (Escritura).xlsm

Patrón básico: bucle anidado

El patrón básico utiliza la sintaxis de bucle para la columna dentro de la sintaxis de bucle para la fila mientras aumenta el desplazamiento de la fila y la columna según la celda inicial.

El código detallado es el siguiente. Publicación anterior Patrón de codificación VBA: bucle de rango - lectura El código es casi el mismo y la lectura se cambió a la escritura. (línea 10)

Debido a que el Texto del objeto Rango es una propiedad de solo lectura, no pude ingresar un valor, así que ingresé el valor directamente en el objeto Rango.

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

Patrón usando un solo bucle - Single Loop(1)

El bucle interno de las columnas se reemplazó con tantas oraciones como columnas haya (líneas 11 a 20). Asimismo, el valor se ingresó directamente en el objeto Rango.

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

Patrón usando un solo bucle - Single Loop(2)

Se mantuvo la sintaxis de bucle único y se utilizó Valor2 al ingresar el valor en el rango.

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

Cómo escribir datos en la memoria en un rango a la vez: uso de Variant Array

Este es un método para escribir datos almacenados en una matriz variante bidimensional en un rango a la vez. El código detallado es el siguiente.

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

El código central es la línea 14. Escriba datos asignando directamente una matriz variante al rango.

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

Patrón de codificación VBA: bucle de rango - lectura Si lo compara con el siguiente código leído en , puede ver que es el mismo, solo que se cambiaron los términos izquierdo y derecho.

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

Comparación de rendimiento

Comparación del rendimiento de escritura de rango

Patrón de codificación VBA: bucle de rango - lectura De la misma manera, el tiempo de ejecución de cada método se resumió aumentando el Row Count de 10 a 100.000.
(Entorno de ejecución: LG Gram 2019, CPU i5, Ram 16 GB, SSD de 256 GB)

El rendimiento de escritura de rango se puede resumir de la siguiente manera.

• Casi no hay diferencia entre ingresar un valor usando Valor2 e ingresar un valor directamente en un objeto Rango.
• Los resultados mostraron que Nested Loop era ligeramente más rápido que Single Loop. Dado que esto es lo opuesto a Leer, siempre parece difícil decir que es más rápido.
• Escribir datos usando Variant Array es incomparablemente más rápido (al menos 10 veces más).

Comparación del rendimiento de lectura de rango

Para que pueda comparar inmediatamente el rendimiento de lectura y escritura Leer comparación de rendimiento Copie el contenido a continuación.

---

Al leer y escribir una gran cantidad de datos en una hoja de Excel con código VBA, el rendimiento se puede mejorar enormemente utilizando la matriz Variant, así que utilícela adecuadamente cuando sea necesario.