Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Trong Microsoft Excel, danh sách xác thực dữ liệu là một trong những công cụ cho phép bạn xác thực dữ liệu của mình trong trang tính. Nó thực sự giúp bạn tiết kiệm rất nhiều thời gian khi chọn một phạm vi giá trị cụ thể. Nếu ô của bạn chỉ nhận các giá trị cụ thể, bạn không cần phải nhập lại nhiều lần. Thay vào đó, bạn có thể tạo danh sách thả xuống để xác thực dữ liệu trong trang tính Excel của mình. Trong hướng dẫn này, bạn sẽ học chính xác cách tạo Danh sách xác thực dữ liệu đầu tiên của mình Từ một mảng với Excel VBA.

Hướng dẫn này sẽ đi vào điểm với các ví dụ phù hợp và hình ảnh minh họa phù hợp. Vì vậy, hãy đọc toàn bộ bài viết để làm giàu thêm kiến thức của bạn.

Tải xuống sổ làm việc thực hành này.

Xác thực dữ liệu trong Excel là gì?

Giờ đây, xác thực dữ liệu cho phép bạn kiểm soát đầu vào của mình trong một ô. Khi bạn có các giá trị giới hạn để nhập một trường, bạn có thể sử dụng danh sách thả xuống để xác thực dữ liệu của mình. Bạn không phải nhập dữ liệu bằng cách nhập lại nhiều lần. Danh sách xác thực dữ liệu cũng đảm bảo rằng đầu vào của bạn không có lỗi.

Bây giờ, tại sao nó được gọi là xác thực dữ liệu? Vì nó đảm bảo chỉ những dữ liệu hợp lệ mới được đưa vào danh sách.

Về cơ bản, nó hữu ích cho những người dùng lần đầu tiên được giới thiệu với tập dữ liệu. Họ không phải nhập dữ liệu theo cách thủ công. Thay vào đó, họ có thể chọn bất kỳ giá trị nào từ danh sách thả xuống hoặc danh sách xác thực dữ liệu mà bạn đã tạo.

Quy trình từng bước để tạo danh sách xác thực dữ liệu từ một mảng với Excel VBA

Trước hết, hãy xem tập dữ liệu của chúng tôi:

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Tại đây, chúng tôi có tập dữ liệu về một số nhân viên bán hàng. Chúng tôi có khu vực làm việc và bán sản phẩm của họ. Tại đây, chúng tôi sẽ tạo danh sách xác thực dữ liệu cho các cột Khu vực và Sản phẩm.

Danh sách xác thực dữ liệu của chúng tôi sẽ bao gồm:

Vùng :“Bắc”, “Nam”, “Đông”, “Tây”
Sản phẩm :“TV”, “Tủ lạnh”, “Di động”, “Máy tính xách tay”, “AC”

Bạn có thể tạo danh sách xác nhận theo cách truyền thống. Tuy nhiên, ở đây chúng tôi sẽ sử dụng mã VBA. Bây giờ, trong mã VBA, chúng ta sẽ đặt chúng vào một mảng. Và từ mảng đó, chúng tôi sẽ xác thực dữ liệu của mình.

Trong phần sau, tôi sẽ xây dựng mã theo cách từng bước. Tôi khuyên bạn nên xây dựng mã của bạn cùng với tôi. Bằng cách này, bạn sẽ học mã tốt hơn. Hãy bắt đầu.

Xây dựng mã VBA để tạo danh sách xác thực dữ liệu Excel từ mảng

Trong phần này, bạn sẽ học cách xây dựng danh sách xác thực dữ liệu của mình từ một mảng bằng cách sử dụng mã VBA trong Excel. Đây, Khu vực của chúng tôi và Sản phẩm cột sẽ chứa một danh sách thả xuống.

📌 Bước 1:Mở VBA Editor

Đầu tiên, nhấn Alt + F11 trên bàn phím của bạn để mở trình chỉnh sửa VBA.
Sau đó, chọn Chèn> Mô-đun .

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

📌 Bước 2:Khai báo thủ tục phụ

Bây giờ, hãy nhập mã sau:

Sub data_validation_from_array()

End Sub

Đây là thủ tục phụ của chúng tôi. Chúng tôi sẽ nhập tất cả các mã bên trong này.

📌 Bước 3:Khai báo các biến cần thiết

Bây giờ đã đến lúc khai báo các biến cần thiết mà chúng ta sẽ phải sử dụng thêm.

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

End Sub

Chúng tôi đang khai báo mảng của mình là Biến thể . Trong biến này, chúng ta sẽ có một số chuỗi.

region_range, product_range: Các biến này sẽ lưu trữ phạm vi cột Khu vực và Sản phẩm của chúng tôi

📌 Bước 4:Đặt Mảng

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

End Sub

