10 Cách để tăng tốc Macro của bạn - núm vú

Vì các macro Excel của bạn ngày càng trở nên mạnh mẽ và phức tạp, bạn có thể thấy rằng chúng sẽ bị mất hiệu suất. Khi thảo luận các macro, từ hiệu suất thường đồng nghĩa với tốc độ . Tốc độ nhanh như thế nào các thủ tục VBA của bạn thực hiện nhiệm vụ dự định của họ. Dưới đây là 10 cách để giữ macro Excel của bạn chạy ở mức hiệu suất tối ưu.

Hết bảng tính

Bạn có biết rằng mỗi khi một ô ảnh hưởng đến bất kỳ công thức nào trong bảng tính của bạn được thay đổi hay thao tác, Excel sẽ tính lại toàn bộ bảng tính? Trong các bảng tính có nhiều công thức, hành vi này có thể làm chậm các macro của bạn.

Bạn có thể sử dụng Ứng dụng. Tài sản tính toán để cho Excel chuyển sang chế độ tính toán thủ công. Khi bảng tính ở chế độ tính toán thủ công, bảng tính sẽ không tính lại cho đến khi bạn kích hoạt tính toán một cách rõ ràng bằng cách nhấn phím F9.

Đặt Excel vào chế độ tính toán thủ công, chạy mã của bạn và sau đó chuyển sang chế độ tính toán tự động.

Ứng dụng phụ Macro1 (). Tính toán = xlCalculationManual 'Đặt mã macro của bạn ở đây Ứng dụng. Tính toán = xlCalculationAutomatic End Sub

Thiết lập chế độ tính toán trở lại xlCalculationAutomatic sẽ tự động kích hoạt tính toán lại của bảng tính, do đó không cần nhấn phím F9 sau khi macro của bạn chạy.

Vô hiệu hoá việc cập nhật trang

Bạn có thể nhận thấy rằng khi macro của bạn chạy, màn hình của bạn sẽ nhấp nháy một cách hợp lý. Sự nhấp nháy này là Excel đang cố gắng vẽ lại màn hình để hiển thị trạng thái hiện tại của bảng tính. Thật không may, mỗi lần Excel vẽ lại màn hình, nó chiếm bộ nhớ tài nguyên.

Bạn có thể sử dụng Ứng dụng. Thuộc tính ScreenUpdating để tắt cập nhật màn hình cho đến khi macro của bạn đã hoàn tất. Vô hiệu hoá màn hình cập nhật tiết kiệm thời gian và tài nguyên, cho phép macro của bạn chạy nhanh hơn một chút. Sau khi mã macro của bạn đã chạy xong, bạn có thể bật lại màn hình.

Ứng dụng phụ Macro1 (). Tính = xlCalculationManual Application. ScreenUpdating = False 'Đặt mã macro của bạn ở đây Ứng dụng. Tính toán = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Sau khi bạn thiết lập thuộc tính ScreenUpdating trở lại True, Excel sẽ tự động kích hoạt vẽ lại màn hình.

Tắt thanh trạng thái cập nhật

Thanh trạng thái Excel, xuất hiện ở dưới cùng của cửa sổ Excel, thường hiển thị tiến bộ của một số hành động nhất định trong Excel.Nếu macro của bạn đang làm việc với nhiều dữ liệu, thanh trạng thái sẽ chiếm một số tài nguyên.

Điều quan trọng cần lưu ý là việc tắt cập nhật màn hình tách rời khỏi hiển thị thanh trạng thái. Thanh trạng thái sẽ tiếp tục được cập nhật ngay cả khi bạn tắt cập nhật màn hình. Bạn có thể sử dụng Ứng dụng. DisplayStatusBar bất động sản để tạm thời vô hiệu hóa bất kỳ cập nhật thanh trạng thái, tiếp tục cải thiện hiệu suất của macro của bạn:

Sub Macro1 () ứng dụng. Tính = xlCalculationManual Application. ScreenUpdating = Ứng dụng Sai. DisplayStatusBar = False 'Đặt mã macro của bạn ở đây Ứng dụng. Tính toán = xlCalculationAutomatic Application. ScreenUpdating = Ứng dụng Đúng. DisplayStatusBar = True End Sub

Cho biết Excel để bỏ qua sự kiện

