Tạo danh sách Kiểm tra dữ liệu trong Excel sử dụng Mảng VBA

Trong Microsoft Excel, danh sách kiểm tra dữ liệu là một trong những công cụ cho phép bạn kiểm tra dữ liệu của mình trong bảng 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 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 cho kiểm tra dữ liệu trong bảng 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 Kiểm tra dữ liệu đầu tiên của bạn Từ Mảng với Excel VBA.

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

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

Kiểm tra dữ liệu trong Excel là gì?

Bây giờ, kiểm tra dữ liệu cho phép bạn kiểm soát đầu vào của bạn 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 để kiểm tra dữ liệu của mình. Bạn không phải nhập dữ liệu bằng cách gõ lại nhiều lần. Danh sách kiểm tra dữ liệu cũng đảm bảo rằng các đầu vào của bạn không có lỗi.

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

Nói cơ bản, nó hữu ích cho những người dùng được giới thiệu lần đầu tiên cho bộ dữ liệu. Họ không phải nhập dữ liệu 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 kiểm tra dữ liệu mà bạn đã tạo.

Quy trình từng bước tạo Danh sách Kiểm tra dữ liệu từ Mảng với Excel VBA

Trước hết, hãy xem bộ dữ liệu của chúng ta:

Ở đây, chúng ta có bộ dữ liệu của một số nhân viên bán hàng. Chúng ta có khu vực làm việc và sản phẩm bán của họ. Ở đây, chúng ta sẽ tạo danh sách kiểm tra dữ liệu cho cột Khu vực và Sản phẩm.

Danh sách kiểm tra dữ liệu của chúng ta sẽ chứa:

• Khu vực: "Bắc", "Nam", "Đông", "Tây"
• Sản phẩm: "TV", "Tủ lạnh", "Điện thoại", "Laptop", "Điều hòa"

Bạn có thể tạo danh sách kiểm tra theo cách truyền thống. Nhưng ở đây chúng ta sẽ dùng mã VBA. Bây giờ, trong mã VBA, chúng ta sẽ đưa chúng vào một mảng. Và từ mảng đó, chúng ta sẽ kiểm tra 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 mình cùng với tôi. Theo cách đó, 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 Kiểm tra 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 kiểm tra dữ liệu từ mảng sử dụng mã VBA trong Excel. Ở đây, cột Khu vực và Sản phẩm của chúng ta sẽ chứa danh sách thả xuống.

📌 Bước 1: Mở Trình soạn thảo VBA

• Trước tiên, nhấn Alt+F11 trên bàn phím để mở trình soạn thảo VBA.
• Sau đó, chọn Chèn>Module.

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

Bây giờ, gõ mã sau:

Sub data_validation_from_array() End Sub

Đây là thủ tục con của chúng ta. Chúng ta sẽ gõ tất cả mã bên trong này.

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

Bây giờ là 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 ta khai báo mảng của mình là Variant. 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 ta

📌 Bước 4: Thiết lập 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 thấy, chúng ta đã lưu một số chuỗi trong biến region và product. Chúng ta sẽ dùng chúng để tạo danh sách thả xuống của mình.

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

📌 Bước 5: Thiết lập Phạm vi Kiểm tra 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

Set region_range = Range("C5:C10"): Bằng dòng mã này, chúng ta chỉ ra cột Khu vực.

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

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

📌 Bước 6: Tạo Danh sách Kiểm tra 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

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

.Delete: Nếu có danh sách kiểm tra trước đó, nó sẽ xóa những cái đó.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ","): Chúng ta đang thêm danh sách kiểm tra dữ liệu ở đây.

• AlertStyle xác định loại cảnh báo chúng ta sẽ hiển thị nếu người dùng nhập mục ngoài danh sách.
• Formula1:=Join(region, ","): Bằng Công thức, chúng ta cung cấp giá trị trong danh sách kiểm tra. Chúng ta có một số chuỗi trong region. Bằng cách dùng phương thức Join, chúng ta kết hợp chúng với dấu phân cách phẩy(,). Các giá trị hoặc mục này sẽ là nguồn trong danh sách kiểm tra.

.IgnoreBlank = True: Bằng dòng này, chúng ta cho phép giá trị trống.

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

.ErrorTitle = "Error": Chúng ta đặt tiêu đề của hộp thoại lỗi kiểm tra dữ liệu.

.ErrorMessage = "Please Provide a Valid Input": Nó sẽ đặt thông báo lỗi trong hộp thoại lỗi kiểm tra dữ liệu

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

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

Đọc thêm: VBA để lấy Giá trị duy nhất từ Cột vào Mảng trong Excel (3 tiêu chí)

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

• VBA chuyển vị 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 file CSV vào 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 Kiểm tra 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

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

.Delete: Nếu có danh sách kiểm tra trước đó, nó sẽ xóa những cái đó.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ","): Chúng ta đang thêm danh sách kiểm tra dữ liệu ở đây.

• AlertStyle xác định loại cảnh báo chúng ta sẽ hiển thị nếu người dùng nhập mục ngoài danh sách.
• Formula1:=Join(region, ","): Bằng Công thức, chúng ta cung cấp giá trị trong danh sách kiểm tra. Chúng ta có một số chuỗi trong product. Bằng cách dùng phương thức Join, chúng ta kết hợp chúng với dấu phân cách phẩy(,). Các giá trị hoặc mục này sẽ là nguồn trong danh sách kiểm tra.

.IgnoreBlank = True: Bằng dòng này, chúng ta cho phép giá trị trống.

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

.ErrorTitle = "Error": Chúng ta đặt tiêu đề của hộp thoại lỗi kiểm tra dữ liệu.

.ErrorMessage = "Please Provide a Valid Input": Nó sẽ đặt thông báo lỗi trong hộp thoại lỗi kiểm tra dữ liệu

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

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

Chạy mã VBA

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

Trước tiên, nhấn Alt+F8 trên bàn phím để mở hộp thoại Macro.

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

Bây giờ, nhấp vào bất kỳ ô nào trong cột Khu vực.

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

Ở đây, bạn có thể thấy tất cả giá trị trong danh sách kiểm tra. Chúng ta đã đưa giá trị này trong mảng region. Bây giờ, chọn dữ liệu cho từng ô.

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

Chúng ta cũng có biểu tượng thả xuống ở đây. Bây giờ, nhấp vào thả xuống.

Như bạn thấy, tất cả giá trị mà chúng ta đưa trong mảng product trong mã VBA của chúng ta được hiển thị ở đây. Vì vậy, chúng ta đã thành công sử dụng mã VBA trong Excel để tạo danh sách kiểm tra từ mảng.

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

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

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

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

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

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

✎ Đây không phải là mảng động. Nếu bạn muốn mở rộng danh sách kiểm tra dữ liệu, chỉ cần thêm chúng như chuỗi trong mảng. Nó sẽ hoạt động tốt.

Kết luận

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

Đừng quên truy cập trang web Exceldemy.com cho các vấn đề và giải pháp liên quan đến Excel.

Hãy tiếp tục học phương pháp mới và không ngừng phát triển!

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

• Cách sử dụng câu lệnh IF trong công thức Kiểm tra dữ liệu trong Excel (6 cách)
• Sử dụng Phạm vi có tên cho Danh sách Kiểm tra dữ liệu với VBA trong Excel
• Cách ReDim Preserve Mảng 2D trong Excel VBA (2 cách dễ dàng)
• Excel VBA: Loại bỏ trùng lặp khỏi Mảng (2 ví dụ)
• Tính trung bình cộng của Mảng với VBA (Macro, UDF, và UserForm)