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

Entre los patrones de codificación de VBA, mire el contenido de Range Loop-Read.

resumen

Range Loop es un patrón de codificación que se usa a menudo al leer y procesar datos ingresados en una hoja de Excel con código VBA. El patrón básico de lectura secuencial desde la celda de inicio hasta la última celda y el patrón de mejora del rendimiento de lectura inmediata en lugar de secuencial se presentan a continuación y se comparan sus rendimientos.

Datos de prueba, código VBA de prueba

Como explicación, los datos de ejemplo se crean con 100 000 filas, 10 columnas y un total de 1 millón de celdas, y el valor de cada celda se establece como el valor de coordenadas de (fila, columna).

En el enlace del archivo github a continuación, puede verificar los datos de ejemplo, la celda "B2" como punto de partida y el patrón de codificación y el resultado de ejecución de explorar el rango de 10 filas (100 celdas) a 100,000 filas (1 millón de celdas).

Enlace del archivo: github DAToolset/VBACode/Patrones de codificación VBA-Range Loop (Read).xlsm

Bucle de rango: patrón básico de lectura: bucle anidado

El patrón básico aumenta el desplazamiento de la fila y la columna en función de la celda inicial y utiliza la instrucción de bucle para la columna dentro de la instrucción de bucle para la fila. El código detallado es el siguiente.

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

El siguiente código del For Loop interno está escrito como un ejemplo del código más simple para leer un valor de celda.

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

Patrón usando un solo bucle: use un solo bucle (1), texto

Esta es una sintaxis que reemplaza la declaración de bucle interno para columnas con declaraciones tantas como el número de columnas.El código detallado es el siguiente. Se usó texto para recuperar el valor de Rango.

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

Patrón usando Bucle simple-Bucle simple (2), Use Value2 en lugar de Text

Al mantener la sintaxis de Single Loop y extraer el valor de Range, se usó Value2 en lugar de Text. La comparación de rendimiento está escrita en la parte inferior.

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

Cómo leer datos en la memoria a la vez: uso de Variant Array

Este es un método para configurar el rango para leer datos y leerlos a la vez como una matriz de variantes 2D. 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
    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

La clave aquí es el siguiente código.

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

El cambio de tamaño expande el rango desde el rango inicial (Rango) hasta el número especificado de filas y columnas para establecer el objetivo para leer los valores. Value2 es una propiedad que recupera el valor de Range como su valor original sin formato. Si el rango para recuperar un valor incluye varias filas y columnas, el valor se recupera como una matriz de variante bidimensional. Entonces, el código anterior lee todo el rango para ser leído en la memoria a la vez.

Comparación de rendimiento

El tiempo de ejecución de cada método se resume de la siguiente manera al aumentar el recuento de filas de 10 a 100 000.
(Entorno de ejecución: 2019 LG Gram, CPU i5, Ram 16 GB, SSD de 256 GB)

El rendimiento de ejecución se puede resumir de la siguiente manera.

• Prácticamente no hay diferencia en el rendimiento de ejecución de las instrucciones de repetición Nested Loop y Single Loop.
• Al extraer un valor de Range, el uso de Text es lento y el uso de Value2 es rápido.
• Usando Variant Array, incluso si hay muchos datos, se pueden leer muy rápidamente.

Como referencia, la comparación de las propiedades de Texto, Valor y Valor2, que se utilizan para recuperar valores del Rango, se organizará más adelante cuando sea el momento adecuado. En primer lugar, enlazo el artículo de stackoverflow.com que encontré buscando en Google.
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2