Như bạn có thể thấy, chúng tôi đã lưu trữ một số chuỗi trong vùng và biến sản phẩm. Chúng tôi sẽ sử dụng chúng để tạo danh sách thả xuống của chúng tôi.

Đọc thêm: Cách tách một chuỗi thành một mảng trong VBA (3 cách)

📌 Bước 5:Đặt phạm vi xác thực dữ liệu

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

End Sub

Đặt region_range =Range (“C5:C10”) :Bằng dòng mã này, chúng tôi chỉ ra Vùng cột.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Đặt product_range =Range (“D5:D10”) :Và dòng mã này đang chỉ định Sản phẩm cột.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Đọc thêm: Cách chuyển đổi dải ô thành mảng trong Excel VBA (3 cách)

📌 Bước 6:Tạo danh sách xác thực dữ liệu trong cột khu vực

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

End Sub

Với region_range.Validation: Với dòng này, chúng tôi chọn cột Khu vực .

.Xóa: Nếu có bất kỳ danh sách xác thực nào đã tồn tại trước đó, danh sách đó sẽ xóa những danh sách đó.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join (region, “,”) :Chúng tôi đang thêm danh sách xác thực dữ liệu tại đây.

AlertStyle xác định loại cảnh báo mà chúng tôi sẽ hiển thị nếu người dùng đưa một mục nhập ngoài danh sách.
Công thức 1:=Tham gia (vùng, “,”) :Theo Công thức, chúng tôi đang cung cấp các giá trị trong danh sách xác nhận. Chúng tôi đã có một số chuỗi trong khu vực Bằng cách sử dụng nút Tham gia , chúng tôi đang kết hợp chúng với dấu phẩy phân tách (,). Các giá trị hoặc mục này sẽ là nguồn của chúng tôi trong danh sách xác thực.

.IgnoreBlank =True :Theo dòng này, chúng tôi đang cho phép các giá trị trống.

.InCellDropdown =True :Chúng tôi sẽ hiển thị danh sách thả xuống với các giá trị có thể chấp nhận được.

.ErrorTitle =“Lỗi” :Chúng tôi đang đặt tiêu đề của hộp thoại lỗi xác thực dữ liệu.

.ErrorMessage =“Vui lòng cung cấp thông tin đầu vào hợp lệ” :Nó sẽ đặt thông báo lỗi trong hộp thoại lỗi xác thực dữ liệu

.ShowInput =True: Nó sẽ hiển thị thông báo đầu vào xác thực dữ liệu bất cứ khi nào người dùng nhấp vào một ô trong phạm vi xác thực dữ liệu.

.ShowError =True: Nó sẽ hiển thị hộp thoại lỗi nếu người dùng đưa ra bất kỳ đầu vào không hợp lệ nào.

Đọc thêm: VBA để nhận giá trị duy nhất từ cột thành mảng trong Excel (3 tiêu chí)

Bài đọc tương tự

VBA để chuyển đổi mảng trong Excel (3 phương pháp)
Cách Đặt tên Mảng Bảng trong Excel (Với các Bước Dễ dàng)
Excel VBA để đọc tệp CSV thành mảng (4 ví dụ lý tưởng)
Excel VBA:Cách lọc với nhiều tiêu chí trong mảng (7 cách)

📌 Bước 7:Tạo danh sách xác thực dữ liệu trong cột sản phẩm

Sub data_validation_from_array()

Dim region, product As Variant
Dim region_range, product_range As Range

region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")

Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")

With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

With product_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With

End Sub

Với product_range.Validation: Với dòng này, chúng tôi chọn cột Sản phẩm .

.Xóa: Nếu có bất kỳ danh sách xác thực nào đã tồn tại trước đó, danh sách đó sẽ xóa những danh sách đó.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join (product, “,”) :Chúng tôi đang thêm danh sách xác thực dữ liệu tại đây.

AlertStyle xác định loại cảnh báo mà chúng tôi sẽ hiển thị nếu người dùng đưa một mục nhập ngoài danh sách.
Công thức 1:=Tham gia (vùng, “,”) :Theo Công thức , chúng tôi đang cung cấp các giá trị trong danh sách xác nhận. Chúng tôi có một số chuỗi trong sản phẩm Bằng cách sử dụng Tham gia , chúng tôi đang kết hợp chúng với dấu phẩy phân tách (,). Các giá trị hoặc mục này sẽ là nguồn của chúng tôi trong danh sách xác thực.

.IgnoreBlank =True :Theo dòng này, chúng tôi đang cho phép các giá trị trống.

.InCellDropdown =True :Chúng tôi sẽ hiển thị danh sách thả xuống với các giá trị có thể chấp nhận được.

.ErrorTitle =“Lỗi” :Chúng tôi đang đặt tiêu đề của hộp thoại lỗi xác thực dữ liệu.

