Excel 2016 VBA Tốc độ Mẹo - núm vú

VBA cho Excel 2016 là nhanh, nhưng nó không phải lúc nào cũng đủ nhanh. (Chương trình máy tính không bao giờ đủ nhanh) Tiếp tục đọc để khám phá một số ví dụ lập trình bạn có thể sử dụng để tăng tốc độ macro của bạn.

Tắt màn hình đang cập nhật

Khi thực hiện macro, bạn có thể ngồi lại và xem tất cả hành động trên màn hình xảy ra trong macro. Mặc dù làm điều này có thể mang tính hướng dẫn, sau khi macro hoạt động bình thường, nó thường gây phiền nhiễu và có thể làm chậm hiệu suất của macro của bạn đáng kể. May thay, bạn có thể vô hiệu hóa việc cập nhật màn hình thường xảy ra khi bạn thực hiện macro. Để tắt cập nhật màn hình, hãy sử dụng câu lệnh sau:

Ứng dụng. ScreenUpdating = False

Nếu bạn muốn người dùng xem những gì đang xảy ra ở bất kỳ thời điểm nào trong suốt macro, hãy sử dụng câu lệnh sau để bật màn hình lên:

Ứng dụng. ScreenUpdating = True

Để chứng minh sự khác biệt về tốc độ, hãy thực hiện macro đơn giản này, điền vào một dải với các con số:

Sub FillRange () Dim r as Long, c Như số Long Dim Long Number = 0 For r = 1 đến 50 Đối với c = 1 đến 50 Số = Số + 1 Tế bào (r, c). Chọn Cells (r, c). Giá trị = Số Next c Tiếp r End Sub

Bạn thấy mỗi ô được chọn và giá trị đang được nhập vào ô. Bây giờ hãy chèn câu lệnh sau vào phần đầu của thủ tục và thực hiện lại:

Ứng dụng. ScreenUpdating = False

Phạm vi được lấp đầy nhanh hơn, và bạn không nhìn thấy kết quả cho đến khi macro chạy xong và cập nhật màn hình là (tự động) được đặt thành True.

Khi bạn gỡ lỗi mã, thực hiện chương trình đôi khi kết thúc ở đâu đó ở giữa mà không cần bật Màn hình cập nhật lại. Điều này đôi khi làm cho cửa sổ ứng dụng Excel trở nên hoàn toàn không phản hồi. Cách thoát khỏi trạng thái đóng băng này rất đơn giản: Quay trở lại với VBE và thực hiện câu lệnh sau trong cửa sổ ngay lập tức:

Ứng dụng. ScreenUpdating = True

Tắt tính toán tự động

Nếu bạn có một bảng tính với nhiều công thức phức tạp, bạn có thể thấy rằng bạn có thể tăng tốc độ đáng kể bằng cách thiết lập chế độ tính toán thành thủ công trong khi macro đang thực hiện. Khi macro kết thúc, thiết lập chế độ tính toán trở lại tự động.

Câu lệnh dưới đây thiết lập chế độ tính toán Excel thành hướng dẫn sử dụng:

Ứng dụng. Tính toán = xlCalculationManual

Thực hiện câu lệnh tiếp theo để thiết lập chế độ tính toán để tự động:

Ứng dụng. Tính toán = xlCalculationAutomatic

Nếu mã của bạn sử dụng ô có kết quả công thức, tính toán tắt có nghĩa là các ô sẽ không được tính lại trừ khi bạn nói rõ với Excel để làm điều đó!

Loại bỏ những thông báo cảnh báo xấu hổ

Như bạn đã biết, macro có thể tự động thực hiện một loạt các hành động. Trong nhiều trường hợp, bạn có thể bắt đầu một macro và sau đó đi ra ngoài trong phòng nghỉ trong khi Excel thực hiện công việc của nó. Tuy nhiên, một số hoạt động của Excel hiển thị các thông điệp yêu cầu phản ứng của con người. Các loại tin nhắn này có nghĩa là bạn không thể rời Excel mà không cần giám sát trong khi thực hiện macro của bạn - trừ khi bạn biết bí quyết bí mật.

Bạn có thể hướng dẫn Excel không hiển thị các loại cảnh báo này trong khi chạy macro.

Bí quyết bí mật để tránh những thông điệp cảnh báo này là chèn câu lệnh VBA sau vào macro của bạn:

Ứng dụng. DisplayAlerts = False

