สร้างรายการตรวจสอบข้อมูล (Data Validation Lists) ใน Excel ด้วย VBA Arrays

ใน Microsoft Excel รายการตรวจสอบข้อมูล (data validation list) เป็นหนึ่งในเครื่องมือที่ช่วยให้คุณตรวจสอบข้อมูลในแผ่นงานได้ มันช่วยประหยัดเวลามากในการเลือกช่วงค่าที่เฉพาะเจาะจง หากเซลล์ของคุณรับค่าเฉพาะบางค่า คุณไม่ต้องพิมพ์ซ้ำๆ อีกต่อไป แทนที่จะเป็นเช่นนั้น คุณสามารถ สร้างรายการแบบเลื่อนลง (drop down list) สำหรับการตรวจสอบข้อมูลในแผ่นงาน Excel ของคุณ ในบทช่วยสอนนี้ คุณจะได้เรียนรู้อย่างถ่องแท้ว่าจะสร้าง Data Validation List แรกของคุณ จาก Array (อาร์เรย์) ด้วย Excel VBA อย่างไร

บทช่วยสอนนี้จะตรงประเด็นพร้อมตัวอย่างที่เหมาะสมและภาพประกอบที่เหมาะสม โปรดอ่านบทความทั้งหมดเพื่อเพิ่มพูนความรู้

ดาวน์โหลดสมุดงานฝึกปฏิบัตินี้

Data Validation ใน Excel คืออะไร?

ตอนนี้ Data Validation ช่วยให้คุณควบคุมการป้อนข้อมูลในเซลล์ได้ เมื่อคุณมีค่าที่จำกัดที่จะป้อนในฟิลด์ คุณสามารถใช้รายการแบบเลื่อนลงเพื่อตรวจสอบข้อมูลของคุณ คุณไม่ต้องป้อนข้อมูลโดยการพิมพ์ซ้ำๆ Data validation list ยังรับประกันว่าการป้อนข้อมูลของคุณไม่มีข้อผิดพลาด

ตอนนี้ ทำไมมันถึงเรียกว่า data validation? เพราะมันทำให้มั่นใจได้ว่ามีเพียงข้อมูลที่ถูกต้องเท่านั้นที่จะเข้ารายการ

มันมีประโยชน์พื้นฐานสำหรับผู้ใช้ที่ได้รับแนะนำครั้งแรกกับชุดข้อมูล พวกเขาไม่ต้องป้อนข้อมูลด้วยตนเอง แทนที่จะเป็นเช่นนั้น พวกเขาสามารถเลือกค่าใดก็ได้จาก drop down หรือ data validation list ที่คุณสร้างขึ้น

ขั้นตอนการสร้าง Data Validation List จาก Array ด้วย Excel VBA แบบทีละขั้นตอน

ขั้นแรก มาดูชุดข้อมูลของเรา:

ที่นี่ เรามีชุดข้อมูลของพนักงานขายบางคน เรามีภูมิภาคทำงานและสินค้าที่ขาย ที่นี่ เราจะสร้าง data validation list สำหรับคอลัมน์ Region และ Product

Data validation list ของเราจะประกอบด้วย:

• Region (ภูมิภาค): "North", "South", "East", "West"
• Product (สินค้า): "TV", "Fridge", "Mobile", "Laptop", "AC"

คุณสามารถสร้าง validation list ด้วยวิธีแบบดั้งเดิมได้ แต่ที่นี่เราจะใช้โค้ด VBA ตอนนี้ ในโค้ด VBA เราจะใส่พวกเขาลงใน array และจาก array นั้น เราจะตรวจสอบข้อมูลของเรา

ในส่วนต่อไป ผมจะสร้างโค้ดในลักษณะทีละขั้นตอน ผมแนะนำให้คุณสร้างโค้ดของคุณพร้อมกับผม ด้วยวิธีนี้ คุณจะเรียนรู้โค้ดได้ดีขึ้น มาเริ่มกันเลย

สร้างโค้ด VBA เพื่อสร้าง Excel Data Validation List จาก Array

ในส่วนนี้ คุณจะได้เรียนรู้การสร้าง data validation list จาก array โดยใช้โค้ด VBA ใน Excel ที่นี่ คอลัมน์ Region (ภูมิภาค) และ Product (สินค้า) ของเราจะมี drop down list

📌 ขั้นตอนที่ 1: เปิด VBA Editor

• ขั้นแรก กด Alt+F11 บนคีย์บอร์ดเพื่อเปิด VBA editor
• จากนั้น เลือก Insert > Module

📌 ขั้นตอนที่ 2: ประกาศ Sub-procedure

ตอนนี้ พิมพ์โค้ดต่อไปนี้:

Sub data_validation_from_array()
End Sub

นี่คือ subprocedure ของเรา เราจะพิมพ์โค้ดทั้งหมดภายในนี้

