VBA-Codierungsmuster: Bereichsschleife - Lesen

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

Zusammenfassung

Bereichsschleife ist ein Codierungsmuster, das häufig beim Lesen und Verarbeiten von Daten verwendet wird, die in eine Excel-Tabelle mit VBA-Code eingegeben wurden. Das Grundmuster des sequentiellen Lesens von der Startzelle bis zur letzten Zelle und das Leistungsverbesserungsmuster des Lesens auf einmal statt sequenziell werden unten eingeführt, und ihre Leistungen werden verglichen.

Testdaten, VBA-Code testen

Zur Erläuterung werden die Beispieldaten mit 100.000 Zeilen, 10 Spalten und insgesamt 1 Million Zellen erstellt, und der Wert jeder Zelle wird als Koordinatenwert von (Zeile, Spalte) festgelegt.

Im Link zur Github-Datei unten können Sie die Beispieldaten, die Zelle „B2“ als Ausgangspunkt sowie das Codierungsmuster und das Ausführungsergebnis der Untersuchung des Bereichs von 10 Zeilen (100 Zellen) bis 100.000 Zeilen (1 Million Zellen) überprüfen.

Dateilink: github DAToolset/VBACode/VBA Coding Patterns-Range Loop(Read).xlsm

Bereichsschleife – Grundmuster lesen – 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.

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

Der folgende Code der inneren For-Schleife ist als Beispiel für den einfachsten Code zum Lesen eines Zellenwerts geschrieben.

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

Muster mit einer einzelnen Schleife - verwenden Sie eine einzelne Schleife (1), Text

Dies ist eine Syntax, die die interne Schleifenanweisung für Spalten durch Anweisungen ersetzt, die der Anzahl der Spalten entsprechen.Der detaillierte Code lautet wie folgt. Text wurde verwendet, um den Wert von Range abzurufen.

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

Muster mit Einzelschleife-Einzelschleife(2), Verwenden Sie Wert2 anstelle von Text

Beim Beibehalten der Single-Loop-Syntax und Herausziehen des Werts von Range wurde Value2 anstelle von Text verwendet. Der Leistungsvergleich steht ganz unten.

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

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

Dies ist eine Methode zum Festlegen des Bereichs zum Lesen von Daten und zum sofortigen Lesen als 2D-Variantenarray.Der detaillierte Code lautet 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
    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

Der Schlüssel hier ist der folgende Code.

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

Resize erweitert den Bereich vom Startbereich (Range) auf die angegebene Anzahl von Zeilen und Spalten, um das Ziel festzulegen, aus dem Werte gelesen werden sollen. Value2 ist eine Eigenschaft, die den Wert von Range als seinen ursprünglichen Wert ohne Formatierung abruft. Wenn der Bereich zum Abrufen eines Werts mehrere Zeilen und Spalten umfasst, wird der Wert als zweidimensionales Variant Array abgerufen. Der obige Code liest also den gesamten zu lesenden Bereich auf einmal in den Speicher.

Leistungsvergleich

Die Ausführungszeit jeder Methode wird wie folgt zusammengefasst, während die Zeilenanzahl von 10 auf 100.000 erhöht wird.
(Ausführungsumgebung: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)

Die Ausführungsleistung kann wie folgt zusammengefasst werden.

• Es gibt fast keinen Unterschied in der Ausführungsleistung von Wiederholungsanweisungen für verschachtelte Schleifen und Einzelschleifen selbst.
• Beim Extrahieren eines Werts aus Range ist die Verwendung von Text langsam und die Verwendung von Value2 ist schnell.
• Mit Variant Array können auch viele Daten sehr schnell gelesen werden.

Als Referenz wird der Vergleich der Eigenschaften von Text, Value und Value2, die zum Abrufen von Werten aus dem Bereich verwendet werden, später organisiert, wenn die Zeit reif ist. Zunächst verlinke ich den Artikel auf stackoverflow.com, den ich bei der Google-Suche gefunden habe.
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2