Có nhiều cách để sử dụng các hàm trong VBA. VBA được tải với nhiều chức năng tích hợp. Bạn thậm chí có thể tạo các chức năng của riêng mình (UDF). Tuy nhiên, bạn cũng có thể sử dụng nhiều hàm của Excel trong VBA bằng cách sử dụng Application.WorksheetFunction.
Cách sử dụng các hàm của trang tính trong VBA
Để truy cập một hàm Excel trong VBA, hãy thêm Application.WorksheetFunction vào trước hàm mà bạn muốn gọi. Trong ví dụ dưới đây, chúng tôi sẽ gọi Hàm tối đa của Excel:
| 12 | Giảm giá trị tối đa miễn làmaxvalue = Application.WorksheetFunction.Max (Phạm vi ("a1"). Giá trị, Phạm vi ("a2"). Giá trị) |
Cú pháp của các hàm giống nhau, tuy nhiên bạn sẽ nhập các đối số của hàm giống như cách bạn làm với bất kỳ hàm VBA nào khác.
Lưu ý rằng cú pháp của Hàm tối đa xuất hiện khi bạn nhập (tương tự như với Hàm VBA):
Phương thức WorksheetFunction
WorksheetFunction là một phương thức của đối tượng Ứng dụng. Nó cho phép bạn truy cập vào nhiều (không phải tất cả) các hàm trang tính Excel tiêu chuẩn. Nói chung, bạn sẽ không có quyền truy cập vào bất kỳ hàm trang tính nào có phiên bản VBA tương ứng.
Bạn có thể xem danh sách nhiều Chức năng Bảng tính phổ biến nhất bên dưới.
Application.WorksheetFunction và Application
Thực tế có hai cách để truy cập các chức năng này:
Application.WorksheetFunction (như đã thấy ở trên):
| 1 | maxvalue = Application.WorksheetFunction.Max (Phạm vi ("a1"). Giá trị, Phạm vi ("a2"). Giá trị) |
hoặc bạn có thể bỏ qua WorksheetFunction
| 1 | maxvalue = Application.Max (Phạm vi ("a1"). Giá trị, Phạm vi ("a2"). Giá trị) |
Thật không may, việc bỏ qua WorksheetFunction sẽ loại bỏ Intellisense hiển thị cú pháp (xem hình trên). Tuy nhiên, nó có một lợi thế tiềm năng lớn: Xử lý lỗi.
Nếu bạn sử dụng Ứng dụng và hàm của bạn tạo ra lỗi, nó sẽ trả về giá trị lỗi. Nếu bạn sử dụng phương thức WorksheetFunction, VBA sẽ thông báo lỗi thời gian chạy. Tất nhiên, bạn có thể xử lý lỗi VBA, nhưng tốt hơn hết là bạn nên tránh lỗi ngay từ đầu.
Hãy xem một ví dụ để thấy sự khác biệt:
Xử lý lỗi Vlookup WorksheetFunction
Chúng tôi sẽ cố gắng thực hiện một Vlookup mà không dẫn đến kết quả trùng khớp. Vì vậy, hàm Vlookup sẽ trả về một lỗi.
Đầu tiên, chúng ta sẽ sử dụng phương thức WorksheetFunction. Lưu ý cách VBA thông báo lỗi:
Tiếp theo, chúng tôi bỏ qua WorksheetFunction. Chú ý làm thế nào
Tiếp theo, chúng ta sẽ bỏ qua WorksheetFunction. Lưu ý rằng không có lỗi nào được ném ra và thay vào đó hàm ‘value’ chứa giá trị lỗi từ Vlookup.
Danh sách chức năng bảng tính VBA
Dưới đây, bạn sẽ tìm thấy danh sách hầu hết các Chức năng VBA WorksheetFunctions phổ biến.
| Hàm số | Sự miêu tả |
|---|---|
| Hợp lý | |
| VÀ | Kiểm tra xem tất cả các điều kiện có được đáp ứng hay không. ĐÚNG SAI |
| NẾU NHƯ | Nếu điều kiện được đáp ứng, hãy làm điều gì đó, nếu không, hãy làm điều gì đó khác. |
| IFERROR | Nếu kết quả là lỗi thì hãy làm điều gì đó khác. |
| HOẶC | Kiểm tra xem có đáp ứng bất kỳ điều kiện nào không. ĐÚNG SAI |
| Tra cứu & Tham khảo | |
| LỰA CHỌN | Chọn một giá trị từ danh sách dựa trên số vị trí của nó. |
| HLOOKUP | Tra cứu một giá trị trong hàng đầu tiên và trả về một giá trị. |
| MỤC LỤC | Trả về một giá trị dựa trên số cột và số hàng của nó. |
| TRA CỨU | Tra cứu các giá trị theo chiều ngang hoặc chiều dọc. |
| TRẬN ĐẤU | Tìm kiếm một giá trị trong danh sách và trả về vị trí của nó. |
| VẬN CHUYỂN | Lật hướng của một loạt các ô. |
| VLOOKUP | Tra cứu một giá trị trong cột đầu tiên và trả về một giá trị. |
| Ngày giờ | |
| NGÀY | Trả về một ngày trong năm, tháng và ngày. |
| DATEVALUE | Chuyển đổi một ngày được lưu trữ dưới dạng văn bản thành một ngày hợp lệ |
| NGÀY | Trả về ngày dưới dạng số (1-31). |
| DAYS360 | Trả về ngày giữa 2 ngày trong một năm 360 ngày. |
| CHỈNH SỬA | Trả về một ngày, cách ngày bắt đầu n tháng. |
| KINH TẾ | Trả về ngày cuối cùng của tháng, ngày thứ n của tháng. |
| GIỜ | Trả về giờ dưới dạng số (0-23). |
| PHÚT | Trả về phút dưới dạng một số (0-59). |
| THÁNG | Trả về tháng dưới dạng số (1-12). |
| NETWORKDAYS | Số ngày làm việc giữa 2 ngày. |
| NETWORKDAYS.INTL | Ngày làm việc giữa 2 ngày, các ngày cuối tuần tùy chỉnh. |
| HIỆN NAY | Trả về ngày và giờ hiện tại. |
| THỨ HAI | Trả về thứ hai dưới dạng một số (0-59) |
| THỜI GIAN | Trả về thời gian từ một giờ, phút và giây. |
| GIÁ TRỊ THỜI GIAN | Chuyển đổi thời gian được lưu trữ dưới dạng văn bản thành thời gian hợp lệ. |
| TUẦN | Trả về ngày trong tuần dưới dạng số (1-7). |
| TUẦN | Trả về số tuần trong một năm (1-52). |
| WORKDAY | Ngày n ngày làm việc kể từ ngày. |
| NĂM | Trả về năm. |
| YEARFRAC | Trả về phần năm giữa 2 ngày. |
| Kỹ thuật | |
| ĐỔI | Chuyển đổi số từ đơn vị này sang đơn vị khác. |
| Tài chính | |
| FV | Tính toán giá trị tương lai. |
| PV | Tính giá trị hiện tại. |
| NPER | Tính tổng số kỳ thanh toán. |
| PMT | Tính toán số tiền thanh toán. |
| TỶ LỆ | Tính lãi suất. |
| NPV | Tính giá trị hiện tại ròng. |
| IRR | Tỷ suất hoàn vốn nội bộ của một tập hợp các CF định kỳ. |
| XIRR | Tỷ suất hoàn vốn nội bộ của một tập hợp các CF không định kỳ. |
| GIÁ BÁN | Tính giá của một trái phiếu. |
| INTRATE | Lãi suất của một chứng khoán được đầu tư đầy đủ. |
| Thông tin | |
| ISERR | Kiểm tra xem giá trị ô có phải là lỗi hay không, bỏ qua # N / A. ĐÚNG SAI |
| BÁC SĨ | Kiểm tra xem giá trị ô có phải là lỗi không. ĐÚNG SAI |
| ISEVEN | Kiểm tra xem giá trị ô là chẵn. ĐÚNG SAI |
| SINH THÁI | Kiểm tra xem ô có logic không (TRUE hoặc FALSE). ĐÚNG SAI |
| ISNA | Kiểm tra xem giá trị ô có là # N / A hay không. ĐÚNG SAI |
| ISNONTEXT | Kiểm tra xem ô không phải là văn bản (các ô trống không phải là văn bản). ĐÚNG SAI |
| THÁNG NĂM | Kiểm tra xem ô có phải là một số không. ĐÚNG SAI |
| LÀ SỐ LẺ | Kiểm tra nếu giá trị ô là lẻ. ĐÚNG SAI |
| ISTEXT | Kiểm tra xem ô có phải là văn bản hay không. ĐÚNG SAI |
| KIỂU | Trả về kiểu giá trị trong một ô. |
| Toán học | |
| ABS | Tính giá trị tuyệt đối của một số. |
| ĐỒNG Ý | Xác định và thực hiện các phép tính cho một cơ sở dữ liệu hoặc một danh sách. |
| TRẦN NHÀ | Làm tròn một số đến bội số được chỉ định gần nhất. |
| COS | Trả về cosin của một góc. |
| DEGREES | Chuyển đổi radian sang độ. |
| DSUM | Tổng hợp các bản ghi cơ sở dữ liệu đáp ứng các tiêu chí nhất định. |
| THẬM CHÍ | Làm tròn đến số nguyên chẵn gần nhất. |
| NS | Tính giá trị theo cấp số nhân của một số nhất định. |
| SỰ THẬT | Trả về giai thừa. |
| SÀN NHÀ | Làm tròn một số xuống đến bội số được chỉ định gần nhất. |
| GCD | Trả về ước số chung lớn nhất. |
| NS | Làm tròn một số xuống số nguyên gần nhất. |
| LCM | Trả về bội số chung nhỏ nhất. |
| LN | Trả về lôgarit tự nhiên của một số. |
| ĐĂNG NHẬP | Trả về lôgarit của một số với một cơ số đã chỉ định. |
| LOG10 | Trả về logarit cơ số 10 của một số. |
| MROUND | Làm tròn một số thành bội số đã chỉ định. |
| SỐ LẺ | Làm tròn đến số nguyên lẻ gần nhất. |
| số Pi | Giá trị của PI. |
| SỨC MẠNH | Tính một số được nâng lên thành lũy thừa. |
| SẢN PHẨM | Nhân một mảng số. |
| QUOTIENT | Trả về kết quả nguyên của phép chia. |
| RADIANS | Chuyển đổi một góc thành radian. |
| RANDBETWEEN | Tính một số ngẫu nhiên giữa hai số. |
| VÒNG | Làm tròn một số đến một số chữ số được chỉ định. |
| LÀM TRÒN XUỐNG | Làm tròn một số xuống (về 0). |
| ROUNDUP | Làm tròn một số lên (đi từ số 0). |
| TỘI | Trả về sin của một góc. |
| TIỀM NĂNG | Trả về thống kê tóm tắt cho một chuỗi dữ liệu. |
| TỔNG | Cộng các số với nhau. |
| SUMIF | Tính tổng các số đáp ứng một tiêu chí. |
| SUMIFS | Tính tổng các số đáp ứng nhiều tiêu chí. |
| GIỚI THIỆU | Nhân các mảng số và tính tổng mảng kết quả. |
| TÂN | Trả về tiếp tuyến của một góc. |
| Số liệu thống kê | |
| TRUNG BÌNH | Số trung bình. |
| AVERAGEIF | Số trung bình đáp ứng một tiêu chí. |
| AVERAGEIFS | Tính trung bình các số đáp ứng nhiều tiêu chí. |
| CORREL | Tính toán sự tương quan của hai chuỗi. |
| ĐẾM | Đếm các ô có chứa một số. |
| COUNTA | Đếm các ô không trống. |
| COUNTBLANK | Đếm các ô trống. |
| COUNTIF | Đếm các ô đáp ứng tiêu chí. |
| COUNTIFS | Đếm các ô đáp ứng nhiều tiêu chí. |
| DỰ BÁO | Dự đoán giá trị y trong tương lai từ đường xu hướng tuyến tính. |
| TẦN SỐ | Đếm các giá trị nằm trong phạm vi được chỉ định. |
| SỰ PHÁT TRIỂN | Tính toán các giá trị Y dựa trên tăng trưởng theo cấp số nhân. |
| GIAO LƯU | Tính toán điểm chặn Y cho một đường phù hợp nhất. |
| LỚN | Trả về giá trị lớn nhất thứ k. |
| CUỐI CÙNG | Trả về thống kê về đường xu hướng. |
| TỐI ĐA | Trả về số lớn nhất. |
| TRUNG BÌNH | Trả về số trung vị. |
| MIN | Trả về số nhỏ nhất. |
| CHẾ ĐỘ | Trả về số phổ biến nhất. |
| PERCENTILE | Trả về phân vị thứ k. |
| PERCENTILE.INC | Trả về phân vị thứ k. Trường hợp k bao gồm. |
| PERCENTILE.EXC | Trả về phân vị thứ k. Trường hợp k độc quyền. |
| QUARTILE | Trả về giá trị phần tư đã chỉ định. |
| QUARTILE.INC | Trả về giá trị phần tư đã chỉ định. Bao gồm. |
| QUARTILE.EXC | Trả về giá trị phần tư đã chỉ định. Loại trừ. |
| THỨ HẠNG | Xếp hạng của một số trong một chuỗi. |
| RANK.AVG | Xếp hạng của một số trong một chuỗi. Mức trung bình. |
| RANK.EQ | Xếp hạng của một số trong một chuỗi. Xếp hạng hàng đầu. |
| DỐC | Tính toán độ dốc từ hồi quy tuyến tính. |
| NHỎ | Trả về giá trị nhỏ nhất thứ k. |
| STDEV | Tính toán độ lệch chuẩn. |
| STDEV.P | Tính SD của toàn bộ dân số. |
| STDEV.S | Tính SD của một mẫu. |
| STDEVP | Tính SD của toàn bộ dân số |
| XU HƯỚNG | Tính toán các giá trị Y dựa trên đường xu hướng. |
| Chữ | |
| DỌN DẸP | Loại bỏ tất cả các ký tự không in được. |
| ĐÔ LA | Chuyển đổi một số thành văn bản ở định dạng tiền tệ. |
| TÌM THẤY | Định vị vị trí của văn bản trong một ô. |
| BÊN TRÁI | Cắt bớt văn bản một số ký tự từ bên trái. |
| LEN | Đếm số ký tự trong văn bản. |
| MID | Trích xuất văn bản từ giữa ô. |
| THÍCH HỢP | Chuyển đổi văn bản thành trường hợp thích hợp. |
| THAY THẾ | Thay thế văn bản dựa trên vị trí của nó. |
| REPT | Lặp lại văn bản một số lần. |
| ĐÚNG | Cắt bớt văn bản một số ký tự từ bên phải. |
| TÌM KIẾM | Định vị vị trí của văn bản trong một ô. Không phân biệt chữ hoa chữ thường. |
| THAY THẾ | Tìm và thay thế văn bản. Trường hợp nhạy cảm. |
| CHỮ | Chuyển đổi một giá trị thành văn bản với một định dạng số cụ thể. |
| TRIM | Loại bỏ tất cả các khoảng trắng thừa khỏi văn bản. |
