Có thể bạn quan tâm: Tự Động Viết Hoa Chữ Cái Đầu Trong Excel

Mục lục bài viết

Giới thiệu

Trong môi trường doanh nghiệp và phân tích dữ liệu ngày nay, Excel vẫn là công cụ phổ biến nhất để lưu trữ, tính toán và trình bày thông tin. Để nâng cao năng suất và giảm thiểu thời gian thao tác, nhiều người dùng chuyển sang sử dụng VBA (Visual Basic for Applications). VBA giúp tự động hoá các Terraform, tạo macro, xử lý dữ liệu phức tạp và tương tác với các thành phần của Office. Tuy nhiên, một đoạn mã VBA cũng có thể trở nên chậm chạp nếu không được tối ưu. Bài viết này sẽ trình bày các kỹ thuật, chiến lược và mẹo để cải thiện và tăng tốc các macro VBA trong Excel, nhằm giúp bạn viết code nhanh, hiệu quả và dễ bảo trì hơn.

Có thể bạn quan tâm: Tự Nhảy Số Tiền Bằng Chữ Trong Excel: Cách Chuyển Đổi Số Sang Chữ Một Cách Nhanh Chóng Và Chính Xác

1. Hiểu rõ nguyên nhân gây chậm trong VBA

Trước khi bắt đầu tối ưu, ta cần nhận diện những nguyên nhân chính khiến macro trở nên chậm. Dưới đây là các yếu tố phổ biến:

Nguyên nhân	Mô tả	Ảnh hưởng
Sử dụng quá nhiều `Select/Activate`	Chỉ định vùng, ôੱਕ qua `Select` rồi `Activate` handbag.	Mất thời gian truyền dữ liệu giữa VBA và giao diện, gây lãng phí tài nguyên.
Gọi `WorksheetFunction` hoặc `Application` quá thường xuyên	Mỗi lần gọi hàm có chi phí tính toán cao.	Tăng độ trễ khi xử lý.send ervan.
Không bật `ScreenUpdating` & `EnableEvents`	Khi macro đang chạy, Excel luôn cập nhật màn hình và xử lý sự kiện.	Giảm tốc độ do lặp đi lặp lại cập nhật giao diện.
Với dữ liệu lớn, sử dụng `Range.Value` lặp lại nhiều lần	Mỗi lần truy cập `Range.Value` yêu cầu truyền dữ liệu qua VBA/Excel.	Chi phí truyền dữ liệu lớn làm chậm.
Sử dụng `Variant` thay(\’\’) kiểu dữ liệu cụ thể	Variant linh hoạt nhưng không tối ưu.	Chi phí bộ nhớ và tốc độ xử lý cao hơn so với kiểu dữ liệu cụ thể.
Không reset biến cục bộ	Biến cục bộ không được reset, gây “đọng” dữ liệu cũ.	Có thể gây lỗi logic và làm chậm.
Sử dụng `DoEvents` không cần thiết	Để cho phép xử lý sự kiện, nhưng gây “break” trong vòng lặp.	Gây mất thời gian không deprim.
Không tận dụng `With` block	Đặt nhiều lần tham chiếu tới cùng một đối tượng.	Tốn thời gian xử lý.

Kết luận

Để tối ưu, ta cần tránh hoặc giảm thiểu những hành vi trên. Bây giờ fixe các bước cụ thể.

Có thể bạn quan tâm: Tổng Hợp Nhiều File Excel Thành 1 File: Hướng Dẫn Chi Tiết & Công Cụ Tiện Ích

2. Các bước chuẩn bị khi viết macro

2.1 Bật tùy chọn tối ưu

Sub SetupPerformance() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False
End Sub

ScreenUpdating = False: Ngăn Excel cập nhật màn hình.
EnableEvents = False: Giảm thiểu sự kiện trigger trong khi macro chạy.
Calculation = xlCalculationManual: Tránh tính toán tự động khi thay đổi dữ liệu.
DisplayAlerts = False: Ngăn thông báo cảnh báo.

2.2 Tắt tính năng AutoCorrect (nếu cần)

Trong một số trường hợp, AutoCorrect có thể làm chậm macro khi làm việc với dữ liệu lớn. Bạn có thể tắt tạm thời:

Application.AutoCorrect.CorrectCase = False

2.3 Đặt kiểu dữ liệu cụ thể

Sử dụng Long, Double, String, Boolean thay vì Variant khi meta.

Dim i As Longzbollah
Dim sum As Double

3. Tránh `Select/Activate`

' Sai
Range("A1").Select
Selection.Copy
Worksheets("Sheet2").Activate
Range("B1").PasteSpecial ' Đúng
Worksheets("Sheet1").Range("A1").Copy Destination:=Worksheets("Sheet2").Range("B1")

Mẹo: Sử dụng `With...End With`

With Worksheets("Sheet1") .Range("A1").Copy .Range("B1").PasteSpecial
End With

4. Sử dụng mảng để xử lý dữ liệu

4.1 Đọc dữ liệu vào mảng

Dim data As Variant
data = Worksheets("Data").Range("A1:D1000").Value

