Trong hướng dẫn này, chúng ta sẽ tìm hiểu về hàm VBA trong Excel {rỗng} 1) Visual Basic trong Excel là gì?

{trống} 2) Cách sử dụng VBA trong Excel?

{trống} 3) Cách tạo hàm Người dùng xác định?

{trống} 4) Cách viết Macro?

Cách viết VBAcode Excel cung cấp cho người dùng một bộ sưu tập lớn các hàm tạo sẵn, quá đủ để đáp ứng người dùng bình thường. Có thể thêm nhiều phần mềm khác bằng cách cài đặt các phần bổ trợ khác nhau có sẵn. Hầu hết các phép tính có thể đạt được với những gì được cung cấp, nhưng không lâu trước khi bạn thấy mình ước rằng có một hàm thực hiện một công việc cụ thể và bạn không thể tìm thấy bất kỳ thứ gì phù hợp trong danh sách. Bạn cần một UDF. UDF (Hàm do người dùng xác định) chỉ đơn giản là một hàm mà bạn tự tạo bằng VBA. UDF thường được gọi là “Chức năng tùy chỉnh”. Một UDF có thể vẫn ở trong mô-đun mã được đính kèm với sổ làm việc, trong trường hợp đó, nó sẽ luôn sẵn dùng khi sổ làm việc đó đang mở. Ngoài ra, bạn có thể tạo phần bổ trợ của riêng mình chứa một hoặc nhiều hàm mà bạn có thể cài đặt vào Excel giống như một bổ trợ thương mại. Các UDF cũng có thể được truy cập bằng các mô-đun mã. Thường thì các UDF được các nhà phát triển tạo ra để chỉ hoạt động trong mã của một thủ tục VBA và người dùng không bao giờ biết về sự tồn tại của chúng. Giống như bất kỳ chức năng nào, UDF có thể đơn giản hoặc phức tạp như bạn muốn. Hãy bắt đầu với một cái dễ …​

Chức năng tính diện tích hình chữ nhật Vâng, tôi biết bạn có thể làm được điều này trong đầu! Khái niệm này rất đơn giản nên bạn có thể tập trung vào kỹ thuật. Giả sử bạn cần một hàm để tính diện tích hình chữ nhật. Bạn xem qua bộ sưu tập các hàm của Excel, nhưng không có hàm nào phù hợp. Đây là phép tính cần thực hiện:

VÙNG = CHIỀU DÀI x RỘNG RÃI Mở sổ làm việc mới rồi mở Trình soạn thảo Visual Basic (Công cụ> Macro> Trình chỉnh sửa Visual Basic hoặc ALT + F11).

Bạn sẽ cần một mô-đun để viết hàm của mình, vì vậy hãy chọn Chèn> Mô-đun. Vào kiểu mô-đun trống: Function Area và nhấn ENTER. Visual Basic Editor hoàn thành dòng cho bạn và thêm dòng End Function như thể bạn đang tạo một chương trình con. Cho đến nay, nó trông giống như thế này …​

Function Area()

End Function

Đặt con trỏ của bạn giữa các dấu ngoặc sau “Khu vực”. Nếu bạn từng tự hỏi dấu ngoặc dùng để làm gì, thì bạn sắp tìm hiểu! Chúng ta sẽ chỉ định các “đối số” mà hàm của chúng ta sẽ nhận (argument là một phần thông tin cần thiết để thực hiện phép tính). Gõ Length thành double, Width là double và nhấp vào dòng trống bên dưới. Lưu ý rằng khi bạn nhập, một hộp cuộn bật lên liệt kê tất cả những thứ phù hợp với những gì bạn đang nhập.

img2

Tính năng này được gọi là Danh sách Thành viên Tự động. Nếu nó không xuất hiện hoặc nó đã bị tắt (bật nó trong Công cụ> Tùy chọn> Trình chỉnh sửa) hoặc bạn có thể đã mắc lỗi đánh máy trước đó. Nó là một kiểm tra rất hữu ích về cú pháp của bạn. Tìm mục bạn cần và nhấp đúp vào mục đó để chèn vào mã của bạn. Bạn có thể bỏ qua nó và chỉ cần nhập nếu bạn muốn. Mã của bạn bây giờ trông như thế này …​

Function Area(Length As Double, Width As Double)

End Function

Khai báo kiểu dữ liệu của các đối số là không bắt buộc nhưng có ý nghĩa. Bạn có thể đã nhập Length, Width và để nguyên như vậy, nhưng cảnh báo cho Excel biết loại dữ liệu nào sẽ giúp mã của bạn chạy nhanh hơn và nhận lỗi trong đầu vào. Kiểu dữ liệu double đề cập đến số (có thể rất lớn) và cho phép phân số. Bây giờ để tính toán chính nó. Trong dòng trống, trước tiên hãy nhấn phím TAB để thụt lề mã của bạn (giúp dễ đọc hơn) và nhập Area = Length * Width. Đây là mã đã hoàn thành …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Bạn sẽ nhận thấy một tính năng trợ giúp khác của Visual Basic Editor bật lên khi bạn đang nhập, Thông tin nhanh tự động …​

