Tạo hàm VBA để trả về mảng
Như tiêu đề cho thấy, chúng ta sẽ học cách tạo một hàm do người dùng xác định trong Excel trả về một mảng. Chúng ta đã học cách `liên kết: / vba-người dùng-xác định-các chức năng [tạo một chức năng do người dùng xác định trong VBA] ‘. Vì vậy, không lãng phí bất kỳ thời gian nào, hãy bắt đầu với hướng dẫn.
Hàm mảng là gì?
Hàm mảng là các hàm trả về một mảng khi được sử dụng. Hàm này được sử dụng với tổ hợp phím CTRL + SHIFT + ENTER và đây là lý do tại sao chúng tôi thích gọi hàm hoặc công thức mảng dưới dạng hàm CSE và công thức.
Tạo một hàm mảng UDF trong VBA
Vì vậy, kịch bản là tôi chỉ muốn trả về 3 số chẵn đầu tiên bằng cách sử dụng hàm ThreeEven ().
Mã sẽ như thế này.
Function ThreeEven() As Integer() 'define array Dim numbers(2) As Integer 'Assign values to array numbers(0) = 0 numbers(1) = 2 numbers(2) = 4 'return values ThreeEven = numbers End Function
Hãy để chúng tôi sử dụng chức năng này trên trang tính.
You can see that, we first select three cells (horizontally, for vertical we have to use two-dimensional array. We have it covered below.). Then we start writing our formula. Then we hit CTRL+SHIFT+ENTER. This fills the selected cells with the array values.
Lưu ý:
-
Trong thực tế, bạn sẽ không biết bạn sẽ cần bao nhiêu tế bào. Trong trường hợp đó, luôn luôn chọn nhiều ô hơn độ dài mảng mong đợi. Hàm sẽ điền vào các ô bằng mảng và các ô thừa sẽ hiển thị lỗi # N / A.
-
Theo mặc định, hàm mảng này trả về các giá trị trong một mảng ngang.
Nếu bạn cố gắng chọn các ô dọc, tất cả các ô sẽ chỉ hiển thị giá trị đầu tiên của mảng.
Nó hoạt động như thế nào?
Để tạo một hàm mảng, bạn phải làm theo cú pháp này.
Function functionName(variables) As returnType() dim resultArray(length) as dataType 'Assign values to array here functionName =resultArray End Function
Khai báo hàm phải được định nghĩa như trên. Điều này đã khai báo rằng nó là một hàm mảng.
Trong khi sử dụng nó trên trang tính, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER. Nếu không, nó sẽ chỉ trả về giá trị đầu tiên của mảng.
Hàm mảng VBA để trả về mảng dọc
Để làm cho hàm mảng UDF của bạn hoạt động theo chiều dọc, bạn không cần phải làm gì nhiều. Chỉ cần khai báo các mảng là một mảng hai chiều. Sau đó, ở thứ nguyên thứ nhất, hãy thêm các giá trị và để trống thứ nguyên khác. Đây là cách bạn thực hiện:
Function ThreeEven() As Integer() 'define array Dim numbers(2,0) As Integer 'Assign values to array numbers(0,0) = 0 numbers(1,0) = 2 numbers(2,0) = 4 'return values ThreeEven = numbers End Function
Đây là cách bạn sử dụng nó trên trang tính.
===
UDF Array Function with Arguments in Excel* In the above examples, we simply printed sum static values on the sheet.
Giả sử chúng ta muốn hàm của mình chấp nhận một đối số phạm vi, thực hiện một số thao tác trên chúng và trả về mảng kết quả.
Ví dụ Thêm “-done” vào mọi giá trị trong phạm vi
Bây giờ, tôi biết điều này có thể được thực hiện dễ dàng nhưng chỉ để cho bạn thấy cách bạn có thể sử dụng các hàm mảng VBA do người dùng định nghĩa để giải quyết vấn đề.
Function CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Create a collection Dim col As New Collection 'Adding values to collection On Error Resume Next For Each v In rng col.Add v Next On Error GoTo 0 'completing operation on each value and adding them to Array ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Next CONCATDone = resulArr End Function
Giải thích:
Hàm trên sẽ chấp nhận một phạm vi làm đối số và nó sẽ thêm “-done” vào mỗi giá trị trong phạm vi.
Bạn có thể thấy rằng chúng tôi đã sử dụng một bộ sưu tập VBA ở đây để giữ các giá trị của mảng và sau đó chúng tôi đã thực hiện thao tác của mình trên mỗi giá trị và thêm chúng trở lại trên một mảng hai chiều.
Vì vậy, các bạn, đây là cách bạn có thể tạo một hàm mảng VBA tùy chỉnh có thể trả về một mảng. Tôi hy vọng nó đủ giải thích. Nếu bạn có bất kỳ câu hỏi nào liên quan đến bài viết này, hãy đưa nó vào phần bình luận bên dưới.