Mảng Variant trong VBA thực chất là một đối tượng Variant chứa mảng, nhưng khi truy cập data(i, j) nhanh hơn nhiều so với Cells(i, j).Value.

4.2 X.hot xử lý trong vòng lặp

Dim i As Long
Dim sum As Double
sum = 0 For i = 1 To UBound(data, 1) sum = sum + data(i, 2) 'giả sử cột B là số
Next i

4.3 Ghi dữ liệu trở lại vùng

Worksheets("Result").Range("A1").Resize(UBound(data, 1), 1).Value = data

5. Sử dụng `Application.Index` thay vì vòng lặp

VBA có thể thay thế một số vòng lặp bằng hàm Application.WorksheetFunction.Index. Ví dụ, tính tổng các giá trị trong cột:

Dim total As Double
total = Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("B:B"))

Điều này tránh được vòng lặp thủ công và tận dụng tính toán nhanh của Excel.

6. Thêm và loại roles dữ liệu

Khi làm việc với dữ liệu lớn, thỉnh thoảng cần lọc dữ liệu nhanh. Thay vì viết vòng lặp lọc, sử dụng tính năng AutoFilter:

With Worksheets("Data") .AutoFilterMode = False .Range("A1:D1000").AutoFilter Field:=2, Criteria1:=">100" ' Lấy dữ liệu sau lọc Dim rngFiltered As Range Set rngFiltered = .Range("A2:D1000").SpecialCells(xlCellTypeVisible)
End With

7. Tối ưu vòng lặp

7.1 Sử dụng `Long` thay vì `Integer`

Trong VBA, Integer giới hạn 32k, gây overflow với các vòng lặp lớn. Sử dụng Long luôn an toàn.

7.2 Tránh `DoEvents` trong vòng lặp

DoEvents cho phép hệ thống xử lý các sự kiện, nhưng nó làm gián đoạn vòng lặp, làm chậm. Nếu cần cập nhật UI, chỉ dùng DoEvents một lần sau mỗi một số bước lớn.

7.3 Dùng `Do While` thay vì `For` khi cần thẻ

Nhiều trường hợp Do While nhanh hơn For khi điều kiện kiểm tra phức tạp.

8. Kiểm soát bộ nhớ

8-American: Xóa đối tượng

Set rng = Nothing
Set ws = Nothing

Khi hoàn thành, đặt đối tượng Nothing giúp giải phóng bộ nhớ.

8.2 Giới hạn biến toàn cục

Tránh khai báo biến toàn cục không cần thiết; biến cục bộ được giải phóng tự động khi thủ tục kết thúc.

9. Xử lý sự kiện & lỗi

9.1 Bắt lỗi

On Error GoTo ErrHandler
' code
Exit Sub ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Next

9.2 diario: Thiết lập `Application.Caller`

Nếu macro được gọi từ ô hoặc button, bạn có thể dùng Application.Caller để kiểm tra nguồn gốc, tránh chạy vô tình.

Có thể bạn quan tâm: Tự Động Viết Hoa Đầu Dòng Trong Excel: Hướng Dẫn Chi Tiết

10. Cải thiện hiệu năng khi làm việc với bảng dữ liệu

10.1 Sử dụng `ListObject` (Table)

Dim tbl As ListObject
Set tbl = Worksheets("Data").ListObjects("tblData")

Với ListObject, bạn có thể thao tác nhanh hơn, vì đối tượng này có các thuộc tính nhanh và hỗ trợ truy vấn.

10.2 Tận dụng `DataTable` và `QueryTable`

Nếu dữ liệu được lấy từ nguồn bên ngoài, dùng QueryTable và DataTable giúp Excel tải dữ liệu nhanh hơn.

11. Tối ưu macro lớn

Khi viết macro có nhiều phần, hãy chia nhỏ thành các thủ tục có nhiệm vụ rõ ràng, tránh lặp lại code. Ví dụ:

Sub Main() SetupPerformance ProcessData GenerateReport CleanUp
End Sub

Mỗi thủ tục sẽ thực hiện một nhiệm vụ riêng, giúp dễ debug và tối ưu từng phần.

12. Kiểm tra và đo tốc độ

12.1 Sử dụng `Timer`

Dim startTime As Double
startTime = Timer
' code
MsgBox "Time elapsed: " & Timer - startTime & " seconds"

12.2 Kiểm tra với dữ liệu mẫu

Tạo dữ liệu mẫu lớn (ví dụ 1 triệu dòng) và chạy macro, ghi lại thời gian. Sau khi tối ưu, so sánh thời gian để đánh giá hiệu quả.

13. Mẹo nâng cao: Sử dụng `WithEvents`

Nếu macro cần phản hồi sự kiện, sử dụng WithEvents giúp giảm overhead. Ví dụ:

Private WithEvents ws As Worksheet Private Sub Worksheet_Change(ByVal Target As Range) ' Code
End Sub

14. Kiểm tra cấu trúc macro

Sử dụng Project Explorer trong VBA để xem cấu trúc, tránh đoạn mã lặp lộn.