img31

Nó không liên quan ở đây. Mục đích của nó là giúp bạn viết các hàm trong VBA, bằng cách cho bạn biết những đối số nào được yêu cầu. Bạn có thể kiểm tra chức năng của mình ngay lập tức. Chuyển sang cửa sổ Excel và nhập các số liệu về Chiều dài và Chiều rộng trong các ô riêng biệt. Trong ô thứ ba, hãy nhập hàm của bạn như thể nó là một trong những hàm được tích hợp sẵn. Trong ví dụ này, ô A1 chứa chiều dài (17) và ô B1 chứa chiều rộng (6,5). Trong C1, tôi nhập _ = area (A1, B1) _ và hàm mới đã tính toán diện tích (110.5) …​

img3

Đôi khi, các đối số của một hàm có thể là tùy chọn. Trong ví dụ này, chúng ta có thể đặt đối số Width là tùy chọn. Giả sử hình chữ nhật là một hình vuông với Chiều dài và Chiều rộng bằng nhau. Để tiết kiệm việc người dùng phải nhập hai đối số, chúng tôi có thể để họ chỉ nhập Chiều dài và để hàm sử dụng giá trị đó hai lần (tức là nhân Chiều dài x Chiều dài). Vì vậy, hàm biết khi nào nó có thể thực hiện điều này, chúng ta phải bao gồm Câu lệnh IF để giúp nó quyết định. Thay đổi mã để nó trông giống như thế này …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Lưu ý rằng kiểu dữ liệu cho Chiều rộng đã được thay đổi thành Variant để cho phép các giá trị rỗng. Bây giờ hàm cho phép người dùng chỉ nhập một đối số, ví dụ: _ = area (A1) _. Câu lệnh IF trong hàm kiểm tra xem đối số Width đã được cung cấp hay chưa và tính toán cho phù hợp …​

img4

Chức năng tính toán mức tiêu thụ nhiên liệu Tôi muốn kiểm tra mức tiêu thụ nhiên liệu của ô tô nên khi mua nhiên liệu, tôi ghi chú lại quãng đường đi được và lượng nhiên liệu cần để đổ đầy bình.

Ở đây, ở Anh, nhiên liệu được bán theo lít. milometer của xe (OK, vì vậy nó là một odometer) ghi lại khoảng cách trong dặm. Và bởi vì tôi quá già và ngu ngốc để thay đổi, tôi chỉ hiểu MPG (dặm mỗi gallon). Bây giờ nếu bạn nghĩ rằng đó là tất cả một chút buồn, còn điều này thì sao. Khi về nhà, tôi mở Excel và nhập dữ liệu vào một trang tính để tính MPG cho tôi và lập biểu đồ hiệu suất của xe. Việc tính toán là số dặm xe đã đi kể từ khi fill-up cuối cùng chia cho số gallon nhiên liệu được sử dụng …​

MPG = (MILES NÀY FILL – MILES LAST FILL) / ĐỐT NHIÊN LIỆU nhưng vì nhiên liệu tính theo lít và có 4,546 lít trong một gallon ..

MPG = (MILES NÀY FILL – MILES LAST FILL) / LITERS OF FUEL x 4.546 Đây là cách tôi viết hàm …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

và đây là cách nó hiển thị trên trang tính …​

img5

Không phải tất cả các hàm đều thực hiện các phép tính toán học. Đây là một trong những cung cấp thông tin …​

Một chức năng cung cấp tên của ngày Tôi thường được hỏi nếu có một hàm ngày tháng cung cấp ngày trong tuần dưới dạng văn bản (ví dụ: Thứ Hai). Câu trả lời là không, nhưng khá dễ dàng để tạo một cái. (Phụ lục: Tôi đã nói không? Kiểm tra ghi chú bên dưới để xem chức năng tôi quên!). Excel có hàm WEEKDAY, trả về ngày trong tuần dưới dạng số từ 1 đến 7. Bạn có thể chọn ngày là 1 nếu bạn không thích mặc định (Chủ nhật). Trong ví dụ bên dưới, hàm trả về “5” mà tôi tình cờ biết có nghĩa là “Thứ Năm”.

img6

Nhưng tôi không muốn xem một con số, tôi muốn xem “Thứ Năm”. Tôi có thể sửa đổi phép tính bằng cách thêm hàm Vlookup tham chiếu đến một bảng ở đâu đó chứa danh sách các số và danh sách tên ngày tương ứng. Hoặc tôi có thể có toàn bộ nội dung với nhiều câu lệnh IF lồng nhau. Quá phức tạp! Câu trả lời là một chức năng tùy chỉnh …​

Function DayName(InputDate As Date)

Dim DayNumber As Integer

DayNumber = Weekday(InputDate, vbSunday)

Select Case DayNumber

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