📌 ขั้นตอนที่ 3: ประกาศตัวแปรที่จำเป็น

ตอนนี้ถึงเวลาประกาศตัวแปรที่จำเป็นที่เราจะต้องใช้ต่อไป

Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
End Sub

เรากำลังประกาศ array ของเราเป็น Variant ในตัวแปรนี้ เราจะมีสตริงบางอย่าง

region_range, product_range: ตัวแปรเหล่านี้จะเก็บช่วงของคอลัมน์ Region และ Product ของเรา

📌 ขั้นตอนที่ 4: ตั้งค่า Arrays

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

ตามที่คุณเห็น เราได้เก็บสตริงบางอย่างไว้ในตัวแปร region และ product เราจะใช้พวกมันเพื่อสร้าง drop down list ของเรา

อ่านเพิ่มเติม: วิธีแยก String เป็น Array ใน VBA (3 วิธี)

📌 ขั้นตอนที่ 5: ตั้งค่าช่วง Data Validation

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"): ด้วยบรรทัดโค้ดนี้ เรากำลังระบุคอลัมน์ Region

Set product_range = Range("D5:D10"): และบรรทัดโค้ดนี้กำหนดคอลัมน์ Product

อ่านเพิ่มเติม: วิธีแปลง Range เป็น Array ใน Excel VBA (3 วิธี)

📌 ขั้นตอนที่ 6: สร้าง Data Validation List ในคอลัมน์ Region

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: ด้วยบรรทัดนี้ เราเลือกคอลัมน์ Region

.Delete: หากมี validation list ที่มีอยู่ก่อนหน้านี้ มันจะลบพวกมัน

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ","): เรากำลังเพิ่ม data validation list ที่นี่

• AlertStyle กำหนดว่าเราจะแสดงการแจ้งเตือนประเภทไหน หากผู้ใช้ป้อนรายการนอกเหนือจากรายการ
• Formula1:=Join(region, ","): ด้วย Formula เรากำลังจัดหาค่าใน validation list เรามีสตริงบางอย่างใน region ด้วยการใช้เมธอด Join เรากำลังรวมพวกมันด้วยตัวคั่นคอมมา (,) ค่าเหล่านี้หรือรายการเหล่านี้จะเป็นแหล่งที่มา (source) ใน validation list ของเรา

.IgnoreBlank = True: ด้วยบรรทัดนี้ เราอนุญาตให้มีค่าว่าง

.InCellDropdown = True: เราจะแสดง drop down list พร้อมค่าที่ยอมรับได้

.ErrorTitle = "Error": เรากำลังตั้งค่าชื่อเรื่องของกล่องโต้ตอบข้อผิดพลาด data-validation

.ErrorMessage = "Please Provide a Valid Input": มันจะตั้งค่าข้อความข้อผิดพลาดในกล่องโต้ตอบข้อผิดพลาด data validation

.ShowInput = True: มันจะแสดงข้อความป้อนข้อมูล data validation ทุกครั้งที่ผู้ใช้คลิกที่เซลล์ในช่วง data validation

.ShowError = True: มันจะแสดงกล่องโต้ตอบข้อผิดพลาด หากผู้ใช้ให้การป้อนข้อมูลที่ไม่ถูกต้อง

อ่านเพิ่มเติม: VBA เพื่อดึงค่าที่ไม่ซ้ำจากคอลัมน์เข้า Array ใน Excel (3 เกณฑ์)

บทความคล้ายกัน

• VBA เพื่อ Transpose Array ใน Excel (3 วิธี)
• วิธีตั้งชื่อ Table Array ใน Excel (พร้อมขั้นตอนง่ายๆ)
• Excel VBA อ่านไฟล์ CSV เข้า Array (4 ตัวอย่างที่เหมาะสม)
• Excel VBA: วิธี Filter ด้วยเกณฑ์หลายอย่างใน Array (7 วิธี)

📌 ขั้นตอนที่ 7: สร้าง Data Validation List ในคอลัมน์ Product

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: ด้วยบรรทัดนี้ เราเลือกคอลัมน์ Product

.Delete: หากมี validation list ที่มีอยู่ก่อนหน้านี้ มันจะลบพวกมัน

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ","): เรากำลังเพิ่ม data validation list ที่นี่

• AlertStyle กำหนดว่าเราจะแสดงการแจ้งเตือนประเภทไหน หากผู้ใช้ป้อนรายการนอกเหนือจากรายการ
• Formula1:=Join(product, ","): ด้วย Formula เรากำลังจัดหาค่าใน validation list เรามีสตริงบางอย่างใน product ด้วยการใช้เมธอด Join เรากำลังรวมพวกมันด้วยตัวคั่นคอมมา (,) ค่าเหล่านี้หรือรายการเหล่านี้จะเป็นแหล่งที่มา (source) ใน validation list ของเรา

.IgnoreBlank = True: ด้วยบรรทัดนี้ เราอนุญาตให้มีค่าว่าง

