Tối Ưu Tốc Độ VBA: 10 Kỹ Thuật Khiến Macro Chạy Nhanh Gấp 10 Lần

1. Tại Sao VBA Chạy Chậm? #

Đa số VBA chạy chậm vì tương tác trực tiếp với Excel quá nhiều lần. Mỗi lần đọc/ghi một cell, Excel phải render lại giao diện, recalculate formulas, và redraw screen. Bí quyết tối ưu là giảm thiểu số lần tương tác này xuống mức tối thiểu.

Một macro xử lý 10,000 dòng có thể mất 30 giây nếu viết sai cách, nhưng chỉ 0.5 giây nếu áp dụng đúng kỹ thuật. Dưới đây là 10 kỹ thuật quan trọng nhất mà bất kỳ VBA developer nào cũng cần biết.

2. Kỹ Thuật 1: Tắt ScreenUpdating, Events Và Calculation #

Đây là kỹ thuật đầu tiên và cơ bản nhất. Tắt ScreenUpdating ngăn Excel render lại màn hình sau mỗi thay đổi. Tắt EnableEvents ngăn trigger các event handler. Chuyển Calculation sang Manual ngăn recalc formulas.

Sub OptimizedMacro()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' Code chính ở đây
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Lưu ý quan trọng: luôn bật lại các setting trong block Finally hoặc Error handler, nếu không Excel sẽ bị treo ở trạng thái Manual Calculation.

3. Kỹ Thuật 2: Dùng Array Thay Vì Đọc/Ghi Từng Cell #

Đây là kỹ thuật quan trọng nhất. Thay vì đọc cell-by-cell (mỗi lần = 1 COM call), hãy load toàn bộ range vào Variant array, xử lý trong memory, rồi ghi ngược lại một lần. Tốc độ cải thiện từ 50x đến 500x.

Sub FastArrayProcessing()
    Dim data As Variant
    data = Range("A1:Z10000").Value  ' Load 1 lần vào RAM
    
    Dim i As Long
    For i = 1 To UBound(data, 1)
        data(i, 3) = data(i, 1) * data(i, 2)  ' Xử lý trong RAM
    Next i
    
    Range("A1:Z10000").Value = data  ' Ghi 1 lần từ RAM
End Sub

4. Kỹ Thuật 3: Tránh Select, Activate, ActiveCell #

Không bao giờ dùng Select hay Activate trong VBA production code. Chúng bắt Excel phải scroll đến cell, focus, highlight — rất chậm và dễ lỗi nếu user click chuột. Thay vào đó, tham chiếu trực tiếp đến Range, Sheet, Workbook.

' CHẬM — tuyệt đối tránh:
Sheets("Data").Select
Range("A1").Select
ActiveCell.Value = "Hello"

' NHANH — tham chiếu trực tiếp:
Sheets("Data").Range("A1").Value = "Hello"

5. Kỹ Thuật 4: Sử Dụng With Statement #

With Statement giảm số lần Excel phải resolve object reference. Thay vì gọi Worksheets("Data").Range("A1") lặp lại 5 lần (5 COM calls), dùng With chỉ cần 1 lần resolve.

' Chậm (3 COM calls):
Worksheets("Data").Range("A1").Font.Bold = True
Worksheets("Data").Range("A1").Font.Size = 14
Worksheets("Data").Range("A1").Interior.Color = vbYellow

' Nhanh (1 COM call):
With Worksheets("Data").Range("A1")
    .Font.Bold = True
    .Font.Size = 14
    .Interior.Color = vbYellow
End With

6. Kỹ Thuật 5: Dùng Long Thay Vì Integer #

Integer trong VBA chỉ chứa giá trị 16-bit (-32,768 đến 32,767) nhưng CPU 64-bit xử lý Long (32-bit) nhanh hơn vì không cần convert. Hơn nữa, Excel có thể có hơn 32,767 dòng nên Integer gây overflow.

' Chậm + nguy hiểm overflow:
Dim i As Integer
For i = 1 To 100000  ' Lỗi Overflow!

' Nhanh + an toàn:
Dim i As Long
For i = 1 To 1048576  ' OK, Long chứa được

7. Kỹ Thuật 6: Tránh Variant — Khai Báo Kiểu Dữ Liệu Rõ Ràng #

Variant là kiểu mặc định nhưng chậm nhất vì VBA phải check kiểu runtime mỗi lần sử dụng. Luôn khai báo As String, As Long, As Double cụ thể. Dùng Option Explicit để bắt buộc khai báo.

