Hướng dẫn cách tăng tốc cho hàm tự tạo trong Excel UDF

Hàm do người dùng tự tạo ra bằng VBA (UDF)

Dưới đây là ví dụ về hàm tùy chỉnh chuyển đổi số thành kí tự. Ví dụ: 22,5USD sẽ được chuyển đổi thành chữ: “Hai mươi hai đô la và năm mươi xu”.

Bạn có thể xem mã cho chức năng tùy chỉnh, được đặt tên là SpellNumber tại đây

Các vấn đề liên quan tới hàm tự tạo

Ví dụ khi chúng ta sử dụng các hàm tự tạo này trong 1 dãy ô.

Chúng ta sẽ thấy là nó sẽ tính toán lại một hoặc tất cả các ô sử dụng hàm SpellNumber. Điều đó khiến chương trình Excel chạy chậm lại.

Tại sao chức năng SpellNumbers chạy lặp lại như vậy? Có cách nào để ngăn việc chạy lại không cần thiết này, để chỉ tính toán 1 lần duy nhất?

Cách khắc phục việc tính toán lại trong hàm tự tạo

Chúng ta không phải là chuyên gia tính toán, nhưng chuyên gia hiệu suất Excel, Charles Williams, cho thấy cách phụ thuộc và tính toán lại hoạt động với các công thức và UDF . Ngay cả với ví dụ rất đơn giản, mọi thứ cũng trở nên phức tạp!

Nguyên tắc hoạt động của công thức trong Excel

Chúng ta thực hiện tính năng Tìm nhanh trong sổ làm việc và chức năng SpellNumber chỉ được sử dụng trên 3 trang. Có 350-400 hàng trên mỗi trang, với hàm được sử dụng trong 2 cột và hai sử dụng 2 lần trong mỗi công thức.

Vì vậy, sẽ có khoảng 400 x 2 x 2 x 3 = 4800 phép tính!

Chúng ta sẽ thực hiện một thử nghiệm nhanh với một biến toàn cục (myCount), để đếm số lần nó lặp qua UDF trong macro chính.

Kết quả là myCount = 3438

Tắt tất cả các tính năng không cần thiết

Đầu tiên ta thử đặt một vài dòng ở đầu macro, để tắt các tính năng và cập nhật không cần thiết: Ví dụ như việc nháy màn hình và các sự kiện có thể xảy ra.

Điều đó có thể làm cho macro chạy nhanh hơn một chút, nhưng nó vẫn lặp qua UDF vô số lần.

Đặt điểm dừng cho hàm tự tạo

Đối với thử nghiệm tiếp theo ta sẽ thêm “dấu hiệu dừng” cho SpellNumber UDF.

• Đầu tiên, ta sẽ tạo một biến toàn cục có tên bNumWd Offer.
• Tiếp theo, ở đầu SpellNumber UDF,ta thêm một dòng để kiểm tra biến bNumWd Offer

 bNumWdOff = True

• Cuối cùng, trong macro khác ta thêm mã để thay đổi biến thành True khi bắt đầu và thay đổi nó thành False ở cuối.

bNumWd Offer = Đúng

Điều đó giúp tăng tốc một chút. Mã vẫn đi đến hàm SpellNumber vô số lần, nhưng không lặp qua nó.

Tắt chức năng tính toán của Excel

Thử nghiệm ta sẽ tắt chức năng tính toán. Macro chính đã thêm các công thức vào một trang tính, vì vậy ta không thể tắt tính toán hoàn toàn.

Thay vào đó, ta tắt tính toán cho 3 trang sử dụng UDF.

Sau đó, ở phần cuối của macro chính, ta thêm các bước này

• Đặt bNumWd Offer thành True, vì vậy nó sẽ không chạy qua toàn bộ SpellNumber UDF
• Bật tính toán cho 3 tờ
• Đặt bNumWd Offer thành False

Vấn Đề Do UDF Gây Ra

Như bạn có thể thấy, một chức năng tùy chỉnh (UDF) có thể làm chậm các macro trong sổ làm việc của bạn.

UDF cũng chạy khi ta lọc các cột sử dụng chức năng tùy chỉnh.

Chúng ta chắc chắn có nhiều thứ khác cũng kích hoạt UDF.

Chức năng tùy chỉnh thay thế

Trong sổ làm việc cho các số được chuyển đổi thành các từ và chúng đều là các số nguyên.

Trong trường hợp này, thay vì sử dụng hàm tùy chỉnh, bạn có thể tạo một bảng số từ 0 đến 100. Sau đó, trong cột thứ hai, hãy đặt các chữ viết cho mỗi số.

Sau đó, trong các ô mà bạn muốn hiển thị các từ được viết, hãy sử dụng công thức INDEX / MATCH để kéo từ bảng tra cứu.

Sử dụng Macro để chuyển đổi số

Một tùy chọn khác là chuyển đổi số thành từ bằng macro, thay vì hàm tùy chỉnh. Nó có thể đặt các từ trong các ô làm giá trị tĩnh, thay vì công thức.

Chạy macro khi cần và sẽ không có công thức với các chức năng tùy chỉnh nên sẽ không làm chậm sổ làm việc của bạn.

Các tùy chọn khác cho các vấn đề của UDF

Ta có thể gặp phải tình huống tương tự với chức năng tùy chỉnh (UDF) gây ra vấn đề và làm sao chúng ta khắc phục nó

Trên đây là cách bạn sử dụng hàm tự tạo UDF trong Excel. Bạn cũng có thể tham khảo thêm các bài viết về cách sử dụng VBA trong Excel trên Gitiho.com hoặc đăng ký một khóa học tin học bổ ích của chúng tôi tại tại đây. Cảm ơn bạn đã theo dõi bài viết.