Tự viết hàm của mình nhờ VBA trong Excel

Thế nào là hàm do người dùng định nghĩa?

Nhắc đến hàm trong Excel, người dùng sẽ ngay lập tức nghĩ đến các hàm thông thường như : SUM; DATE hay VLOOKUP. Điều này có thể hiểu được, bởi Excel đã tạo ra hàng trăm hàm mà bạn chỉ cần áp dụng công thức để xử  lý dữ liệu.

Tuy nhiên, với những ai đã có kinh nghiệm tạo macros thì có lẽ sẽ cần định nghĩa thêm về “hàm”. Có ba loại hàm có sẵn trong Excel. Loại đầu tiên là các hàm Worksheet thông thường như SUM; DATE hay VLOOKUP. Chúng được  thiết lập trong Excel và được sử dụng thường xuyên khi làm việc với Worksheets.

Loại hàm thứ hai là Hàm thủ tục ( Procedure Function), đây là cấu trúc lập trình thông thường được sử dụng để tạo macros. Hàm thủ tục được sử dụng để thực hiện một số hoạt động và trả về một giá trị ứng với  mã lập trình được gọi bởi hàm đó.

Loại hàm thứ ba được gọi là hàm do người dùng định nghĩa ( User-Defined Function-UDF).  Đây là loại hàm cho phép bạn truy cập trực tiếp vào macros trong phạm vi worksheets của mình.

Hãy suy nghĩ thêm về điều này: Bây giờ bạn có thể tạo ra một macro mà có thể được truy cập trực tiếp trong phạm vi worksheet đang làm việc, giống như các hàm worksheet khác. Điều này có nghĩa là khi tạo UDFs, bạn sẽ không bị giới hạn chỉ sử dụng các hàm được cung cấp bởi Excel mà có thể  tự tạo hàm để thao tác.

Mẹo để tạo UDFs cho riêng mình là xác định thông tin bạn muốn hàm đó trả về cùng với những thông tin cần để cung cấp cho hàm. Ví dụ: bạn muốn xác định số kí tự “X” viết hoa trong dải ô sau. Kết quả cần trả về là một giá trị (số đếm) và điều bạn cần cung cấp là dải ô để kiểm tra.

Dưới đây là ví dụ về một UDF có thể tạo:

Các thành phần của một UDF

Với những người đã biết lập trình macro thì có thể lập tức nhậ n ra rằng hàm UDF có cấu trúc tương tự hàm VBA thông thường.  Tuy nhiên, nếu bạn chưa quen với macros thì sẽ giúp ích hơn khi  xem xét từng phần của macros như sau. Bắt đầu với dòng đầu tiên:

Dòng này đơn giản chỉ ra hàm được định nghĩa tên là “NumXs”; hàm này được áp dụng với dải ô bạn muốn , và kết quả trả về là số nguyên.

Khi tự tạo UDFS, có hai điều bạn  cần thay đổi là tên hàm (NumXs) và định dang kết quả ( Số nguyên). Những phần khác trong dòng không cần thay đổi thường xuyên.

Bốn dòng tiếp theo trong UDF là:

Đây là phần khai báo biến được sử dụng trong nôi dung chính của UDF. Mỗi dòng bao gồm từ khóa “Dim” theo sau bởi tên biến + As+ loại dữ liệu dành cho biến.

Mỗi biến có một chức năng nhất đinh, được xác định khi bạn thực sự hình dung UDF sẽ chạy như thế nào. Ví dụ, iCnt là một biến sẽ có chức năng như là một máy tính đếm số chữ X được tìm thấy.

Bảy dòng tiếp theo trong UDF để kiểm tra trong từng ô

Bắt đầu bằng việc cài đặt chức năng đếm (iCnt) bằng 0. Sau đó là cấu trúc lập trình đặc biệt, đó là vòng lặp For Next. Trong ví dụ này, nó có nghĩa là các dòng lệnh lập trình từ “For Each” đến “Next c” sẽ được lặp lại với từng ô.  Đầu tiên, chúng sẽ xác định giá trị trong mỗi ô và lưu trữ chúng vào biến sTeamp. Sau đó, một vòng lặp For Next khác được sử dụng để kiểm tra mỗi ký tự trong biến sTemp. Nếu là “X” thì giá trị tại iCnt sẽ tăng lên một đơn vị.

Dòng tiếp theo trong UDF rất quan trọng:

Dòng này định nghĩa biến NumXs bằng với giá trị trong iCnt. Chú ý rằng biến NumXs này có tên trùng với hàm của nó ( được định nghĩa ở dòng đầu tiên của UDF). Định giá trị cho NumXs theo cách này để thông báo cho VBA biết rằng giá trị này nên được trả về từ UDF.

Dòng cuối cùng trong UDF:

Dòng này thông báo cho VBA biết rằng đã kết thúc hàm UDF. ( Gồm tất cả các dòng từ đầu tiên bắt đầu với “Function” và dòng)

Áp dụng UDF trong bảng bảng tính

Vậy làm thế nào để sử dụng UDF trong worksheet? Tương tự như các hàm trong worksheet khác. Ví dụ, nếu muốn xác định số chữ X trong dãy A2:A15, có thể viết hàm vào một ô như sau:

=NumXs(A2:A15)

Hàm trả về số chữ số X in hoa trong dãy A2:A15. Kết quả này hiện ra khi nhấn Enter sau khi gõ công thức.

Dưới đây là hình ảnh về cách sử dụng UDF trong bảng tính

Rõ ràng là ví dụ được nêu ra ở bài viết quá đơn giản và hầu hết mọi người đều không cần biết có bao nhiêu chữ X viết hoa. Tuy nhiên, ví dụ trên đã chỉ ra rằng bằng cách sử dụng UDFs bạn có thể mở rộng cách thức làm việc với dữ liệu trong worksheet. Chúng rất hữu dụng và không khó để tạo, vì vậy hãy thử áp dụng khi cần.

Học lập trình VBA trong Excel ở đâu?