Tạo hàm tùy chỉnh trong Excel

Các hàm tùy chỉnh, như macro, sử dụng ngôn ngữ lập trình Visual Basic for Applications (VBA) . Chúng khác với các hàm macro theo hai cách có ý nghĩa. Trước tiên, chúng sử dụng các thủ tục Function thay vì thủ tục Sub. Nghĩa là, chúng bắt đầu bằng một câu lệnh Function thay vì câu lệnh Sub và kết thúc bằng End Function thay vì End Sub. Thứ hai, chúng thực hiện tính toán thay vì thực hiện hành động. Một số loại câu lệnh nhất định, chẳng hạn như câu lệnh chọn và định dạng dải ô, bị loại trừ khỏi các hàm tùy chỉnh. Trong bài viết này, bạn sẽ tìm hiểu cách tạo và sử dụng các hàm tùy chỉnh. Để tạo các hàm và macro, bạn sẽ làm việc với Visual Basic Editor (VBE), ứng dụng này sẽ mở ra trong một cửa sổ mới tách biệt với Excel.

Giả sử công ty của bạn đưa ra chiết khấu số lượng là 10 phần trăm khi bán sản phẩm, miễn là đơn hàng mua trên 100 chiếc. Trong các đoạn sau đây, chúng ta sẽ trình bày một hàm tính toán khoản chiết khấu này.

Ví dụ dưới đây thể hiện một biểu mẫu đơn hàng liệt kê từng mặt hàng, số lượng, giá, chiết khấu (nếu có) và giá mở rộng thu được.

Để tạo hàm DISCOUNT tùy chỉnh trong sổ làm việc này, hãy làm theo các bước sau:

1. Nhấn Alt+F11 để mở Visual Basic Editor (trên máy Mac, nhấn FN+ALT+F11), rồi bấm vào Chèn > Mô-đun. Một cửa sổ mô-đun mới sẽ xuất hiện ở phía bên tay phải của Visual Basic Editor.

2. Sao chép và dán mã sau vào mô-đun mới.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Bây giờ, bạn đã sẵn sàng sử dụng hàm DISCOUNT mới. Đóng Visual Basic Editor, chọn ô G7, rồi nhập nội dung sau:

=DISCOUNT(D7,E7)

Excel tính toán chiết khấu 10 phần trăm cho 200 chiếc với giá $47,50 mỗi chiếc và trả về $950,00.

Trong dòng đầu tiên của mã VBA, Hàm DISCOUNT(số lượng, giá), bạn đã chỉ ra rằng hàm DISCOUNT yêu cầu hai đối số là quantity (số lượng) và price (giá). Khi bạn gọi hàm trong một ô trang tính, bạn phải bao gồm hai đối số đó. Trong công thức =DISCOUNT(D7,E7), D7 là đối số quantity và E7 là đối số price. Giờ đây, bạn có thể sao chép công thức DISCOUNT sang G8:G13 để có được kết quả hiển thị bên dưới.

Hãy cân nhắc cách Excel diễn giải thủ tục hàm này. Khi bạn nhấn Enter, Excel sẽ tìm tên DISCOUNT trong sổ làm việc hiện tại và thấy rằng đó là một hàm tùy chỉnh trong mô-đun VBA. Tên đối số được đặt trong dấu ngoặc đơn, quantity và price, là chỗ dành sẵn cho các giá trị làm cơ sở để tính toán chiết khấu.

Câu lệnh If trong khối mã sau đây kiểm tra đối số quantity và xác định xem số lượng mặt hàng đã bán có lớn hơn hoặc bằng 100 không:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Nếu số lượng mục đã bán lớn hơn hoặc bằng 100, VBA thực thi câu lệnh sau đây, nhân giá trị quantity với giá trị price, rồi nhân kết quả với 0,1:

Discount = quantity * price * 0.1

