Tehokas tietojenkäsittely

Kaikki tähän asti on toiminut solu kerrallaan — toimii viidelle tilaukselle, mutta on tuskallisen hidasta viidellekymmenelletuhannelle. Ratkaisu on lopettaa taulukon käsittely solu kerrallaan ja siirtää koko lohko muistiin taulukkoon, käsitellä sitä siellä ja kirjoittaa takaisin yhdellä kertaa.

Alueen lukeminen taulukkoon

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

dataArr on nyt 2-ulotteinen taulukko muistissa: dataArr(1,1) on ORD1001, dataArr(1,4) on "Laptop Stand" ja niin edelleen — VBA-taulukot, jotka luetaan alueelta, ovat 1-pohjaisia, eivät 0-pohjaisia, mikä on yleinen kompastuskivi.

Taulukon käsittely

Tapahtuu tavallisilla silmukoilla — mutta nyt silmukoidaan muistissa, mikä on tuhansia kertoja nopeampaa kuin taulukossa:

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

Taulukon kirjoittaminen takaisin

Jälleen yksi operaatio:

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

Select- ja Activate-komentojen välttäminen

Tallennetut makrot käyttävät näitä usein (Range("A1").Select ja sitten Selection.Font.Bold = True), mutta ne ovat tarpeettomia ja hitaita — jokainen .Select pakottaa Excelin piirtämään näytön uudelleen. Viittaa suoraan alueeseen tai objektiin:

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

Suorituskykytekijät

Näillä on merkitystä, kun dataa on satoja rivejä tai enemmän:

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

Palauta nämä asetukset aina lopuksi — ja käytä virheenkäsittelyä, jotta mahdollinen virhe ei jätä Exceliä näyttöpäivitys pois päältä -tilaan.

Työstetty esimerkki: Tilausten tarkistus

Tämä luku on johtanut tähän:

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

Suorita tämä esimerkkitaululle ja kaikkien viiden tilauksen summien pitäisi olla oikein — muuta ORD1002:n Total-arvo taulukossa vääräksi ja suorita uudelleen nähdäksesi hälytyksen.

Tehtävä

1. Kopioi VerifyOrderTotals työkirjaasi ja suorita se viidelle esimerkkitilaukselle.
2. Riko yhden tilauksen Total-arvo manuaalisesti taulukossa (kirjoita selvästi väärä luku) ja suorita makro uudelleen — varmista, että poikkeama raportoidaan oikealla Order ID:llä.
3. Lisää kymmenen uutta keksittyä tilausta rivin 6 alapuolelle ja varmista, että makro toimii edelleen ilman koodimuutoksia — tämä osoittaa lastRow- ja taulukonkäsittelyn hyödyt.