Excel thực hiện thao tác mặc định cho các kiểu thư này. Trong trường hợp xóa một tờ, thao tác mặc định là Xóa. Nếu bạn không chắc chắn về hoạt động mặc định, hãy thực hiện một bài kiểm tra để xem điều gì sẽ xảy ra.

Khi thủ tục kết thúc, Excel tự động đặt lại thuộc tính DisplayAlerts thành True. Nếu bạn cần bật các cảnh báo trở lại trước khi thủ tục kết thúc, sử dụng câu lệnh này:

Ứng dụng. DisplayAlerts = True

Đơn giản hóa các tham chiếu đối tượng

Như bạn đã biết, các tham chiếu đến các đối tượng có thể trở nên rất dài. Ví dụ, tài liệu tham khảo đầy đủ về đối tượng Range có thể như sau:

Workbooks ("MyBook.xlsx"). Worksheets ("Sheet1") _. Range ("InterestRate")

Nếu macro của bạn thường sử dụng phạm vi này, bạn có thể muốn tạo một biến đối tượng bằng cách sử dụng lệnh Set. Ví dụ, câu lệnh sau gán đối tượng Range này cho một biến đối tượng có tên Rate:

Set Rate = Workbooks ("MyBook.xlsx") _. Worksheets ("Sheet1"). Range ("InterestRate")

Sau khi xác định biến đối tượng này, bạn có thể sử dụng biến Rate thay vì tham chiếu dài. Ví dụ, bạn có thể thay đổi giá trị của ô có tên InterestRate:

Rate. Giá trị =. 085

Cách gõ này dễ hơn câu lệnh sau:

Workbooks ("MyBook.xlsx"). Worksheets ("Sheet1"). _ Phạm vi ("InterestRate") =. 085

Ngoài việc đơn giản hóa mã hóa của bạn, sử dụng các biến đối tượng sẽ tăng tốc các macro của bạn một cách đáng kể.

Khai báo các loại biến

Bạn thường không phải lo lắng về loại dữ liệu bạn chỉ định cho một biến. Excel xử lý tất cả các chi tiết cho bạn đằng sau hậu trường. Ví dụ, nếu bạn có một biến có tên là MyVar, bạn có thể gán một số loại cho biến đó. Bạn thậm chí có thể chỉ định một chuỗi văn bản để nó sau này trong thủ tục.

Nếu bạn muốn các thủ tục của bạn để thực hiện càng nhanh càng tốt, nói cho Excel loại dữ liệu sẽ được gán cho mỗi biến của bạn. Đây được gọi là tuyên bố kiểu của một biến.

Nói chung, bạn nên sử dụng kiểu dữ liệu đòi hỏi số lượng byte nhỏ nhất nhưng vẫn có thể xử lý tất cả dữ liệu được gán cho nó. Khi VBA làm việc với dữ liệu, tốc độ thực hiện phụ thuộc vào số byte mà VBA có thể sử dụng. Nói cách khác, dữ liệu byte ít được sử dụng, VBA nhanh hơn có thể truy cập và thao tác dữ liệu.Một ngoại lệ cho trường hợp này là loại dữ liệu Integer. Nếu tốc độ là quan trọng, thay vào đó hãy sử dụng loại Dữ liệu dài.

Nếu bạn sử dụng một biến đối tượng, bạn có thể khai báo biến như là một loại đối tượng cụ thể. Dưới đây là ví dụ:

Tỷ lệ mờ là Tầm giá Set Set = Tập tin ("MyBook.xlsx") _. Worksheets ("Sheet1"). Range ("InterestRate")

Sử dụng cấu trúc With-End With

Bạn có cần thiết lập một số thuộc tính cho một đối tượng không? Mã của bạn chạy nhanh hơn nếu bạn sử dụng cấu trúc Kết thúc với. Một lợi ích khác là mã của bạn có thể dễ đọc hơn.

Đoạn mã dưới đây không sử dụng With-End With:

Lựa chọn. HorizontalAlignment = Lựa chọn xlCenter. VerticalAlignment = Lựa chọn xlCenter. WrapText = Lựa chọn đúng. Định hướng = 0 Lựa chọn. ShrinkToFit = Sai lựa chọn. MergeCells = False

Đây là cùng mã, được viết lại để sử dụng With-End With:

Với lựa chọn. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = Đúng. Định hướng = 0. ShrinkToFit = Sai. MergeCells = Kết thúc Sai Với

Khi bạn sử dụng With-End With, đảm bảo rằng mỗi câu lệnh bắt đầu với một dấu chấm.