Mục lục:
- Ví dụ về các tính năng của bảng tính
- Bạn không thể sử dụng hộp thoại Chức năng Dán Chức năng Excel để chèn một tính năng bảng tính vào một mô-đun VBA. Thay vào đó, hãy nhập các chức năng theo kiểu cũ: bằng tay. Tuy nhiên, bạn
- Người mới đến VBA thường nhầm lẫn các chức năng tích hợp của VBA và các chức năng của bảng tính Excel. Một quy tắc tốt để nhớ là VBA không cố gắng để phát minh lại bánh xe.Đối với hầu hết các phần, VBA không sao chép các chức năng của bảng tính Excel.
Video: Phần 2: VBA for Excel - Bài 15: In ấn nhanh trong Excel sử dụng VBA 2025
Mặc dù VBA cung cấp một loạt các chức năng tích hợp sẵn, bạn có thể không phải lúc nào cũng tìm thấy chính xác những gì bạn cần. May mắn thay, bạn cũng có thể sử dụng hầu hết các chức năng của bảng tính Excel trong thủ tục VBA của bạn. Các chức năng bảng tính duy nhất bạn không thể sử dụng là các hàm có chức năng VBA tương đương. Ví dụ, bạn không thể sử dụng chức năng RAND của Excel (tạo ra một số ngẫu nhiên) bởi vì VBA có một chức năng tương đương: Rnd.
VBA làm cho các tính năng của bảng tính Excel có sẵn thông qua đối tượng WorksheetFunction, được chứa trong đối tượng Application. Dưới đây là ví dụ về cách bạn có thể sử dụng chức năng SUM của Excel trong câu lệnh VBA:
Total = Application. WorksheetChức năng. SUM (Phạm vi ("A1: A12"))
Bạn có thể bỏ qua phần Application hoặc WorksheetFunction của biểu thức. Trong cả hai trường hợp, VBA đều cho biết bạn đang làm gì. Nói cách khác, ba biểu thức này đều hoạt động chính xác như nhau:
Tổng = Đơn. WorksheetChức năng. SUM (Phạm vi ("A1: A12")) Tổng cộng = WorksheetFunction. SUM (Phạm vi ("A1: A12")) Tổng = Ứng dụng. SUM (Range ("A1: A12"))
Sở thích cá nhân của tôi là sử dụng phần WorksheetFunction chỉ để làm cho nó hoàn toàn rõ ràng rằng mã đang sử dụng một hàm Excel.
Ví dụ về các tính năng của bảng tính
Ở đây, bạn khám phá cách sử dụng các hàm bảng tính trong các biểu thức VBA của bạn.
Tìm giá trị lớn nhất trong dải
Dưới đây là ví dụ cho thấy cách sử dụng hàm MAX của bảng tính Excel trong thủ tục VBA. Thủ tục này hiển thị giá trị lớn nhất trong cột A của bảng tính đang hoạt động:
Sub ShowMax () Dim TheMax Như Đôi TheMax = WorksheetFunction. MAX (Range ("A: A")) MsgBox TheMax End Sub
Bạn có thể sử dụng chức năng MIN để lấy giá trị nhỏ nhất trong một phạm vi. Và như bạn mong đợi, bạn có thể sử dụng các chức năng bảng tính khác theo cách tương tự. Ví dụ: bạn có thể sử dụng chức năng LARGE để xác định giá trị lớn nhất k trong một dải. Biểu thức sau minh họa điều này:
SecondHighest = WorksheetFunction. LARGE (Phạm vi ("A: A"), 2)
Lưu ý rằng hàm LARGE sử dụng hai đối số. Đối số thứ hai đại diện cho k phần thứ-2, trong trường hợp này (giá trị lớn thứ hai).
Tính khoản thanh toán thế chấp
Ví dụ tiếp theo sử dụng chức năng bảng tính PMT để tính khoản thanh toán thế chấp. Ba biến được sử dụng để lưu trữ dữ liệu được truyền vào hàm Pmt làm đối số.Hộp thông báo hiển thị thanh toán được tính.
Sub PmtCalc () Dim IntRate Như Double Dim LoanAmt As Đôi Thời lượng Kích thước Như Long IntRate = 0. 0625/12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. Trong bài báo dưới đây, bạn cũng có thể chèn các giá trị trực tiếp như các tham số hàm:
MsgBox WorksheetFunction. PMT (0. 0625/12, 360, -150000)
Tuy nhiên, sử dụng các biến để lưu các tham số làm cho mã dễ đọc và sửa đổi hơn, nếu cần.
Sử dụng chức năng tra cứu
Ví dụ sau sử dụng các hàm InputBox và MsgBox của VBA, cộng với chức năng VLOOKUP của Excel. Nó nhắc cho một số phần và sau đó được giá từ một bảng tra cứu. Dưới đây, phạm vi A1: B13 được đặt tên là PriceList.
Phạm vi, có tên PriceList, có chứa giá cho các bộ phận.
Đây là cách hoạt động của thủ tục GetPrice:
Chức năng InputBox của VBA yêu cầu người sử dụng cho một số phần.
-
Số phần người dùng nhập vào được gán cho biến PartNum.
-
Câu lệnh tiếp theo kích hoạt bảng giá, chỉ trong trường hợp nó chưa phải là tờ hoạt động.
-
Mã sử dụng hàm VLOOKUP để tìm số phần trong bảng.
-
Lưu ý rằng các đối số bạn sử dụng trong câu lệnh này giống như các đối số bạn sẽ sử dụng với hàm trong một công thức bảng tính. Câu lệnh này gán kết quả của hàm cho biến Price.
-
Mã hiển thị giá cho một phần thông qua chức năng MsgBox.
-
Thủ tục này không có bất kỳ lỗi xử lý, và nó không đau khổ nếu bạn nhập một số phần không tồn tại. (Hãy thử.) Nếu đây là một ứng dụng thực tế được sử dụng trong một doanh nghiệp thực tế, bạn sẽ muốn thêm một số câu lệnh xử lý lỗi một cách duyên dáng hơn.
Nhập các tính năng bảng tính
Bạn không thể sử dụng hộp thoại Chức năng Dán Chức năng Excel để chèn một tính năng bảng tính vào một mô-đun VBA. Thay vào đó, hãy nhập các chức năng theo kiểu cũ: bằng tay. Tuy nhiên, bạn
có thể sử dụng hộp thoại Chức năng Dán để xác định chức năng bạn muốn sử dụng và tìm hiểu về các đối số của nó. Bạn cũng có thể tận dụng tùy chọn Auto List Members của VBE, hiển thị một danh sách thả xuống của tất cả các chức năng của bảng tính. Chỉ cần gõ
Ứng dụng. WorksheetFunction , theo sau là một khoảng thời gian. Sau đó, bạn nhìn thấy một danh sách các chức năng bạn có thể sử dụng. Nếu tính năng này không hoạt động, hãy chọn lệnh Tools → Tools của VBE, nhấp vào tab Editor và đặt một kiểm tra bên cạnh Auto List Members. Nhận danh sách các chức năng của bảng tính mà bạn có thể sử dụng trong mã VBA của bạn.
Người mới đến VBA thường nhầm lẫn các chức năng tích hợp của VBA và các chức năng của bảng tính Excel. Một quy tắc tốt để nhớ là VBA không cố gắng để phát minh lại bánh xe.Đối với hầu hết các phần, VBA không sao chép các chức năng của bảng tính Excel.
Đối với hầu hết các chức năng của bảng tính không có sẵn như các phương thức của đối tượng WorksheetFunction, bạn có thể sử dụng toán tử hoặc chức năng tích hợp VBA tương ứng. Ví dụ, chức năng bảng tính MOD không có sẵn trong đối tượng WorksheetFunction vì VBA có một số tương đương: được xây dựng trong Mod operator.
Dòng dưới cùng? Nếu bạn cần sử dụng một chức năng, trước tiên hãy xác định xem VBA có cái gì đó đáp ứng được nhu cầu của bạn. Nếu không, hãy kiểm tra các chức năng của bảng tính. Nếu mọi thứ khác không thành công, bạn có thể viết một hàm tùy chỉnh bằng cách sử dụng VBA.
