Hàm bảng tính VBA - Cách sử dụng WorksheetFunction trong VBA?

Mục lục

Chức năng bảng tính VBA trong Excel

Chức năng bảng tính VBA trong Excel

Hàm worksheet trong VBA được sử dụng khi chúng ta phải tham chiếu đến một trang tính cụ thể, thông thường khi chúng ta tạo một mô-đun, mã thực thi trong trang hiện đang hoạt động của sổ làm việc nhưng nếu chúng ta muốn thực thi mã trong trang tính cụ thể, chúng ta sử dụng hàm trang tính, chức năng này có nhiều cách sử dụng và ứng dụng khác nhau trong VBA.

Điều tốt nhất về VBA là, giống như cách chúng ta sử dụng công thức trong trang tính tương tự, VBA cũng có các chức năng riêng của nó. Nếu điều này là tốt nhất, thì nó cũng có một thứ đẹp đẽ. Đó là “chúng ta cũng có thể sử dụng các hàm trang tính trong VBA.”

Đúng!!! Bạn đã nghe nó đúng; chúng ta cũng có thể truy cập vào các hàm trang tính trong VBA. Chúng tôi có thể truy cập một số hàm của trang tính trong khi viết mã và biến nó thành một phần của mã của chúng tôi.

Làm thế nào để sử dụng các hàm của trang tính trong VBA?

Trong trang tính, tất cả các công thức đều bắt đầu bằng dấu bằng (=), tương tự trong mã hóa VBA, để truy cập công thức trang tính, chúng ta nên sử dụng từ “WorksheetFunction”.

Trước khi nhập bất kỳ công thức trang tính nào, bạn cần phải đề cập đến tên đối tượng “WorksheetFunction”, sau đó đặt dấu chấm (.), Sau đó bạn sẽ nhận được danh sách tất cả các hàm có sẵn trong đối tượng này.

Trong bài viết này, chúng tôi sẽ chỉ tập trung vào cách sử dụng các hàm trang tính trong mã hóa VBA, điều này sẽ bổ sung thêm giá trị cho kiến thức mã hóa của bạn.

# 1 - Chức năng Bảng tính SUM đơn giản

Được rồi, để bắt đầu với các hàm trang tính, hãy áp dụng hàm SUM đơn giản trong excel để thêm số từ trang tính.

Giả sử bạn có dữ liệu bán hàng và chi phí hàng tháng trong bảng tính như bảng dưới đây.

Trong B14 và C14, chúng ta cần đi đến tổng các số trên. Làm theo các bước dưới đây để bắt đầu quá trình áp dụng hàm “SUM” trong Excel VBA.

Bước 1: Tạo một tên macro excel đơn giản.

Mã:

Sub Worksheet_Function_Example1 () End Sub

Bước 2: Vì chúng ta cần kết quả trong ô B14, hãy bắt đầu mã là Phạm vi (“B14”). Giá trị =

Mã:

Sub Worksheet_Function_Example1 () Phạm vi ("B14"). Giá trị = End Sub

Bước 3: Trong B14, chúng ta cần giá trị là kết quả của tổng các số. Vì vậy, để truy cập hàm SUM từ trang tính, hãy bắt đầu mã là “WorksheetFunction”.

Mã:

Sub Worksheet_Function_Example1 () Phạm vi ("B14"). Giá trị = WorksheetFunction. Kết thúc Sub

Bước 4: Ngay khi bạn đặt dấu chấm (.), Nó sẽ bắt đầu hiển thị các chức năng có sẵn. Vì vậy, chọn SUM từ này.

Mã:

Sub Worksheet_Function_Example1 () Phạm vi ("B14"). Giá trị = WorksheetFunction.Sum End Sub

Bước 5: Bây giờ đưa ra tham chiếu của các số trên, tức là Phạm vi (“B2: B13”).

Mã:

Sub Worksheet_Function_Example1 () Phạm vi ("B14"). Giá trị = WorksheetFunction.Sum (Phạm vi ("B2: B13")) Kết thúc Sub

Bước 6: Tương tự, đối với cột tiếp theo, hãy áp dụng mã tương tự bằng cách thay đổi tham chiếu ô.

Mã:

Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Bước 7: Bây giờ chạy mã này theo cách thủ công hoặc sử dụng phím F5 để có tổng số trong các ô B14 & C14.

Wow, chúng tôi có giá trị của mình. Một điều bạn cần lưu ý ở đây là chúng tôi không có bất kỳ công thức nào trong trang tính, nhưng chúng tôi chỉ nhận được kết quả của hàm “SUM” trong VBA.

# 2 - Sử dụng hàm VLOOKUP làm Hàm trang tính

Chúng ta sẽ xem cách sử dụng hàm VLOOKUP trong VBA. Giả sử bên dưới là dữ liệu bạn có trong trang tính excel của mình.

Trong ô E2, bạn đã tạo một danh sách thả xuống gồm tất cả các khu vực.

Based on the selection you made in the E2 cell, we need to fetch the Pin Code for the respective zone. But this time through VBA VLOOKUP, not worksheet VLOOKUP. Follow the below steps to apply VLOOKUP.

Step 1: Create a simple macro name in the Sub Procedure.

Code:

Sub Worksheet_Function_Example2() End Sub

Step 2: We need the result in the F2 cell. So start the code as Range (“F2”).Value =

Code:

Sub Worksheet_Function_Example2() Range ("F2").Value = End Sub

Step 3: To access the worksheet function, VLOOKUP starts the code as “WorksheetFunction.VLOOKUP.”

Code:

Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup( End Sub

Step 4: One of the problems here is syntax will not give you any sort of guidance to work with VLOOKUP. You need to be absolutely sure about the syntax you are working on.

The first syntax of VLOOKUP is “Lookup Value.” In this case, our lookup value is E2 cell value, so write the code as Range (“E2”).Value

Code:

Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub

Step 5: Now, the second argument is our table array. In this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2^nd column, so the argument will be 2.

Code:

Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub

Step 7: The final argument is range lookup, we need an exact match, so the argument is zero (0).

Code:

Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using the F5 key or manually to get the pin code of the selected zone.

Chúng ta không thể quay lại và chạy macro mọi lúc, vì vậy hãy gán macro cho các hình dạng. Chèn một trong các hình dạng trong trang tính.

Thêm giá trị văn bản vào hình dạng đã chèn.

Bây giờ nhấp chuột phải và gán tên macro cho hình dạng này.

Nhấp vào ok sau khi chọn tên macro.

Bây giờ, hình dạng này chứa mã của công thức VLOOKUP của chúng ta. Vì vậy, bất cứ khi nào bạn thay đổi tên vùng, hãy nhấp vào nút, nó sẽ cập nhật các giá trị.

Những điều cần ghi nhớ

Để truy cập các hàm trang tính, chúng ta cần viết từ “WorksheetFunction” hoặc “Application.WorksheetFunction”
Chúng tôi không có quyền truy cập vào tất cả các chức năng, chỉ có một số chức năng.
Chúng tôi không thấy cú pháp thực tế của các hàm trang tính, vì vậy chúng tôi cần hoàn toàn chắc chắn về hàm chúng tôi đang sử dụng.