Kết quả được lưu trữ dưới dạng biến Discount (Chiết khấu). Câu lệnh VBA lưu trữ giá trị trong biến được gọi là câu lệnh assignment (chỉ định) vì câu lệnh này đánh giá biểu thức ở bên phải dấu bằng và gán kết quả cho tên biến ở bên trái. Vì biến Discount có cùng tên với thủ tục hàm, giá trị được lưu trữ trong biến sẽ được trả về công thức trang tính có tên là hàm DISCOUNT.

Nếu quantity dưới 100, VBA sẽ thực thi câu lệnh sau:

Discount = 0

Cuối cùng, câu lệnh sau làm tròn giá trị được gán cho biến Discount đến hai vị trí thập phân:

Discount = Application.Round(Discount, 2)

VBA không có hàm ROUND nhưng Excel thì có. Do đó, để sử dụng Hàm ROUND trong câu lệnh này, bạn yêu cầu VBA tìm kiếm phương pháp (hàm) Round trong đối tượng Application (Excel). Bạn thực hiện điều đó bằng cách thêm từApplication trước từ Round. Sử dụng cú pháp này bất cứ khi nào bạn cần truy nhập vào hàm Excel từ mô-đun VBA.

Hàm tùy chỉnh phải bắt đầu bằng câu lệnh Function và kết thúc bằng câu lệnh End Function. Ngoài tên hàm, câu lệnh Function thường chỉ định một hoặc nhiều đối số. Tuy nhiên, bạn có thể tạo hàm mà không có đối số. Excel bao gồm một số hàm tích hợp sẵn— ví dụ: RAND và NOW—không sử dụng đối số.

Sau câu lệnh Function, một thủ tục hàm bao gồm một hoặc nhiều câu lệnh VBA đưa ra quyết định và thực hiện tính toán bằng cách sử dụng các đối số được truyền cho hàm. Cuối cùng, ở đâu đó trong thủ tục hàm, bạn phải đưa vào một câu lệnh chỉ định giá trị cho một biến có cùng tên với hàm. Giá trị này được trả về cho công thức gọi hàm.

Số lượng từ khóa VBA bạn có thể sử dụng trong các hàm tùy chỉnh nhỏ hơn số lượng mà bạn có thể sử dụng trong macro. Hàm tùy chỉnh không được phép làm bất cứ điều gì ngoài việc trả về giá trị cho công thức trong trang tính hoặc cho biểu thức được sử dụng trong một macro hay hàm VBA khác. Ví dụ, các hàm tùy chỉnh không thể đổi kích cỡ cửa sổ, chỉnh sửa công thức trong ô hoặc thay đổi tùy chọn phông chữ, màu hoặc mẫu cho văn bản trong ô. Nếu bạn bao gồm mã “thao tác” thuộc loại này trong thủ tục hàm, hàm sẽ trả về #VALUE! lỗi.

Một thao tác mà một thủ tục hàm có thể thực hiện (ngoài việc thực hiện tính toán) là hiển thị một hộp thoại. Bạn có thể sử dụng câu lệnh InputBox trong hàm tùy chỉnh làm phương tiện nhận đầu vào từ người dùng thực thi hàm. Bạn có thể sử dụng câu lệnh MsgBox như một phương tiện truyền tải thông tin cho người dùng. Bạn cũng có thể sử dụng hộp thoại tùy chỉnh hoặc UserForms, nhưng đó là chủ đề nằm ngoài phạm vi của phần giới thiệu này.

Ngay cả macro và hàm tùy chỉnh đơn giản cũng có thể khó đọc. Bạn có thể khiến chúng dễ hiểu hơn bằng cách nhập văn bản giải thích vào biểu mẫu chú thích. Bạn thêm chú thích bằng cách đặt dấu nháy đơn vào trước văn bản giải thích. Ví dụ, các ví dụ sau đây hiển thị hàm DISCOUNT có chú thích. Việc thêm chú thích như thế này sẽ giúp bạn hoặc những người khác dễ dàng duy trì mã VBA của bạn khi thời gian trôi qua. Nếu bạn cần thực hiện thay đổi đối với mã trong tương lai, bạn sẽ dễ dàng hiểu ban đầu mình đã làm những gì.