.InCellDropdown = True: เราจะแสดง drop down list พร้อมค่าที่ยอมรับได้

.ErrorTitle = "Error": เรากำลังตั้งค่าชื่อเรื่องของกล่องโต้ตอบข้อผิดพลาด data-validation

.ErrorMessage = "Please Provide a Valid Input": มันจะตั้งค่าข้อความข้อผิดพลาดในกล่องโต้ตอบข้อผิดพลาด data validation

.ShowInput = True: มันจะแสดงข้อความป้อนข้อมูล data validation ทุกครั้งที่ผู้ใช้คลิกที่เซลล์ในช่วง data validation

.ShowError = True: มันจะแสดงกล่องโต้ตอบข้อผิดพลาด หากผู้ใช้ให้การป้อนข้อมูลที่ไม่ถูกต้อง

รันโค้ด VBA

เราสร้างโค้ด VBA ของเราแล้ว ตอนนี้ถึงเวลาตรวจสอบว่าโค้ดทำงานถูกต้องหรือไม่ เราจะรันโค้ดนี้ในแผ่นงานปัจจุบันของเรา

ขั้นแรก กด Alt+F8 บนคีย์บอร์ดเพื่อเปิดกล่องโต้ตอบ Macro

ต่อไป เลือก data_validation_from_array และคลิก Run (รัน)

ตอนนี้ คลิกที่เซลล์ใดๆ ในคอลัมน์ Region

ที่นี่ คุณเห็นไอคอน dropdown ข้างๆ เซลล์ ตอนนี้ คลิกที่ไอคอน dropdown

ที่นี่ คุณเห็นค่าทั้งหมดใน validation list เราให้ค่านี้ใน region array ของเรา ตอนนี้ เลือกข้อมูลสำหรับแต่ละเซลล์

มาตรวจสอบคอลัมน์ Product คลิกที่เซลล์ใดๆ ในคอลัมน์ Product

เรายังได้ไอคอน dropdown ที่นี่ ตอนนี้ คลิกที่ dropdown

ตามที่คุณเห็น ค่าทั้งหมดที่เราให้ใน product array ในโค้ด VBA ของเราถูกแสดงที่นี่ ดังนั้น เราใช้โค้ด VBA ใน Excel เพื่อสร้าง validation list จาก array สำเร็จแล้ว

ตอนนี้ ลองให้ค่าที่ไม่อยู่ใน array ที่เราให้ เราลองกับสินค้า "Headphone"

ตอนนี้ กด Enter หลังจากนั้น คุณจะเห็นสิ่งต่อไปนี้:

ตามที่คุณเห็น มันกำลังแสดงกล่องโต้ตอบข้อผิดพลาด เราตั้งค่า error title และ error message ในโค้ด VBA ของเราไว้แล้ว และมันแสดงตรงตามนั้น

อ่านเพิ่มเติม: สร้าง Data Validation Drop-Down List พร้อมการเลือกหลายรายการใน Excel

💬 สิ่งที่ควรจดจำ

✎ คุณสามารถคัดลอกเซลล์ใดๆ ที่มี data validation และวางไปยังเซลล์อื่น เซลล์ที่ได้จะมี data validation list เดียวกัน

✎ นี่ไม่ใช่ dynamic array หากคุณต้องการขยาย data validation list ของคุณ แค่เพิ่มพวกมันเป็นสตริงใน arrays มันจะทำงานได้ดี

สรุป

โดยสรุป หวังว่าบทช่วยสอนนี้จะให้ความรู้ที่มีประโยชน์ในการสร้าง data validation list ใน Excel โดยใช้โค้ด VBA เราแนะนำให้คุณเรียนรู้และนำไปใช้ตามคำแนะนำทั้งหมดนี้กับชุดข้อมูลของคุณ ดาวน์โหลดสมุดงานฝึกปฏิบัติและลองทำด้วยตัวเอง ด้วย อย่าลืมให้คำติชมในส่วนความคิดเห็น คำติชมที่มีค่าของคุณทำให้เราได้แรงจูงใจสร้างบทช่วยสอนแบบนี้ต่อไป

อย่าลืมตรวจสอบเว็บไซต์ Exceldemy.com สำหรับปัญหาและวิธีแก้ไขเกี่ยวกับ Excel ต่างๆ

เรียนรู้วิธีใหม่ๆ และเติบโตต่อไป!

บทความที่เกี่ยวข้อง

• วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)
• ใช้ Named Range สำหรับ Data Validation List กับ VBA ใน Excel
• วิธี ReDim Preserve 2D Array ใน Excel VBA (2 วิธีง่ายๆ)
• Excel VBA: ลบค่าซ้ำจาก Array (2 ตัวอย่าง)
• คำนวณค่าเฉลี่ยของ Array ด้วย VBA (Macro, UDF, และ UserForm)