.ErrorMessage =“Vui lòng cung cấp thông tin đầu vào hợp lệ” :Nó sẽ đặt thông báo lỗi trong hộp thoại lỗi xác thực dữ liệu

.ShowInput =True: Nó sẽ hiển thị thông báo đầu vào xác thực dữ liệu bất cứ khi nào người dùng nhấp vào một ô trong phạm vi xác thực dữ liệu.

.ShowError =True: Nó sẽ hiển thị hộp thoại lỗi nếu người dùng đưa ra bất kỳ đầu vào không hợp lệ nào.

Chạy mã VBA

Chúng tôi đã xây dựng mã VBA của chúng tôi. Bây giờ, đã đến lúc kiểm tra xem mã có hoạt động bình thường hay không. Chúng tôi sẽ chạy mã này trong trang tính hiện tại của chúng tôi.

Đầu tiên, nhấn Alt + F8 trên bàn phím của bạn để mở hộp thoại Macro.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Tiếp theo, chọn data_validation_from_array và nhấp vào Chạy .

Bây giờ, hãy nhấp vào bất kỳ ô nào trong Vùng cột.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Tại đây, bạn có thể thấy biểu tượng thả xuống bên cạnh ô. Bây giờ, hãy nhấp vào biểu tượng thả xuống.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Tại đây, bạn có thể xem tất cả các giá trị trong danh sách xác nhận. Chúng tôi đã đưa ra giá trị này trong khu vực của chúng tôi mảng. Bây giờ, hãy chọn dữ liệu cho từng ô.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Hãy kiểm tra Sản phẩm cột. Nhấp vào bất kỳ ô nào trong Sản phẩm cột.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Chúng tôi cũng có một biểu tượng thả xuống ở đây. Bây giờ, hãy nhấp vào menu thả xuống.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Như bạn có thể thấy, tất cả các giá trị mà chúng tôi đã đưa ra trong sản phẩm mảng trong mã VBA của chúng tôi được hiển thị ở đây. Vì vậy, chúng tôi đã sử dụng thành công mã VBA trong Excel để tạo danh sách xác thực từ một mảng.

Bây giờ, hãy đưa ra một giá trị không có trong mảng đã cho của chúng ta. Chúng tôi đang thử với sản phẩm “ Tai nghe ”.

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Bây giờ, nhấn Enter . Sau đó, bạn sẽ thấy như sau:

Excel VBA để tạo danh sách xác thực dữ liệu từ mảng

Như bạn có thể thấy, nó đang hiển thị một hộp thoại lỗi. Chúng tôi đã đặt tiêu đề lỗi và thông báo lỗi trong mã VBA của chúng tôi và nó đang hiển thị chính xác điều đó.

Đọc thêm: Tạo danh sách thả xuống xác thực dữ liệu với nhiều lựa chọn trong Excel

💬 Những điều cần nhớ

✎ Bạn có thể sao chép bất kỳ ô nào có xác thực dữ liệu và dán vào các ô khác. Các ô kết quả sẽ có cùng một danh sách xác thực dữ liệu.

✎ Đây không phải là một mảng động. Nếu bạn muốn mở rộng danh sách xác thực dữ liệu của mình, chỉ cần thêm chúng dưới dạng một chuỗi trong các mảng. Sẽ ổn thôi.

Kết luận

Để kết thúc, tôi hy vọng hướng dẫn này đã cung cấp cho bạn một phần kiến thức hữu ích để tạo danh sách xác thực dữ liệu trong Excel bằng mã VBA. Chúng tôi khuyên bạn nên tìm hiểu và áp dụng tất cả các hướng dẫn này cho tập dữ liệu của mình. Tải xuống sách bài tập thực hành và tự mình thử các tài liệu này. Ngoài ra, hãy đưa ra phản hồi trong phần bình luận. Phản hồi có giá trị của bạn giúp chúng tôi có động lực để tạo ra các hướng dẫn như thế này.

Đừng quên kiểm tra trang web Exceldemy.com của chúng tôi cho các vấn đề và giải pháp liên quan đến Excel khác nhau.

Tiếp tục học các phương pháp mới và tiếp tục phát triển!

Các bài viết có liên quan

Cách sử dụng câu lệnh IF trong công thức xác thực dữ liệu trong Excel (6 cách)
Sử dụng Dải ô được Đặt tên cho Danh sách Xác thực Dữ liệu với VBA trong Excel
Cách Chỉnh sửa Duy trì Mảng 2D trong Excel VBA (2 Cách Dễ dàng)
VBA trong Excel:Loại bỏ các bản sao khỏi một mảng (2 ví dụ)
Tính giá trị trung bình của một mảng bằng VBA (Macro, UDF và UserForm)