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.
![표2. Row Count, Cell Count와 각 방법의 읽기(Read) 소요시간(초)](https://prodskill.com/wp-content/uploads/2022/09/image-134.png)
![그림1. Row Count, Cell Count와 각 방법의 읽기(Read) 소요시간(초)](https://prodskill.com/wp-content/uploads/2022/09/image-135.png)
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.