Chuyển công thức Excel sang hàm Vba sử dụng WorkSheetFunction

Trong bài viết này, blog.hocexcel.online sẽ hướng dẫn các bạn cách chuyển công thức Excel sang hàm Vba sử dụng WorkSheetFunction một cách đơn giản nhất.

Khi tạo công thức, bạn gặp các vấn đề sau:

• + Công thức quá dài.
• + Có nhiều đoạn lặp lại.
• + Công thức chạy quá chậm.
• + Bạn lại đang võ vẽ cũng như tò mò về Vba.
• + Trong Vba bạn muốn gọi một hàm giống hàm trong Excel.

Đây chính là lúc bạn thực hành dùng Vba đề viết hàm thay cho các công thức.

Excel cung cấp lớp WorkSheetFunction:

• Chứa các hàm giống các hàm của Excel, ít phải suy nghĩ mà vẫn viết được.
• Lớp này không thể tạo đối tượng mới.
• Vba đã tạo sẵn đối tượng, truy cập Application.WorkSheetFunction.
• Cấu trúc Application.WorkSheetFunction.TenHam(DanhSachDoiSo).
• Hàm “IF” không có, thay vào đó dùng IIF hoặc câu lệnh IF.
• Dùng WorkSheetFunction bạn sẽ thấy dễ dàng, và công thức rất trong sáng.

Xem thêm: Học vba ở đâu?

Cách khai thác lớp WorkSheetFunction, lấy hàm Match làm ví dụ:

1.  Tạo một biến để gán đối tượng WorkSheetFunction( tùy chọn).
2. Dùng Set để gán Application.WorkSheetFunction.
3. Gọi hàm Bien=Application.WorkSheetFunction.TenHam(DanhSachDoiSo).

Tìm thông tin về hàm Match:

+Ví dụ cách sử dụng hàm Match trong Vba.

Help của hàm Match.

Một số vấn đề ngoài lề:

Các hàm này trả lại kiểu Variant thì không sao, ngược lại nếu nguyên mẫu hàm trả lại kiểu cụ thể ( double chẳng hạn), nếu lỗi trong excel thì trả lại lỗi, còn trong vba nó sẽ tạo ra một lỗi, có thể gây dừng hàm. Do vậy nên chủ động bẫy lỗi, các hàm test lỗi của excel ( isna,…) cũng vô dụng trong trường hợp này.

Dò tìm hai chiều với WorksheetFunction

Cho bài toán như trên, dò tìm hai chiều có tính trường hợp ngoại lệ. Ở đây ta đã có công thức nằm tại ô D6.

Phân tích công thức ta được:

Như trên hình ta thấy công thức trên phụ thuộc vào 4 đối số:

+ C6: Code.

+ $I$7:$L$10: BangDuLieu.

+ $H$7:$H$10: Row.

+ $I$6:$L$6: Col.

Học Excel kế toán ở Hà Nội

Bây giờ sẽ dùng Vba để định nghĩa công thức trên với tư cách một hàm, hàm “MyFunction”. Trong excel nhấn ALT+ F11, Insert->Module, một Module mới được tạo ra, mã nguồn vba sẽ được viết ở đây. Nhập code như hinh.

• Tên hàm: MyFunction, có thể đặt tùy ý, nhưng nên tránh như tên có sẵn như sum, if, day,…
• Bốn đối số được để trong dấu ngoặc: Code, BangDuLieu, Row, Col.
• Nhất quyết phải có dòng MyFunction=MotBieuThuc. Để trả lại kết quả cho hàm.
• Nguyên mẫu các hàm Left, Right, Match, Index khá giống trong Excel.
• Các chữ màu xanh là ghi chú, có thể bỏ qua không cần nhập.

Trong excel ta gõ công thức: =MyFunction(C6,$I$7:$L$10,$I$6:$L$6,$H$7:$H$10)

MyFunction đã trở thành một hàm của Excel, với chức năng tính toán giống như công thức tại ô D6. So sánh kết quả ở hai cột D và côt E cho cùng một kết quả.

==> với WorksheetFunction, một chút sáng tạo, bạn có thể viết các hàm vba cho riêng mình, đơn giản mà mạnh mẽ.

Video minh họa.