Chức năng VBA - Hướng dẫn tạo chức năng tùy chỉnh bằng VBA

Mục lục

Các hàm VBA trong Excel

Các hàm VBA trong Excel

Chúng ta đã thấy rằng chúng ta có thể sử dụng các hàm trang tính trong VBA, tức là các hàm của trang tính excel trong mã hóa VBA bằng cách sử dụng phương pháp application.worksheet, nhưng làm thế nào để chúng ta sử dụng một hàm của VBA trong excel, các hàm như vậy được gọi là hàm do người dùng định nghĩa, khi người dùng tạo một hàm trong VBA, nó cũng có thể được sử dụng trong trang tính excel.

Mặc dù chúng ta có nhiều hàm trong excel để thao tác dữ liệu, nhưng đôi khi chúng ta cần có một số tùy chỉnh trong các công cụ để có thể tiết kiệm thời gian khi chúng ta thực hiện một số tác vụ lặp đi lặp lại. Chúng tôi có các hàm được xác định trước trong excel như SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH trong excel, v.v. nhưng chúng tôi thực hiện một số tác vụ hàng ngày mà một lệnh hoặc hàm không có sẵn trong Excel, thì bằng cách sử dụng VBA, chúng ta có thể tạo hàm tùy chỉnh được gọi là Hàm do người dùng xác định (UDF).

Chức năng VBA làm gì?

Họ thực hiện các tính toán nhất định; và
Trả lại một giá trị

Trong VBA, khi xác định hàm, chúng ta sử dụng cú pháp sau để xác định các tham số và kiểu dữ liệu của chúng.

Kiểu dữ liệu ở đây là kiểu dữ liệu mà biến sẽ giữ. Nó có thể chứa bất kỳ giá trị nào (bất kỳ kiểu dữ liệu hoặc đối tượng của bất kỳ lớp nào).

Chúng ta có thể kết nối đối tượng với thuộc tính hoặc phương thức của nó bằng cách sử dụng dấu chấm hoặc dấu chấm (.).

Làm thế nào để tạo các chức năng tùy chỉnh bằng VBA?

Thí dụ

Giả sử chúng ta có dữ liệu sau đây từ một trường học mà chúng ta cần tìm tổng điểm của học sinh, kết quả và điểm.

Để tổng hợp điểm của một học sinh cá nhân trong tất cả các môn học, chúng tôi có một hàm có sẵn, tức là SUM, nhưng để tìm ra điểm và kết quả dựa trên các tiêu chí do trường đưa ra không có sẵn trong Excel theo mặc định .

Đây là lý do tại sao chúng ta cần tạo các hàm do người dùng xác định.

Bước 1: Tìm tổng số điểm

Đầu tiên, chúng ta sẽ tìm tổng điểm bằng cách sử dụng hàm SUM trong excel.

Nhấn Enter để nhận kết quả.

Kéo Công thức vào phần còn lại của các ô.

Bây giờ để tìm ra Kết quả (Đạt, Không đạt hay Lặp lại cơ bản), tiêu chí của trường đặt ra là như vậy.

Nếu học sinh đạt điểm cao hơn hoặc bằng 200 với tổng số điểm trên 500 và học sinh không bị trượt môn nào (mỗi môn đạt hơn 32 điểm), thì học sinh đó được đậu,
Nếu học sinh đạt điểm cao hơn hoặc bằng 200, nhưng học sinh bị trượt 1 hoặc 2 môn, thì học sinh đã đạt điểm “Lặp lại cơ bản” trong các môn đó,
Nếu học sinh đạt điểm dưới 200 hoặc không đạt 3 môn trở lên thì học sinh đó bị trượt.

Bước 2: Tạo chức năng ResultOfStudent

Để tạo một hàm có tên 'ResultOfStudent', chúng ta cần mở "Visual Basic Editor" bằng cách sử dụng bất kỳ phương pháp nào bên dưới:

Bằng cách sử dụng excel tab Nhà phát triển.

Nếu tab Nhà phát triển không có sẵn trong MS Excel, thì chúng ta có thể lấy tab đó bằng cách sử dụng các bước sau:

Sau đó, nhấp chuột phải vào bất kỳ vị trí nào trên ruy-băng, chọn Tùy chỉnh ruy-băng trong excel ' .

Khi chúng ta chọn lệnh này, hộp thoại “Tùy chọn Excel” sẽ mở ra.

Chúng ta cần chọn hộp "Nhà phát triển" để lấy tab.

Bằng cách sử dụng phím tắt, tức là Alt + F11.

Khi chúng ta mở trình soạn thảo VBA, chúng ta cần chèn mô-đun bằng cách đi tới trình đơn Chèn và chọn một mô-đun.

Chúng ta cần dán đoạn mã sau vào mô-đun.

Hàm ResultOfStudents (Marks As Range) As String Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer For Each mycell In Marks Total = Total + mycell.Value If mycell.Value = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Lặp lại" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Khác ResultOfStudents = "Không thành công" Kết thúc Nếu Kết thúc Hàm

Hàm trên trả về kết quả cho một học sinh.

Chúng ta cần hiểu mã này hoạt động như thế nào.

Câu lệnh đầu tiên, 'Function ResultOfStudents (Marks As Range) As String,' khai báo một hàm có tên 'ResultOfStudents' sẽ chấp nhận một phạm vi làm đầu vào cho các dấu và sẽ trả về kết quả dưới dạng một chuỗi.

Làm mờ mycell theo phạm vi Làm mờ tổng cộng dưới dạng số nguyên Dim Đếm không thành côngĐối tượng dưới dạng số nguyên

Ba câu lệnh này khai báo các biến, tức là

'myCell' dưới dạng một Phạm vi,
'Tổng số' dưới dạng Số nguyên (để lưu trữ tổng số điểm do học sinh ghi được),
'CountOfFailedSubject' dưới dạng số nguyên (để lưu trữ số môn học mà một sinh viên đã trượt ).

Đối với mỗi mycell In Marks Total = Total + mycell.Value Nếu mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell

Mã này kiểm tra mọi ô trong phạm vi 'Marks' và thêm giá trị của mọi ô trong biến ' Total' và nếu giá trị của ô nhỏ hơn 33, thì thêm 1 vào biến 'CountOfFailedSubject' .

If Total> = 200 And CountOfFailedSubject 0 then ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 And CountOfFailedSubject = 0 then ResultOfFailedSubject = "Passed" Else ResultOfStudents = "Failed" End If

Mã này kiểm tra giá trị của 'Total' và 'CountOfFailedSubject' và chuyển ' Báo cáo cần thiết', 'Đạt' hoặc 'Không đạt' tương ứng với 'ResultOfStudents.'

Bước 3: Áp dụng chức năng ResultOfStudents để nhận kết quả

Hàm ResultOfStudents lấy điểm, tức là chọn 5 điểm do học sinh chấm.

Bây giờ chọn Phạm vi ô, tức là B2: F2.

Kéo Công thức vào phần còn lại của Ô.

Bước 4: Tạo chức năng 'GradeForStudent' để nhận Điểm

Bây giờ để tìm điểm cho học sinh, chúng ta sẽ tạo thêm một hàm nữa có tên là 'GradeForStudent.'

The code would be:

Function GradeForStudent(TotalMarks As Integer, Result As String) As String If TotalMarks> 440 And TotalMarks 380 And TotalMarks 320 And TotalMarks 260 And TotalMarks = 200 And TotalMarks <= 260 And (Result = "Passed" Or Result = "Essential Repeat") Then GradeForStudent = "E" ElseIf TotalMarks < 200 Or Result = "Failed" Then GradeForStudent = "F" End If End Function

This function assigns a ‘Grade’ to the student based on the ‘Total Marks’ and ‘Result.’