Bạn có thể thực hiện macro như các thủ tục sự kiện, nói với Excel để chạy mã nhất định khi một bảng tính hoặc bảng tính thay đổi.

Đôi khi, các macro tiêu chuẩn thực hiện các thay đổi sẽ kích hoạt một thủ tục sự kiện. Ví dụ: nếu bạn có macro chuẩn thao tác một số ô trên Sheet1, mỗi lần một ô trên tờ đó được thay đổi, macro của bạn phải tạm dừng trong khi sự kiện Worksheet_Change chạy.

Bạn có thể thêm một mức độ tăng hiệu năng khác bằng cách sử dụng thuộc tính EnableEvents để cho Excel bỏ qua các sự kiện trong khi macro của bạn chạy.

Đặt thuộc tính EnableEvents thành False trước khi chạy macro của bạn. Sau khi mã macro của bạn kết thúc, bạn có thể thiết lập thuộc tính EnableEvents trở lại True.

Ứng dụng phụ Macro1 (). Tính = xlCalculationManual Application. ScreenUpdating = Ứng dụng Sai. DisplayStatusBar = Ứng dụng Sai. EnableEvents = False 'Đặt mã macro của bạn ở đây Ứng dụng. Tính toán = xlCalculationAutomatic Application. ScreenUpdating = Ứng dụng Đúng. DisplayStatusBar = Ứng dụng Đúng. EnableEvents = True End Sub

Hạn ngắt trang

Mỗi lần macro của bạn sửa đổi số hàng, sửa đổi số cột hoặc thay đổi thiết lập trang của một bảng tính, Excel sẽ buộc phải tính toán lại thời gian trang được hiển thị trên tờ.

Bạn có thể tránh hành vi này bằng cách đơn giản giấu trang bị ngắt trước khi bắt đầu macro của bạn.

Đặt thuộc tính DisplayPageBreaks thành False để ẩn các ngắt trang. Nếu bạn muốn tiếp tục hiển thị các lần ngắt trang sau khi vĩ mô của bạn chạy, hãy đặt thuộc tính SheetPageBreaks trở lại thành True.

Ứng dụng phụ Macro1 (). Tính = xlCalculationManual Application. ScreenUpdating = Ứng dụng Sai. DisplayStatusBar = Ứng dụng Sai. EnableEvents = False Activesheet. DisplayPageBreaks = Sai 'Đặt mã macro của bạn ở đây Ứng dụng. Tính toán = xlCalculationAutomatic Application. ScreenUpdating = Ứng dụng Đúng. DisplayStatusBar = Ứng dụng Đúng. EnableEvents = Bảng hiệu hoạt động thực. DisplayPageBreaks = True End Sub

Tạm dừng bảng cập nhật

Nếu macro của bạn thao tác các bảng trụ có chứa các nguồn dữ liệu lớn, bạn có thể gặp sự cố khi làm những việc như tự động thêm hoặc di chuyển các trường khung.

Bạn có thể cải thiện hiệu suất của macro bằng cách tạm ngừng tính toán lại bảng xoay cho đến khi tất cả thay đổi lĩnh vực trục đã được thực hiện. Đơn giản chỉ cần đặt PivotTable. ManualUpdate tài sản để Đúng để hoán tính lại, chạy mã vĩ mô của bạn, và sau đó thiết lập các PivotTable. Tài sản ManualUpdate trở về False để kích hoạt tính toán lại.

Tiểu Macro1 () ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = True 'Đặt mã macro của bạn ở đây ActiveSheet. PivotTables ("PivotTable1"). ManualUpdate = False End Sub

Điều quan trọng là phải nhớ rằng mặc dù Macro Recorder tiết kiệm thời gian bằng cách viết mã VBA cho bạn, nhưng nó không phải lúc nào cũng viết mã hiệu quả nhất. Một ví dụ điển hình là cách Macro Recorder ghi lại bất kỳ hành động sao chép và dán nào bạn thực hiện trong khi ghi âm.

Bạn có thể cho macro của bạn tăng nhẹ bằng cách cắt bỏ người trung gian và thực hiện một bản sao trực tiếp từ một ô đến một tế bào đích. Mã thay thế này sử dụng đối số Đích để bỏ qua bộ nhớ tạm và sao chép nội dung của ô A1 trực tiếp vào ô B1.