Option Explicit  ' Bắt buộc khai báo biến

' Chậm:
Dim x, y, z  ' Tất cả là Variant

' Nhanh:
Dim x As Long, y As Double, z As String

8. Kỹ Thuật 7: Dùng StringBuilder (Mid$) Thay Concatenation #

Nối chuỗi bằng & trong vòng lặp tạo ra string mới mỗi lần → O(n²) memory allocation. Với chuỗi lớn, dùng kỹ thuật Mid$ pre-allocate buffer hoặc Join array.

' Chậm O(n²) — string mới mỗi lần:
Dim result As String
For i = 1 To 100000
    result = result & "Line " & i & vbCrLf  ' Rất chậm!
Next

' Nhanh O(n) — dùng Array + Join:
Dim arr() As String
ReDim arr(1 To 100000)
For i = 1 To 100000
    arr(i) = "Line " & i
Next
result = Join(arr, vbCrLf)  ' 1 lần allocate

9. Kỹ Thuật 8: Tắt StatusBar Và Giảm DoEvents #

Application.StatusBar = "Processing..." mỗi iteration rất chậm vì phải render UI. Nếu cần progress, chỉ update mỗi 100 hoặc 1000 iterations. DoEvents cho phép user cancel nhưng cực chậm — chỉ dùng khi thật cần thiết.

' Chậm — update mỗi dòng:
For i = 1 To 100000
    Application.StatusBar = "Row " & i & "/100000"
    DoEvents  ' Rất chậm!
Next

' Nhanh — update mỗi 1000 dòng:
For i = 1 To 100000
    If i Mod 1000 = 0 Then
        Application.StatusBar = "Row " & i & "/100000"
    End If
Next
Application.StatusBar = False

10. Kỹ Thuật 9: Dùng Dictionary Thay Vì Nested Loop #

Lookup bằng nested For loop là O(n²). Dùng Scripting.Dictionary để lookup O(1) — giống VLOOKUP nhưng nhanh hơn hàng ngàn lần khi dữ liệu lớn.

' Chậm O(n²):
For i = 1 To 10000
    For j = 1 To 10000
        If data(j, 1) = key(i) Then result(i) = data(j, 2)
    Next j
Next i

' Nhanh O(n) — Dictionary:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
For j = 1 To 10000
    dict(data(j, 1)) = data(j, 2)  ' Build index
Next j
For i = 1 To 10000
    If dict.Exists(key(i)) Then result(i) = dict(key(i))  ' O(1) lookup
Next i

11. Kỹ Thuật 10: Batch Database Operations #

Khi làm việc với database (ADO/DAO) hoặc external files, tránh gửi từng query riêng lẻ. Gom lại thành 1 batch INSERT hoặc dùng transaction. Tương tự khi copy/paste giữa workbooks, nên copy range lớn thay vì cell-by-cell.

' Chậm — 1000 queries riêng lẻ:
For i = 1 To 1000
    conn.Execute "INSERT INTO tbl VALUES(" & data(i) & ")"
Next i

' Nhanh — 1 transaction batch:
conn.Execute "BEGIN TRANSACTION"
For i = 1 To 1000
    conn.Execute "INSERT INTO tbl VALUES(" & data(i) & ")"
Next i
conn.Execute "COMMIT TRANSACTION"

12. Tổng Kết: Checklist Tối Ưu VBA #

Trước khi chạy bất kỳ macro nào, hãy kiểm tra checklist: 1) ScreenUpdating = False ✓ 2) Array thay Range ✓ 3) Không Select/Activate ✓ 4) With Statement ✓ 5) Long thay Integer ✓ 6) Khai báo kiểu rõ ràng ✓ 7) Join/Mid$ thay & ✓ 8) StatusBar mỗi 1000 dòng ✓ 9) Dictionary thay nested loop ✓ 10) Batch operations ✓

Áp dụng đầy đủ 10 kỹ thuật này, macro VBA của bạn có thể chạy nhanh gấp 10 đến 100 lần so với code chưa tối ưu. Kỹ thuật quan trọng nhất để bắt đầu là Array (kỹ thuật 2) và tắt ScreenUpdating (kỹ thuật 1).

📌 Lưu ý: File demo .xlsm download từ mạng cần bỏ chặn macro trước khi sử dụng. Xem hướng dẫn chi tiết: Cách Bỏ Chặn Macro Excel.