Tôi đã gọi hàm của mình là “DayName” và nó sử dụng một đối số duy nhất, mà tôi gọi là “Ngày nhập” (tất nhiên) phải là ngày. Đây là cách nó hoạt động …​

  • Dòng đầu tiên của hàm khai báo một biến mà tôi đã gọi là “DayNumber” sẽ là một Số nguyên (tức là một số nguyên).

  • Dòng tiếp theo của hàm gán giá trị cho biến đó bằng cách sử dụng hàm WEEKDAY của Excel. Giá trị sẽ là một số từ 1 đến 7.

Mặc dù mặc định là 1 = Chủ nhật, tôi vẫn đưa nó vào để rõ ràng.

Cuối cùng, một Câu lệnh trường hợp * kiểm tra giá trị của biến và trả về đoạn văn bản thích hợp.

Đây là cách nó hiển thị trên trang tính …​

img7

=== === Truy cập các chức năng tùy chỉnh của bạn Nếu sổ làm việc có mô-đun mã VBA được đính kèm với nó có chứa các chức năng tùy chỉnh, các chức năng đó có thể được giải quyết dễ dàng trong cùng một sổ làm việc như được minh họa trong các ví dụ trên. Bạn sử dụng tên hàm như thể nó là một trong những hàm dựng sẵn của Excel.

Bạn cũng có thể tìm thấy các hàm được liệt kê trong Trình hướng dẫn Hàm (đôi khi được gọi là công cụ Dán Hàm). Sử dụng trình hướng dẫn để chèn một hàm theo cách thông thường (Chèn> Hàm).

Cuộn xuống danh sách các danh mục chức năng để tìm Người dùng xác định và chọn nó để xem danh sách các UDF có sẵn …​

img8

img9

Bạn có thể thấy rằng các chức năng do người dùng xác định thiếu bất kỳ mô tả nào ngoài thông báo “Không có trợ giúp” vô ích, nhưng bạn có thể thêm một mô tả ngắn …​

Đảm bảo rằng bạn đang ở trong sổ làm việc có chứa các hàm. Đi tới Công cụ> Macro> Macro. Bạn sẽ không thấy các hàm của mình được liệt kê ở đây nhưng Excel biết về chúng! Trong hộp Tên Macro ở đầu hộp thoại, hãy nhập tên của hàm, sau đó bấm vào nút Tùy chọn của hộp thoại. Nếu nút chuyển sang màu xám hoặc bạn đã viết sai tên hàm hoặc bạn đang ở trong sổ làm việc sai hoặc nó không tồn tại! Thao tác này sẽ mở ra một hộp thoại khác mà bạn có thể nhập mô tả ngắn gọn về chức năng. Nhấp vào OK để lưu mô tả và (đây là một chút khó hiểu) nhấp vào Hủy để đóng hộp thoại Macro. Nhớ Lưu sổ làm việc có chứa hàm. Lần tới khi bạn truy cập vào Trình hướng dẫn chức năng, UDF của bạn sẽ có mô tả …​

img10

Giống như macro, các hàm do người dùng xác định có thể được sử dụng trong bất kỳ sổ làm việc nào khác miễn là sổ làm việc chứa chúng đang mở. Tuy nhiên nó không phải là thực hành tốt để làm điều này. Nhập hàm trong một sổ làm việc khác không đơn giản. Bạn phải thêm tên sổ làm việc chủ của nó vào tên hàm. Điều này không khó nếu bạn dựa vào Trình hướng dẫn chức năng, nhưng lại vụng về để viết ra thủ công. Trình hướng dẫn chức năng hiển thị tên đầy đủ của bất kỳ UDF nào trong các sổ làm việc khác …​

img11

Nếu bạn mở sổ làm việc mà bạn đã sử dụng hàm tại thời điểm đóng sổ làm việc chứa hàm, bạn sẽ thấy thông báo lỗi trong ô mà bạn đã sử dụng hàm. Excel đã quên nó! Mở sổ làm việc chủ của chức năng, tính toán lại và tất cả đều ổn trở lại. May mắn thay có một cách tốt hơn.

Nếu bạn muốn viết các Hàm do Người dùng Xác định để sử dụng trong nhiều sổ làm việc, phương pháp tốt nhất là tạo Phần bổ trợ Excel. Tìm hiểu cách thực hiện việc này trong hướng dẫn Xây dựng Bổ trợ Excel.

Phụ lục Tôi thực sự nên biết rõ hơn! Không bao giờ, không bao giờ, nói không bao giờ! Đã nói với bạn rằng không có hàm cung cấp tên ngày, giờ tôi đã nhớ ra hàm có thể. Nhìn vào ví dụ này …​

img12

Hàm TEXT trả về giá trị của ô dưới dạng văn bản ở định dạng số cụ thể. Vì vậy, trong ví dụ này, tôi có thể chọn = TEXT (A1, “ddd”) để trả về “Thu”, = TEXT (A1, “mmmm”) để trả về “September”, v.v. Trợ giúp của Excel có thêm một số ví dụ về cách sử dụng Chức năng này.

__ Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook.

Chúng tôi rất muốn nghe ý kiến ​​từ bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected] __