Đảm bảo không có macro lặp lại cùng một công việc.
Kiểm tra fias Option Explicit để bắt lỗi(format) biến.

15. Tối ưu công thức Excel

Nếu macro liên quan tới công thức, hãy chuyển công thức sang dạng Array Formula hoặc OFFSET để giảm chi phí tính toán. Ví dụ, tính tổng cột một cách nhanh:

sum = WorksheetFunction.Sum(ws.Range("B:B"))

Bằng cách này, Excel sẽ xử lý nhanh hơn so với vòng lặp VBA.

16. Sử dụng `Application.ScreenUpdating = False` trong thời gian ngắn

Trong macro dài, hãy tắt ScreenUpdating chỉ khi cần. Sau khi hoàn thành, bật lại:

Application.ScreenUpdating = False
' code
Application.ScreenUpdating = True

17. Sử dụng `Application.Calculation = xlCalculationManual`

Trong khi macro thực hiện nhiều thay đổi, tắt tính toán tự động giúp tránh tính toán lặp lại:

Application.Calculation = xlCalculationManual
' code
Application.Calculation = xlCalculationAutomatic

18. Kiểm tra `WorksheetFunction` và `Application.WorksheetFunction`

Trong một số trường hợp, WorksheetFunction hỗ trợ các hàm nhanh hơn. Nếu bạn cần tính toán phức tạp, hãy dùng WorksheetFunction thay vì viết vòng lặp.

19. Tối ưu hóa `Range.Value`

Nếu macro cần truy cập nhiều ô, hãy đọc toàn bộ vùng một lần:

Dim arr As Variant
arr = ws.Range("A1:C1000").Value

Sau đó làm việc với arr. Khi ghi lại, dùng một lần:

ws.Range("A1:C1000").Value = arr

20. Xử lý dữ liệu dạng TextByColumns

Nếu macro cần chuyển đổi cột văn bản thành nhiều cột, dùng TextToColumns nhanh hơn vòng lặp thủ công:

ws.Range("A1:A1000").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False

21. Tạo hàm hỗ trợ

Khi viết macro, hãy tạo các hàm hỗ trợ để tái sử dụng:

Function GetColumnIndex(ws店 As Worksheet, header As String) As Long Dim rng As Range Set rng = ws店.Rows(1).Find(header, LookIn:=xlValues, LookAt:=xlWhole) If Not rng Is Nothing Then GetColumnIndex = rng.Column Else GetColumnIndex = -1 End If
End Function

22. Tối ưu hóa `For Each` vs `For`

For Each Cartagena nhanh hơn khi lặp qua các đối tượng Range. Tuy nhiên, For i = 1 to N nhanh hơn khi lặp qua mảng.

23. Sử dụng `Performance Profiling`

-قاط: Sử dụng Debug.Print để in thời gian thực thi từng phần.
– Sử dụng Profile trong VB Editor: Debug > Step Into (F8) để xem dòng code thực thi.

24. Áp dụng `Parallel Computing` (nếu có)

Trong các phiên bản Excel mới hơn, bạn có thể tận dụng tính năng Parallel trong Power Query hoặc Power Pivot. Mặc dù không trực tiếp trong VBA, nhưng bạn có thể xuất dữ liệu ra Power Query để thực hiện tính toán nhanh.

25. Thư viện hỗ trợ

Scripting.Dictionary: Thay vì sử dụng mảng để lưu trữ dữ liệu tạm thời, Dictionary nhanh hơn và dễ quản lý.
Collection: Dùng khi cần lưu trữ các đối tượng.

26. Tối ưu hóa macro khi làm việc với file lớn

Đọc dữ liệu từ file lớn bằng Open và Line Input thay vì Workbooks.Open nếu dữ liệu trong định dạng .txt hoặc .csv.
Khi ghi file, dùng Print # hoặc Write #.

27. Xác minh lỗi thường gặp

Lỗi	Nguyên nhân	Cách khắc phục
Overflow	Biến kiểu `Integer` quá lớn	Dùng `Long`
Type mismatch	Định dạng dữ liệu sai	Kiểm tra kiểu dữ liệu
Object variable not set	Đối tượng chưa được khởi tạo	Khởi tạo `Set`
Application-defined or object-defined error	Mảng ngoài giới hạn	Kiểm tra `UBound` và `LBound`

28. Kết luận

Hiệu suất VBA không chỉ là viết code nhanh hơn, mà còn là viết code tối ưu, dễ bảo trì và dễ mở rộng.
Việc tránh Select/Activate, sử dụng mảng, tắt tính năng tự động, quản lý biến và sự kiện, và tận dụng các hàm Excel trong VBA sẽ mang lại sự khác biệt rõ rệt trong thời gian thực thi, đặc biệt là khi làm việc với dữ liệu lớn.

Bằng inmates những chiến lược và mẹo ở trên, bạn không chỉ viết macro nhanh hơn mà còn có thể bảo trì và mở rộng công việc một cách linh hoạt hơn. Hãy thử áp dụng từng bước, đo lường thời gian thực thi, và điều chỉnh cho phù hợp với công việc của bạn. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.