Dấu nháy đơn yêu cầu Excel bỏ qua mọi thứ ở bên phải trên cùng một dòng để bạn có thể tạo chú thích trên chính dòng đó hoặc ở bên phải của dòng có chứa mã VBA. Bạn có thể bắt đầu một khối mã tương đối dài với chú thích giải thích mục đích tổng thể của khối, rồi sử dụng chú thích nội tuyến để ghi lại các câu lệnh riêng lẻ.

Một cách khác để ghi lại macro và các hàm tùy chỉnh của bạn là cấp cho chúng tên mô tả. Ví dụ, thay vì đặt tên cho macro là Labels, bạn có thể đặt tên là MonthLabels để mô tả cụ thể hơn mục đích mà macro phục vụ. Việc sử dụng tên mô tả cho macro và hàm tùy chỉnh đặc biệt hữu ích khi bạn đã tạo nhiều thủ tục, đặc biệt là khi bạn tạo các thủ tục có mục đích tương tự nhưng không giống hệt nhau.

Cách bạn ghi lại macro và các hàm tùy chỉnh là một tùy chọn cá nhân. Điều quan trọng là phải áp dụng một số phương pháp ghi và sử dụng một cách nhất quán.

Để sử dụng hàm tùy chỉnh, sổ làm việc có chứa mô-đun mà bạn đã tạo hàm đó phải mở. Nếu sổ làm việc đó không mở, bạn sẽ nhận được lỗi #NAME? khi bạn cố sử dụng hàm đó. Nếu bạn tham chiếu hàm trong một sổ làm việc khác, bạn phải đặt trước tên hàm tên sổ làm việc chứa hàm đó. Ví dụ, nếu bạn tạo một hàm có tên là DISCOUNT trong sổ làm việc có tên là Personal.xlsb và bạn gọi hàm đó từ một sổ làm việc khác, bạn phải nhập =personal.xlsb!discount(), chứ không chỉ =discount().

Bạn có thể tự lưu một số tổ hợp phím (và có thể có lỗi nhập) bằng cách chọn các hàm tùy chỉnh từ hộp thoại Chèn hàm. Hàm tùy chỉnh của bạn xuất hiện trong danh mục Do người dùng xác định:

Một cách dễ dàng hơn để khiến các hàm tùy chỉnh của bạn khả dụng mọi lúc là lưu trữ chúng trong một sổ làm việc riêng biệt, rồi lưu sổ làm việc đó dưới dạng phần bổ trợ. Sau đó, bạn có thể khiến phần bổ trợ sẵn dùng bất cứ khi nào bạn chạy Excel. Hãy làm như sau:

Sau khi bạn làm theo các bước này, các hàm tùy chỉnh của bạn sẽ khả dụng mỗi khi bạn chạy Excel. Nếu bạn muốn thêm vào thư viện hàm của mình, hãy quay lại Visual Basic Editor. Nếu bạn nhìn vào phần Visual Basic Editor Project Explorer bên dưới đầu đề VBAProject, bạn sẽ thấy một mô-đun có tên theo tệp bổ trợ của bạn. Phần bổ trợ của bạn sẽ có đuôi là .xlam.

Việc bấm đúp vào mô-đun đó trong Project Explorer sẽ khiến Visual Basic Editor hiển thị mã hàm của bạn. Để thêm hàm mới, hãy định vị điểm chèn của bạn sau câu lệnh End Function chấm dứt hàm cuối cùng trong cửa sổ Mã, rồi bắt đầu nhập. Bạn có thể tạo bao nhiêu hàm tùy thích theo cách này và chúng sẽ luôn khả dụng trong danh mục Do người dùng xác định trong hộp thoại Chèn hàm.

Nội dung này ban đầu được tạo bởi Mark Dodge và Craig Stinson nằm trong cuốn sách của họ có tiêu đề Microsoft Office Excel 2007 Inside Out. Kể từ đó, nội dung đã được cập nhật để áp dụng cho các phiên bản mới hơn của Excel.