Bisher wurde immer nur eine Zelle nach der anderen bearbeitet – ausreichend für fünf Bestellungen, aber extrem langsam bei fünfzigtausend. Die Lösung besteht darin, nicht mehr Zelle für Zelle auf das Arbeitsblatt zuzugreifen, sondern den gesamten Bereich in ein Array im Speicher zu laden, dort zu bearbeiten und anschließend in einem Schritt zurückzuschreiben.

Einlesen eines Bereichs in ein Array

Dim dataArr As Variant
dataArr = ws.Range("A2:I6").Value    ' one read, not 45 individual reads

dataArr ist jetzt ein zweidimensionales Array im Speicher: dataArr(1,1) ist ORD1001, dataArr(1,4) ist "Laptop Stand" und so weiter — VBA-Arrays, die aus einem Bereich gelesen werden, sind 1-basiert, nicht 0-basiert, was häufig zu Verwirrung führt.

Verarbeitung des Arrays

Erfolgt mit normalen Schleifen – allerdings wird jetzt im Speicher gearbeitet, was tausendfach schneller ist als das Durchlaufen des Arbeitsblatts:

Dim i As Long
Dim recalculated As Double
 
For i = 1 To UBound(dataArr, 1)
    recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
    If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
        Debug.Print "Mismatch on row " & i & ": sheet says " & dataArr(i, 8) & _
                    ", recalculated " & Format(recalculated, "0.00")
    End If
Next i

Zurückschreiben des Arrays

Wieder ein einziger Vorgang:

ws.Range("A2:I6").Value = dataArr

Vermeidung von Select und Activate

Aufgezeichnete Makros verwenden diese häufig (Range("A1").Select gefolgt von Selection.Font.Bold = True), aber sie sind unnötig und langsam – jedes .Select zwingt Excel dazu, den Bildschirm neu zu zeichnen. Stattdessen den Bereich oder das Objekt direkt referenzieren:

' Avoid:
ws.Range("A1").Select
Selection.Font.Bold = True
 
' Prefer:
ws.Range("A1").Font.Bold = True

Leistungsaspekte

Diese werden relevant, sobald Ihre Daten mehr als ein paar hundert Zeilen umfassen:

Application.ScreenUpdating = False   ' stop redrawing while the macro runs
Application.Calculation = xlCalculationManual   ' pause recalculation
Application.EnableEvents = False     ' suppress other macros triggering mid-run
 
' ... your fast array-based code here ...
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Diese Einstellungen immer am Ende wiederherstellen – und die Wiederherstellung in eine Fehlerbehandlung einbinden, damit Excel bei einem Absturz nicht mit deaktivierter Bildschirmaktualisierung zurückbleibt.

Durchgearbeitetes Beispiel: Der Order Integrity Checker

Dieses Kapitel läuft auf Folgendes hinaus:

Option Explicit
 
Sub VerifyOrderTotals()
    Dim ws As Worksheet
    Dim dataArr As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim recalculated As Double
    Dim issues As String
 
    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
    Application.ScreenUpdating = False
 
    dataArr = ws.Range("A2:I" & lastRow).Value
    issues = ""
 
    For i = 1 To UBound(dataArr, 1)
        recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
        If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
            issues = issues & dataArr(i, 1) & ": sheet=" & dataArr(i, 8) & _
                      ", expected=" & Format(recalculated, "0.00") & vbNewLine
        End If
    Next i
 
    Application.ScreenUpdating = True
 
    If issues = "" Then
        MsgBox "All " & UBound(dataArr, 1) & " order totals check out."
    Else
        MsgBox "Discrepancies found:" & vbNewLine & issues
    End If
End Sub

Führen Sie dies mit der Beispieltabelle aus und es sollten alle fünf Summen als korrekt gemeldet werden – ändern Sie zum Testen den Wert von ORD1002 im Feld Total auf einen falschen Wert und führen Sie das Makro erneut aus, um die Fehlermeldung zu sehen.

Aufgabe

1. Kopieren Sie VerifyOrderTotals in Ihre Arbeitsmappe und führen Sie es für die fünf Beispielaufträge aus.
2. Ändern Sie manuell den Wert von Total eines Auftrags im Tabellenblatt (geben Sie eine offensichtlich falsche Zahl ein) und führen Sie das Makro erneut aus – bestätigen Sie, dass die Abweichung mit der richtigen Order ID gemeldet wird.
3. Fügen Sie zehn weitere Zeilen mit erfundenen Auftragsdaten unterhalb von Zeile 6 hinzu und prüfen Sie, ob das Makro weiterhin ohne Codeänderungen funktioniert – das ist der Vorteil von lastRow und Array-Verarbeitung.