Chức năng bảng tính VBA | Làm thế nào để sử dụng WorksheetFunction trong VBA?

Chức năng của trang 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ự như vậy, 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ó một điều tuyệt vời đó là “chúng ta cũng có thể sử dụng các hàm trang tính trong VBA”.

Đúng!!! Bạn nghe đúng rồi, 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 chức năng của trang tính trong VBA?

Bạn có thể tải xuống Mẫu bảng tính VBA này tại đây - Mẫu bảng tính chức năng 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ác 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 đề cập đến tên đối tượng “WorksheetFunction”, sau đó đặt dấu chấm (.) Thì 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 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 - Các chức năng của 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 đế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: Thời điểm 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à Range (“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 các 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.

Dựa trên lựa chọn bạn đã thực hiện trong ô E2, chúng tôi cần tìm nạp Mã Pin cho vùng tương ứng. Nhưng lần này thông qua VBA VLOOKUP, không phải VLOOKUP bảng tính. Làm theo các bước dưới đây để áp dụng hàm VLOOKUP.

Bước 1: Tạo một tên macro đơn giản trong Thủ tục phụ.

Mã:

 Sub Worksheet_Function_Example2 () End Sub 

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

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = End Sub 

Bước 3: Để truy cập hàm trang tính, hàm VLOOKUP bắt đầu mã là “WorksheetFunction.VLOOKUP”.

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = WorksheetFunction.Vlookup (End Sub 

Bước 4: Một trong những vấn đề ở đây là cú pháp sẽ không cung cấp cho bạn bất kỳ loại hướng dẫn nào để làm việc với VLOOKUP. Bạn cần phải hoàn toàn chắc chắn về cú pháp mà bạn đang làm việc.

Cú pháp đầu tiên của hàm VLOOKUP là "Giá trị tra cứu". Trong trường hợp này, giá trị tra cứu của chúng tôi là giá trị ô E2, vì vậy hãy viết mã là Phạm vi (“E2”).

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = WorksheetFunction.Vlookup (Phạm vi ("E2"). Giá trị, Kết thúc phụ 

Bước 5: Bây giờ đối số thứ hai là mảng bảng của chúng ta, trong trường hợp này, phạm vi mảng bảng của chúng ta là từ A2 đến B6. Vì vậy, mã sẽ là Phạm vi (“A2: B6”)

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = WorksheetFunction.Vlookup (Phạm vi ("E2"). Giá trị, Phạm vi ("A2: B6"), Kết thúc phụ 

Bước 6: Đối số Thứ ba sẽ là từ cột nào chúng ta cần dữ liệu từ mảng bảng. Ở đây chúng ta cần dữ liệu từ cột thứ 2, vì vậy đối số sẽ là 2.

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = WorksheetFunction.Vlookup (Phạm vi ("E2"). Giá trị, Phạm vi ("A2: B6"), 2, Kết thúc phụ 

Bước 7: Đối số cuối cùng là tra cứu phạm vi, chúng ta cần đối sánh chính xác để đối số là không (0).

Mã:

 Sub Worksheet_Function_Example2 () Phạm vi ("F2"). Giá trị = WorksheetFunction.VLookup (Phạm vi ("E2"). Giá trị, Phạm vi ("A2: B6"), 2, 0) Kết thúc Phụ 

Như vậy là chúng ta đã hoàn thành xong phần viết mã. Bây giờ đi tới trang tính và chọn bất kỳ phạm vi nào.

Bây giờ, hãy chuyển đến mô-đun mã hóa của bạn và chạy macro Sử dụng phím F5 hoặc theo cách thủ công để lấy mã pin của vùng đã chọn.

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ỉ một số.
  • 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.