Hướng dẫn này sẽ thảo luận về cách tăng tốc Macro VBA và các phương pháp hay nhất khác về VBA.
Cài đặt để tăng tốc mã VBA
Dưới đây, bạn sẽ tìm thấy một số mẹo để tăng tốc mã VBA của mình. Các mẹo được sắp xếp lỏng lẻo theo mức độ quan trọng.
Cách dễ nhất để cải thiện tốc độ mã VBA của bạn là tắt Cập nhật màn hình và tắt Tính toán tự động. Các cài đặt này nên được tắt trong tất cả các quy trình lớn.
Tắt cập nhật màn hình
Theo mặc định, Excel sẽ hiển thị các thay đổi đối với (các) sổ làm việc trong thời gian thực khi mã VBA chạy. Điều này gây ra sự chậm lại lớn về tốc độ xử lý vì Excel hầu hết diễn giải và hiển thị các thay đổi cho mỗi dòng mã.
Để tắt Cập nhật màn hình:
| 1 | Application.ScreenUpdating = Sai |
Khi kết thúc macro, bạn nên bật lại Cập nhật màn hình:
| 1 | Application.ScreenUpdating = True |
Trong khi mã của bạn đang chạy, bạn có thể cần phải "làm mới" màn hình. Không có lệnh "làm mới". Thay vào đó, bạn cần bật lại Cập nhật màn hình và tắt lại.
Đặt tính toán thành thủ công
Bất cứ khi nào một giá trị ô được thay đổi, Excel phải tuân theo “cây tính toán” để tính toán lại tất cả các ô phụ thuộc. Ngoài ra, bất cứ khi nào thay đổi công thức, Excel sẽ cần cập nhật “cây tính toán” ngoài việc tính toán lại tất cả các ô phụ thuộc. Tùy thuộc vào kích thước sổ làm việc của bạn, những tính toán lại này có thể khiến macro của bạn chạy chậm một cách bất hợp lý.
Để đặt Tính toán thành Thủ công:
| 1 | Application.Calculation = xlManual |
Để tính toán lại toàn bộ sổ làm việc theo cách thủ công:
| 1 | Tính toán |
Lưu ý rằng bạn cũng có thể chỉ tính toán một trang tính, phạm vi hoặc ô riêng lẻ, nếu cần thiết để cải thiện tốc độ.
Để khôi phục các Tính toán Tự động (ở cuối quy trình của bạn):
| 1 | Application.Calculation = xlAutomatic |
Quan trọng! Đây là một cài đặt Excel. Nếu bạn không đặt lại các phép tính thành tự động, sổ làm việc của bạn sẽ không tính toán lại cho đến khi bạn yêu cầu.
Bạn sẽ thấy những cải tiến lớn nhất từ các cài đặt trên, nhưng có một số cài đặt khác có thể tạo ra sự khác biệt:
Tắt sự kiện
Sự kiện là "yếu tố kích hoạt" gây ra thủ tục sự kiện chạy. Ví dụ bao gồm: khi bất kỳ ô nào trên trang tính thay đổi, khi trang tính được kích hoạt, khi sổ làm việc được mở, trước khi sổ làm việc được lưu, v.v.
Việc tắt các sự kiện có thể gây ra những cải thiện nhỏ về tốc độ khi bất kỳ macro nào chạy, nhưng tốc độ cải thiện có thể lớn hơn nhiều nếu sổ làm việc của bạn sử dụng các sự kiện. Và trong một số trường hợp, việc vô hiệu hóa các sự kiện là cần thiết để tránh tạo ra các vòng lặp vô tận.
Để tắt các sự kiện:
| 1 | Application.EnableEvents = Sai |
Để bật lại sự kiện:
| 1 | Application.EnableEvents = True |
Tắt chức năng ngắt trang
Tắt chức năng ngắt trang có thể hữu ích trong một số trường hợp nhất định:
- Trước đây bạn đã đặt thuộc tính Thiết lập trang cho trang tính có liên quan và thủ tục VBA của bạn sửa đổi các thuộc tính của nhiều hàng hoặc cột
- HOẶC Quy trình VBA của bạn buộc Excel phải tính toán ngắt trang (hiển thị Xem trước khi in hoặc sửa đổi bất kỳ thuộc tính nào của Thiết lập trang).
Để vô hiệu hóa PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Sai |
Để kích hoạt lại PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Các phương pháp hay nhất để cải thiện tốc độ VBA
Tránh kích hoạt và chọn
Khi ghi Macro, bạn sẽ thấy nhiều phương pháp Kích hoạt và Chọn:
| 12345678 | Sub Slow_Example ()Trang tính ("Trang tính2"). ChọnPhạm vi ("D9"). ChọnActiveCell.FormulaR1C1 = "ví dụ"Phạm vi ("D12"). ChọnActiveCell.FormulaR1C1 = "demo"Phạm vi ("D13"). ChọnKết thúc Sub |
Việc kích hoạt và lựa chọn các đối tượng thường là không cần thiết, chúng tạo thêm sự lộn xộn cho mã của bạn và rất tốn thời gian. Bạn nên tránh các phương pháp này khi có thể.
Ví dụ cải tiến:
| 1234 | Sub Fast_Example ()Trang tính ("Trang tính2"). Phạm vi ("D9"). Công thứcR1C1 = "ví dụ"Trang tính ("Trang tính2"). Phạm vi ("D12"). Công thứcR1C1 = "bản trình diễn"Kết thúc Sub |
Tránh sao chép và dán
Sao chép yêu cầu bộ nhớ đáng kể. Rất tiếc, bạn không thể yêu cầu VBA xóa bộ nhớ trong. Thay vào đó, Excel sẽ xóa bộ nhớ trong của nó vào những khoảng thời gian (dường như) cụ thể. Vì vậy, nếu bạn thực hiện nhiều thao tác sao chép và dán, bạn có nguy cơ chiếm quá nhiều bộ nhớ, điều này có thể làm chậm đáng kể mã của bạn hoặc thậm chí làm hỏng Excel.
Thay vì sao chép và dán, hãy cân nhắc thiết lập các thuộc tính giá trị của ô.
| 123456789 | Sub CopyPaste ()'Chậm hơnPhạm vi ("a1: a1000"). Sao chép phạm vi ("b1: b1000")'Nhanh hơnPhạm vi ("b1: b1000"). Giá trị = Phạm vi ("a1: a1000"). Giá trịKết thúc Sub |
Sử dụng vòng lặp Cho Mỗi thay vì Vòng lặp Cho
Khi lặp qua các đối tượng, vòng lặp For Each nhanh hơn vòng lặp For. Thí dụ:
Vòng lặp này:
| 123456 | Vòng lặp phụ1 ()mờ tôi là Phạm viĐối với i = 1 đến 100Ô (i, 1) .Value = 1Tiếp theo tôiKết thúc Sub |
| 123456 | Vòng lặp phụ2 ()Làm mờ ô dưới dạng phạm viĐối với mỗi ô trong phạm vi ("a1: a100")cell.Value = 1Ô tiếp theoKết thúc Sub |
Khai báo các biến / Sử dụng tùy chọn rõ ràng
VBA không yêu cầu bạn khai báo các biến của mình, trừ khi bạn thêm Option Explicit vào đầu mô-đun của mình:| 1 | Tùy chọn rõ ràng |
| 1234 | Tùy chọn phụExplicit ()var1 = 10MsgBox varlKết thúc Sub |
Sử dụng với - Kết thúc với câu lệnh
Nếu bạn tham chiếu các đối tượng giống nhau nhiều lần (ví dụ: Phạm vi, Trang tính, Sổ làm việc), hãy xem xét sử dụng Câu lệnh Với. Quá trình xử lý nhanh hơn, có thể làm cho mã của bạn dễ đọc hơn và đơn giản hóa mã của bạn.Với ví dụ câu lệnh:| 12345678 | Sub Faster_Example ()Với Trang tính ("Trang tính2").Range ("D9"). FormulaR1C1 = "ví dụ".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueKết thúc vớiKết thúc Sub |
| 123456 | Sub Slow_Example ()Trang tính ("Trang tính2"). Phạm vi ("D9"). Công thứcR1C1 = "ví dụ"Trang tính ("Trang tính2"). Phạm vi ("D12"). Công thứcR1C1 = "bản trình diễn"Trang tính ("Trang 2"). Phạm vi ("D9"). Font.Bold = TrueTrang tính ("Trang tính2"). Phạm vi ("D12"). Font.Bold = TrueKết thúc Sub |
Mẹo thực hành tốt nhất nâng cao
Bảo vệ UserInterfaceOnly
Bạn nên bảo vệ trang tính của mình khỏi việc chỉnh sửa các ô không được bảo vệ để ngăn người dùng cuối (hoặc bạn!) Vô tình làm hỏng sổ làm việc. Tuy nhiên, điều này cũng sẽ bảo vệ (các) trang tính không cho phép VBA thực hiện thay đổi. Vì vậy, bạn phải bỏ bảo vệ và bảo vệ lại các trang tính, rất mất thời gian khi thực hiện trên nhiều trang tính.
| 12345 | Sub UnProtectSheet ()Trang tính (“sheet1”). Bỏ bảo vệ ”mật khẩu”'Chỉnh sửa Trang tính1Trang tính (“sheet1”). Bảo vệ ”mật khẩu”Kết thúc Sub |
Thay vào đó, bạn có thể bảo vệ trang tính bằng cách đặt UserInterfaceOnly: = True. Điều này cho phép VBA thực hiện các thay đổi đối với trang tính, trong khi vẫn bảo vệ chúng khỏi người dùng.
| 1 | Trang tính (“sheet1”). Bảo vệ Mật khẩu: = "mật khẩu", UserInterFaceOnly: = True |
Quan trọng! UserInterFaceOnly đặt lại thành False mỗi khi sổ làm việc mở ra. Vì vậy, để sử dụng tính năng tuyệt vời này, bạn sẽ cần sử dụng các sự kiện Workbook_Open hoặc Auto_Open để đặt cài đặt mỗi khi mở sổ làm việc.
Đặt mã này trong mô-đun Thisworkbook:
| 123456 | Private Sub Workbook_Open ()Dim ws As WorksheetĐối với mỗi ws trong Worksheetsws.Protect Password: = "password", UserInterFaceOnly: = TrueTuần tiếp theoKết thúc Sub |
hoặc mã này trong bất kỳ mô-đun thông thường nào:
| 123456 | Private Sub Auto_Open ()Dim ws As WorksheetĐối với mỗi ws trong Worksheetsws.Protect Password: = "password", UserInterFaceOnly: = TrueTuần tiếp theoKết thúc Sub |
Sử dụng Mảng để Chỉnh sửa Dải rộng lớn
Có thể rất tốn thời gian để thao tác với các phạm vi ô lớn (Ví dụ: 100.000+). Thay vì lặp qua các phạm vi ô, thao tác với từng ô, bạn có thể tải các ô vào một mảng, xử lý từng mục trong mảng và sau đó xuất mảng trở lại các ô ban đầu của chúng. Việc tải các ô vào các mảng để thao tác có thể nhanh hơn nhiều.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Làm mờ ô dưới dạng phạm viDim tStart As DoubletStart = Bộ hẹn giờĐối với mỗi ô trong phạm vi ("A1: A100000")cell.Value = cell.Value * 100Ô tiếp theoDebug.Print (Hẹn giờ - tStart) & "giây"Kết thúc SubSub LoopArray ()Dim arr As VariantLàm mờ mục dưới dạng biến thểDim tStart As DoubletStart = Bộ hẹn giờarr = Range ("A1: A100000"). Giá trịĐối với mỗi mục Trong arritem = item * 100Mục tiếp theoPhạm vi ("A1: A100000"). Giá trị = arrDebug.Print (Hẹn giờ - tStart) & "giây"Kết thúc Sub |
