До цього моменту всі операції виконувалися з однією клітинкою за раз — це прийнятно для п’яти замовлень, але надзвичайно повільно для п’ятдесяти тисяч. Рішення полягає в тому, щоб припинити працювати з аркушем по клітинці, а замість цього перемістити весь блок у масив у пам’яті, обробити його там і записати назад за один раз.

Зчитування діапазону в масив

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

dataArr тепер є двовимірним масивом у пам’яті: dataArr(1,1) — це ORD1001, dataArr(1,4) — це "Laptop Stand" і так далі — масиви VBA, зчитані з діапазону, мають індексацію з 1, а не з 0, що часто призводить до помилок.

Обробка масиву

Використовуються звичайні цикли — але тепер ви перебираєте дані в пам’яті, що у тисячі разів швидше, ніж по аркушу:

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

Запис масиву назад

Знову одна операція:

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

Уникнення Select та Activate

Записані макроси часто використовують ці команди (Range("A1").Select, потім Selection.Font.Bold = True), але вони непотрібні та уповільнюють виконання — кожен .Select змушує Excel перемальовувати екран. Звертайтеся до діапазону або об'єкта напряму:

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

Міркування щодо продуктивності

Це стає важливим, коли даних більше кількох сотень рядків:

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

Завжди відновлюйте ці налаштування наприкінці — і обгорніть відновлення в обробку помилок, щоб у разі збою Excel не залишився з вимкненим оновленням екрана.

Практичний приклад: перевірка цілісності замовлень

У цьому розділі ми поступово підводили до такого прикладу:

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

Запустіть цей макрос на прикладній таблиці — він має показати, що всі п’ять підсумків правильні. Спробуйте змінити Total для ORD1002 на неправильне значення та перезапустіть макрос, щоб побачити сповіщення про помилку.

Завдання

1. Скопіюйте VerifyOrderTotals у свою книгу та запустіть його для п’яти зразків замовлень.
2. Вручну змініть Total для одного із замовлень на аркуші (введіть явно неправильне число) та перезапустіть — переконайтеся, що невідповідність відображається з правильним Order ID.
3. Додайте ще десять рядків вигаданих замовлень під рядком 6 і переконайтеся, що макрос працює без змін у коді — це результат використання lastRow та обробки масивів.