Effektiv Databehandling

Hittills har allt arbete skett en cell i taget — fungerar bra för fem beställningar, men är smärtsamt långsamt för femtiotusen. Lösningen är att sluta arbeta cell för cell i kalkylbladet och istället flytta hela blocket till en array i minnet, bearbeta det där och skriva tillbaka allt på en gång.

Läsa in ett område till en array

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

dataArr är nu en tvådimensionell array i minnet: dataArr(1,1) är ORD1001, dataArr(1,4) är "Laptop Stand" och så vidare — VBA-arrayer som läses från ett område är 1-baserade, inte 0-baserade, vilket ofta orsakar förväxling.

Bearbeta arrayen

Sker med vanliga loopar — men nu loopar du över minnet, vilket är tusentals gånger snabbare än att loopa över kalkylbladet:

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

Skriva tillbaka arrayen

Återigen en enda operation:

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

Undvik Select och Activate

Inspelade makron använder ofta dessa (Range("A1").Select följt av Selection.Font.Bold = True), men de är onödiga och långsamma — varje .Select tvingar Excel att rita om skärmen. Referera istället direkt till området eller objektet:

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

Prestandaöverväganden

Dessa blir viktiga när din data växer över några hundra rader:

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

Återställ alltid dessa inställningar i slutet — och kapsla in återställningen i felhantering så att ett avbrott inte lämnar Excel med avstängd skärmuppdatering.

Genomgång: Order Integrity Checker

Detta kapitel har lett fram till detta:

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

Kör detta mot exempeltabellen och det ska rapportera att alla fem totaler är korrekta — prova att ändra ORD1002:s Total på bladet till något felaktigt och kör igen för att se varningen.

Uppgift

1. Kopiera VerifyOrderTotals till din arbetsbok och kör den mot de fem exempelordrarna.
2. Ändra manuellt en orders Total på bladet (skriv in ett uppenbart felaktigt tal) och kör igen — bekräfta att avvikelsen rapporteras med rätt Order ID.
3. Lägg till tio rader med påhittad orderdata under rad 6 och kontrollera att makrot fortfarande fungerar utan kodändringar — detta är fördelen med lastRow och array-baserad bearbetning.