Phạm vi ("A1"). Sao chép điểm đến: = Phạm vi ("B1")

Nếu bạn chỉ cần sao chép các giá trị (không phải định dạng hoặc công thức), bạn có thể cải thiện hiệu suất hơn nữa bằng cách tránh tất cả các phương pháp Copy. Đơn giản chỉ cần đặt giá trị của các tế bào đích đến cùng một giá trị được tìm thấy trong tế bào nguồn. Phương pháp này nhanh gấp khoảng 25 lần so với sử dụng phương pháp Copy:

Range ("B1"). Giá trị = Phạm vi ("A1"). Giá trị

Nếu bạn chỉ cần sao chép các công thức từ một ô này sang một ô khác (không phải là giá trị hoặc định dạng), bạn có thể đặt công thức của ô đích vào cùng một công thức chứa trong ô nguồn:

Range ("B1"). Công thức = Phạm vi ("A1"). Công thức

Sử dụng câu lệnh With

Khi ghi macro, bạn sẽ thường thao tác cùng một đối tượng nhiều lần. Bạn có thể tiết kiệm thời gian và cải thiện hiệu suất bằng cách sử dụng câu lệnh With với một vài thao tác trên một đối tượng nhất định.

Câu lệnh With sử dụng trong ví dụ sau cho biết Excel áp dụng tất cả các thay đổi định dạng cùng một lúc:

Với phạm vi ("A1"). Phông chữ. Bold = Đúng. Italic = Đúng. Underline = xlUnlineStyleSingle Kết thúc Với

Bắt đầu thói quen chunking actions vào Với các câu lệnh sẽ không chỉ giữ macro của bạn chạy nhanh hơn mà còn làm cho nó dễ đọc macro code hơn.

Tránh chọn phương pháp

Macro Recorder thích sử dụng phương pháp Chọn để lựa chọn các đối tượng một cách rõ ràng trước khi thực hiện hành động trên chúng. Nói chung không cần phải chọn các đối tượng trước khi làm việc với chúng. Trên thực tế, bạn có thể cải thiện đáng kể hiệu suất vĩ mô bằng cách không sử dụng phương pháp Chọn.

Sau khi ghi macro của bạn, làm cho nó một thói quen để thay đổi mã được tạo ra để loại bỏ các phương pháp Chọn. Trong trường hợp này, mã tối ưu sẽ trông như sau:

Sheets ("Sheet1"). Phạm vi ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet2"). Phạm vi ("A1"). FormulaR1C1 = Tấm "1000" ("Sheet3"). Phạm vi ("A1"). FormulaR1C1 = "1000"

Lưu ý rằng không có gì đang được chọn.Mã chỉ đơn giản sử dụng hệ thống phân cấp đối tượng để áp dụng các hành động cần thiết.

Giới hạn các chuyến đi đến bảng tính

Một cách khác để tăng tốc độ macro của bạn là hạn chế số lần bạn tham chiếu dữ liệu bảng trong mã của bạn. Thu thập dữ liệu từ bảng tính luôn ít hiệu quả hơn so với bộ nhớ. Tức là các macro của bạn sẽ chạy nhanh hơn nếu không phải lặp lại nhiều lần với bảng tính.

Ví dụ: mã đơn giản sau đây yêu cầu VBA liên tục trở lại trang tính ("Sheet1"). Phạm vi ("A1") để nhận số cần thiết để so sánh được thực hiện trong câu lệnh if:

Đối với ReportMonth = 1 đến 12 Nếu Range ("A1"). Value = ReportMonth MsgBox 1000000 / ReportMonth End If Next ReportMonth

Một phương pháp hiệu quả hơn nhiều là lưu giá trị trong Sheets ("Sheet1"). Phạm vi ("A1") đến một biến có tên là MyMonth. Bằng cách này, mã tham khảo các biến MyMonth thay vì bảng tính:

Dim MyMonth như Integer MyMonth = Range ("A1"). Giá trị cho ReportMonth = 1 đến 12 Nếu MyMonth = ReportMonth Sau đó MsgBox 1000000 / ReportMonth Kết thúc Nếu Tiếp theoThời gianThẻNhân

Xem xét sử dụng các biến để làm việc với dữ liệu trong bộ nhớ thay vì các bảng tính trực tiếp tham khảo.