Làm thế nào để sử dụng các hàm của trang tính như hàm VLOOKUP trong VBA Excel?
Các hàm như VLOOKUP, COUNTIF, SUMIF được gọi là hàm trang tính. Nói chung, các hàm được xác định trước trong Excel và sẵn sàng sử dụng trên trang tính là các hàm trang tính. Bạn không thể thay đổi hoặc xem mã đằng sau các hàm này trong VBA.
Mặt khác, người dùng xác định các chức năng và chức năng cụ thể cho VBA như MsgBox hoặc InputBox là các chức năng VBA. Tất cả chúng ta đều biết cách sử dụng các hàm VBA trong VBA. Nhưng nếu chúng ta muốn sử dụng hàm VLOOKUP trong VBA thì sao. làm sao chúng ta làm việc đó bây giờ? Trong bài viết này, chúng ta sẽ khám phá chính xác điều đó.
Sử dụng các hàm của bảng tính trong VBA
Để truy cập hàm trang tính, chúng tôi sử dụng một lớp Ứng dụng. Hầu hết tất cả các hàm của trang tính đều được liệt kê trong lớp Application.WorksheetFunction. Và sử dụng toán tử dấu chấm, bạn có thể truy cập tất cả chúng.
Trong bất kỳ phụ nào, hãy viết Application.WorksheetFunction. Và bắt đầu viết tên của hàm. Intellisense của VBA sẽ hiển thị tên của các hàm có sẵn để sử dụng. Khi bạn đã chọn tên hàm, nó sẽ yêu cầu các biến, giống như bất kỳ hàm nào trên excel. Nhưng bạn sẽ phải chuyển các biến ở định dạng dễ hiểu VBA. Ví dụ: nếu bạn muốn chuyển phạm vi A1: A10, bạn sẽ phải chuyển nó dưới dạng một đối tượng phạm vi như Phạm vi (“A1: A10”).
Vì vậy, chúng ta hãy sử dụng một số hàm trang tính để hiểu nó tốt hơn.
Cách sử dụng hàm VLOOKUP trong VBA
Để chứng minh cách bạn có thể sử dụng hàm VLOOKUP trong VBA, ở đây tôi có dữ liệu mẫu. Tôi cần hiển thị Tên và Thành phố của Id đăng nhập đã cho trong hộp thông báo bằng VBA. Dữ liệu được trải rộng trong phạm vi A1: K26.
Nhấn ALT + F11 để mở VBE và chèn một mô-đun. Xem đoạn mã dưới đây.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Khi bạn chạy mã này, bạn sẽ nhận được kết quả này.
Bạn có thể xem VBA in kết quả vào hộp thông báo nhanh như thế nào. Bây giờ chúng ta hãy kiểm tra mã.
Nó hoạt động như thế nào?
1.
Dim loginID As String
Tên Dim, thành phố As String
Đầu tiên, chúng ta đã khai báo hai biến kiểu chuỗi để lưu trữ kết quả được trả về bởi hàm VLOOKUP. Tôi đã sử dụng các biến kiểu chuỗi vì tôi chắc chắn rằng kết quả trả về bởi hàm VLOOKUP sẽ là một giá trị chuỗi. Nếu hàm trang tính của bạn được mong đợi trả về loại giá trị số, ngày, phạm vi, v.v., hãy sử dụng loại biến đó để lưu trữ kết quả. Nếu bạn không chắc chắn, loại giá trị nào sẽ được trả về bởi hàm trang tính, hãy sử dụng các biến kiểu biến thể.
2.
loginID = “AHKJ_1-3357042451”
Tiếp theo, chúng tôi đã sử dụng biến loginID để lưu trữ giá trị tra cứu. Ở đây chúng tôi đã sử dụng một giá trị được mã hóa cứng. Bạn cũng có thể sử dụng tài liệu tham khảo. Ví dụ.
Bạn có thể sử dụng Range (“A2”). Giá trị để cập nhật động giá trị tra cứu từ dải ô A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range (“A1: K26”), 2, 0) Ở đây chúng ta sử dụng hàm VLOOKUP để lấy. Bây giờ khi bạn chỉnh đúng hàm và mở dấu ngoặc đơn, nó sẽ hiển thị cho bạn các đối số được yêu cầu nhưng không mang tính mô tả như hiển thị trên Excel. Xem cho chính mình.
Bạn cần nhớ cách thức và biến bạn cần sử dụng. Bạn luôn có thể quay lại trang tính để xem chi tiết biến mô tả.
Ở đây, giá trị tra cứu là Arg1. Đối với Arg1, chúng tôi sử dụng loginID. Bảng tra cứu là Arg2. Đối với Arg2, chúng tôi đã sử dụng Range (“A1: K26”). Lưu ý rằng chúng tôi không sử dụng trực tiếp A2: K26 như chúng tôi làm trên Excel. Chỉ số cột là Arg3.
Đối với Arg3, chúng tôi đã sử dụng 2, vì tên nằm trong cột thứ hai. Kiểu tra cứu là Arg4. Chúng tôi đã sử dụng 0 làm Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range (“A1: K26”), 4, 0)
Tương tự như vậy chúng ta có được tên thành phố.
4.
MsgBox (“Tên:” & tên & vbLf & “Thành phố:” & thành phố)
Cuối cùng, chúng tôi in tên và thành phố bằng Messagebox.
Tại sao lại sử dụng Hàm Worksheet trong VBA? Các hàm trang tính có sức mạnh tính toán to lớn và sẽ không thông minh nếu bỏ qua sức mạnh của các hàm trang tính. Ví dụ: nếu chúng tôi muốn độ lệch chuẩn của một tập dữ liệu và bạn muốn viết toàn bộ mã cho điều này, bạn có thể mất hàng giờ. Nhưng nếu bạn biết cách sử dụng hàm trang tính STDEV.P trong VBA để thực hiện phép tính trong một lần.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Sử dụng nhiều chức năng của bảng tính VBA
Giả sử chúng ta cần sử dụng khớp chỉ mục để truy xuất một số giá trị. Bây giờ bạn sẽ viết công thức trong VBA như thế nào. Đây là những gì tôi đoán bạn sẽ viết:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Điều này không sai nhưng nó dài dòng. Cách đúng để sử dụng nhiều chức năng là sử dụng khối With. Xem ví dụ dưới đây:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Như bạn có thể thấy, tôi đã sử dụng With block để nói với VBA rằng tôi sẽ sử dụng các thuộc tính và chức năng của Application.WorksheetFunction. Vì vậy, tôi không cần phải xác định nó ở khắp mọi nơi. Tôi vừa sử dụng toán tử dấu chấm để truy cập các hàm INDEX, MATCH, VLOOKUP và STDEV.P. Khi chúng tôi sử dụng câu lệnh End With, chúng tôi không thể truy cập các hàm mà không sử dụng các tên hàm đủ điều kiện.
Vì vậy, nếu bạn phải sử dụng nhiều hàm trang tính trong VBA, hãy sử dụng với khối.
Không phải Tất cả các Chức năng của Trang tính đều khả dụng thông qua Ứng dụng. Chức năng của Trang tính Một số Chức năng của Trang tính có sẵn trực tiếp để sử dụng trong VBA. Bạn không cần sử dụng đối tượng Application.WorksheetFunction.
Ví dụ, các hàm như Len () được sử dụng để lấy số ký tự trong một chuỗi, left, right, mid, trim, offset, v.v. Các hàm này có thể được sử dụng trực tiếp trong VBA. Đây là một ví dụ.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Hãy xem, ở đây chúng tôi đã sử dụng hàm LEN mà không sử dụng đối tượng Application.WorksheetFunction.
Tương tự, bạn có thể sử dụng các chức năng khác như left, right, mid, char, v.v.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
Khi chạy sub bên trên nó sẽ trả về:
5 He o ll
Vì vậy, các bạn, đây là cách bạn có thể sử dụng hàm trang tính của Excel trong VBA. Tôi hy vọng tôi đã giải thích đủ và bài viết này đã giúp bạn. Nếu bạn có bất kỳ câu hỏi nào liên quan đến bài viết này hoặc bất kỳ điều gì khác liên quan đến VBA, hãy hỏi trong phần bình luận bên dưới. Cho đến khi đó bạn có thể đọc về các chủ đề liên quan khác bên dưới.
Bài viết liên quan:
Bài viết phổ biến:
Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.