VBA-Codierungsmuster: Bereichsschleife - Schreiben

Sehen Sie sich unter den VBA-Codierungsmustern den Inhalt von Range Loop-Write an.

Zusammenfassung

Codierungsmuster, die die Leistung beim Schreiben vieler Daten in Excel-Tabellen mit Variant Arrays verbessern, werden eingeführt.

Der folgende Artikel hat bereits behandelt, wie Sie die Leistung verbessern können, wenn Sie viele Daten aus einer Excel-Tabelle lesen.

VBA-Codierungsmuster: Bereichsschleife - Lesen

Einzeilige Zusammenfassung: Das Lesen in Variant Array ist unvergleichlich schneller als Loop.

Im Gegensatz dazu werden wir uns dieses Mal ansehen, wie Daten innerhalb des VBA-Codes in eine Excel-Tabelle geschrieben werden. Die Leistung jeder Methode wird unten verglichen.

Testmethode

Führen Sie den Test folgendermaßen durch.

• Werte werden in jede Zelle geschrieben, während Zeilen und Spalten basierend auf Range („B2“) erhöht werden.
• Der in der Zelle aufgezeichnete Wert hat das Format „Zeilennummer, Spaltennummer“.
• Die Anzahl der Spalten bleibt fest (10 Spalten).
• Wiederholen Sie den Vorgang, während Sie die Anzahl der Zeilen von 10, 100, 500, 1000 auf 100.000 erhöhen.
• Da die Anzahl der Zellen gleich der Anzahl der Zeilen x 10 ist, testen Sie bis zu 1 Million Zellen.

Der Testcode kann von github heruntergeladen werden.

Link zur Github-Datei: DAToolset/VBACode/VBA Coding Patterns-Range Loop(Write).xlsm

Grundmuster – verschachtelte Schleife

Das Grundmuster erhöht den Offset der Zeile und Spalte basierend auf der Startzelle und verwendet die Loop-Anweisung für die Spalte innerhalb der Loop-Anweisung für die Zeile.

Der detaillierte Code lautet wie folgt. vorherigen Post VBA-Codierungsmuster: Bereichsschleife - Lesen und der Code ist fast derselbe, und Lesen wurde in Schreiben geändert. (Zeile 10)

Da der Text des Range-Objekts eine schreibgeschützte Eigenschaft ist, können Sie keinen Wert eingeben, also geben Sie den Wert direkt in das Range-Objekt ein.

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

Muster mit Einzelschleife-Einzelschleife(1)

Die interne Schleifenanweisung für die Spalten wurde durch so viele Anweisungen ersetzt, wie es Spalten gibt (Zeilen 11 bis 20). Ebenso haben wir Werte direkt in das Range-Objekt eingegeben.

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

Muster mit Einzelschleife-Einzelschleife(2)

Value2 wurde verwendet, um die Single-Loop-Syntax beizubehalten und Werte in den Bereich einzugeben.

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

So schreiben Sie Daten in den Speicher auf einmal in Range - Verwenden von Variant Array

Dies ist eine Methode, um die im 2D Variant Array gespeicherten Daten auf einmal in den Bereich zu schreiben.Der detaillierte Code ist wie folgt.

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

Der Kerncode besteht aus 14 Zeilen. Ersetzen Sie Range direkt durch das Variant-Array, um Daten zu schreiben.

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

VBA-Codierungsmuster: Bereichsschleife - Lesen Wenn Sie es mit dem folgenden abgelesenen Code vergleichen, können Sie sehen, dass nur die linke und rechte Seite geändert wurden und sie gleich sind.

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

Leistungsvergleich

Bereichsschreibleistungsvergleich

VBA-Codierungsmuster: Bereichsschleife - Lesen Ebenso wurde die Ausführungszeit jeder Methode zusammengefasst und gleichzeitig die Zeilenanzahl von 10 auf 100.000 erhöht.
(Ausführungsumgebung: 2019 LG Gram, CPU i5, Ram 16 GB, 256 GB SSD)

Die Bereichsschreibleistung kann wie folgt zusammengefasst werden.

• Es gibt fast keinen Unterschied zwischen der Eingabe eines Werts mit Value2 und der direkten Eingabe eines Werts in das Range-Objekt.
• Verschachtelte Schleifen erzeugten etwas schnellere Ergebnisse als einzelne Schleifen. Es ist das Gegenteil von Read, also ist es schwer zu sagen, dass es immer schneller ist.
• Das Schreiben von Daten mit Variant Array ist unvergleichlich schneller (mindestens 10 Mal mehr).

Reichweiten-Leseleistungsvergleich

So können Sie die Lese- und Schreibleistung direkt vergleichen. Leistungsvergleich lesen Kopieren Sie den Inhalt unten.

---

Variant Array kann die Leistung beim Lesen und Schreiben einer großen Datenmenge in einer Excel-Tabelle mit VBA-Code erheblich verbessern, verwenden Sie es daher bei Bedarf entsprechend.