Elaborazione Efficiente Dei Dati

Finora tutto ha funzionato una cella alla volta — va bene per cinque ordini, ma è estremamente lento per cinquantamila. La soluzione è smettere di lavorare cella per cella sul foglio di lavoro e invece spostare l'intero blocco in un array in memoria, elaborarlo lì e poi scriverlo nuovamente in un'unica operazione.

Lettura di un intervallo in un array

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

dataArr è ora un array 2D in memoria: dataArr(1,1) è ORD1001, dataArr(1,4) è "Laptop Stand", e così via — gli array VBA letti da un intervallo sono indicizzati a partire da 1, non da 0, il che spesso causa confusione.

Elaborazione dell'array

Avviene con normali cicli — ma ora si cicla sulla memoria, che è migliaia di volte più veloce rispetto al ciclo sul foglio di lavoro:

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

Scrittura dell'array sul foglio

Ancora una volta, un'unica operazione:

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

Evitare Select e Activate

Le macro registrate fanno largo uso di questi comandi (Range("A1").Select seguito da Selection.Font.Bold = True), ma sono inutili e rallentano l'esecuzione — ogni .Select costringe Excel a ridisegnare lo schermo. Fare riferimento direttamente all'intervallo o all'oggetto:

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

Considerazioni sulle prestazioni

Questi aspetti diventano rilevanti quando i dati superano alcune centinaia di righe:

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

Ripristinare sempre queste impostazioni alla fine — e racchiudere il ripristino in una gestione degli errori per evitare che un arresto anomalo lasci Excel con l'aggiornamento dello schermo disattivato.

Esempio pratico: il Controllo Integrità Ordini

Questo capitolo porta a questo esempio:

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

Esegui questo codice sulla tabella di esempio: dovrebbe segnalare che tutti e cinque i totali sono corretti — prova a modificare il Total di ORD1002 sul foglio con un valore errato e riesegui per vedere l'avviso.

Attività

1. Copiare VerifyOrderTotals nel proprio file di lavoro ed eseguirlo sui cinque ordini di esempio.
2. Modificare manualmente il campo Total di un ordine sul foglio (inserire un numero evidentemente errato) e rieseguire — verificare che la discrepanza venga segnalata con il corretto Order ID.
3. Aggiungere dieci righe di dati ordine inventati sotto la riga 6 e verificare che la macro funzioni ancora senza modifiche al codice — questo dimostra l'efficacia di lastRow e dell'elaborazione tramite array.