Effektiv Databehandling

Alt hidtil har arbejdet én celle ad gangen — fint til fem ordrer, men ekstremt langsomt til halvtreds tusinde. Løsningen er at undgå at arbejde celle for celle på regnearket og i stedet flytte hele blokken ind i et array i hukommelsen, bearbejde det der, og skrive det tilbage på én gang.

Læsning af et område ind i et array

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

dataArr er nu et 2D-array i hukommelsen: dataArr(1,1) er ORD1001, dataArr(1,4) er "Laptop Stand", og så videre — VBA-arrays læst fra et område er 1-baserede, ikke 0-baserede, hvilket ofte forvirrer.

Bearbejdning af arrayet

Foregår med almindelige løkker — men nu looper du over hukommelsen, hvilket er tusindvis af gange hurtigere end at loope over regnearket:

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

Skrivning af arrayet tilbage

Igen en enkelt operation:

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

Undgå Select og Activate

Optagede makroer bruger ofte disse (Range("A1").Select efterfulgt af Selection.Font.Bold = True), men de er unødvendige og langsomme — hver .Select tvinger Excel til at opdatere skærmen. Henvis i stedet direkte til området eller objektet:

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

Ydelsesmæssige overvejelser

Disse bliver vigtige, når dine data overstiger et par hundrede rækker:

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

Gendan altid disse indstillinger til sidst — og indpak gendannelsen i fejlhåndtering, så et nedbrud undervejs ikke efterlader Excel med skærmopdatering slået fra.

Gennemarbejdet eksempel: Order Integrity Checker

Dette kapitel har bygget op til dette:

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 dette mod eksempeltabellen, og det bør rapportere, at alle fem totaler er korrekte — prøv at ændre ORD1002's Total på arket til noget forkert og kør igen for at se advarslen.

Opgave

1. Kopiér VerifyOrderTotals ind i din projektmappe og kør den på de fem eksempelordrer.
2. Ødelæg manuelt én ordres Total på arket (indtast et åbenlyst forkert tal) og kør igen — bekræft at uoverensstemmelsen rapporteres med det korrekte Order ID.
3. Tilføj ti ekstra rækker med fiktive ordredata under række 6, og bekræft at makroen stadig fungerer uden ændringer i koden — dette er fordelen ved lastRow og array-baseret behandling.