เชี่ยวชาญ Excel Tables ด้วย VBA: 9 เทคนิคที่พิสูจน์แล้ว

Table เป็นหนึ่งในวัตถุที่สำคัญที่สุดและใช้กันอย่างแพร่หลายขณะทำงานกับ VBA ใน Excel ในบทความนี้ ผมจะแสดงวิธีที่คุณสามารถใช้ Excel Table กับ VBA ได้

9 วิธีในการใช้ Excel Table กับ VBA

ตารางเป็นชนิดข้อมูลพิเศษใน Excel ที่แถวแรกมีส่วนหัว

ใน VBA มันอยู่ในหมวดหมู่ ListObject นี่คือวิธีที่คุณสามารถใช้ Excel Table ใน VBA ได้

1. สร้าง Table ด้วย VBA ใน Excel

ก่อนอื่น เราจะเรียนรู้วิธีสร้าง Table ด้วย VBA ใน Excel

ในการสร้าง Table ด้วย VBA ใน Excel ก่อนอื่น คุณต้องประกาศ Table เป็น ListObject

จากนั้น คุณต้องแทรก Table ในช่วงที่ต้องการ

Dim Table1 as ListObject
Set Table1 = ActiveSheet.ListObjects.Add(xlSrcRange, Range("B4:D13"), , xlYes)

[ที่นี่ Table1 คือชื่อของ Table ใน VBA และ B4:D13 คือช่วงใน Active Worksheet ของผมที่ต้องการตาราง คุณสามารถใช้สิ่งที่คุณชอบได้]

รันโค้ดนี้ มันจะสร้าง Table ในช่วง B4:D13 ของ Active Worksheet ของคุณ

ตอนนี้ หากต้องการ คุณสามารถเปลี่ยนชื่อตารางเป็นอะไรก็ได้ที่คุณชอบ

Table1.Name = "MyTable"

มันจะเปลี่ยนชื่อตารางเป็น MyTable

อ่านเพิ่มเติม: สร้าง Table ใน Excel ด้วย Shortcut

2. อ้างอิง Excel Table ด้วย VBA

เราเรียนรู้การสร้าง Table ด้วย VBA ใน Excel แล้ว

ตอนนี้ เพื่ออ้างอิง Table ที่สร้างไว้ใน Excel แล้ว คุณต้องประกาศ Table เป็น ListObject ก่อน

จากนั้น อ้างอิง Table ด้วยชื่อที่มีอยู่ใน Excel

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")

มันอ้างอิงถึงตาราง "MyTable" ใน Excel ด้วยชื่อ Table1

อ่านเพิ่มเติม: วิธีใช้ Excel Table Reference

3. ใส่ค่าใน Table ด้วย VBA ใน Excel

เพื่อใส่ค่าภายใน Table ก่อนอื่น คุณต้องอ้างอิง Table

จากนั้น คุณต้องใส่ค่าในเซลล์โดยใช้คุณสมบัติ Range.Cells ของ VBA

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Table1.Range.Cells(2, 1) = "A Tale of Two Cities"

รันโค้ดนี้ มันจะใส่สตริง "A Tale of Two Cities" ในแถว ที่ 2 และคอลัมน์ ที่ 1 ของตาราง MyTable ของ Active Worksheet

4. แทรกแถวและคอลัมน์ลงใน Table ด้วย VBA ใน Excel

เพื่อแทรกแถวหรือคอลัมน์ลงใน Table ด้วย VBA ก่อนอื่น ให้อ้างอิง Table

จากนั้น เพื่อเพิ่มแถว ให้แทรกคุณสมบัติ ListRows.Add

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Table1.ListRows.Add

มันเพิ่มแถวใหม่ที่ท้ายของตาราง MyTable

หรือเพื่อเพิ่มคอลัมน์ ให้แทรกคุณสมบัติ ListColumns.Add

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Table1.ListColumns.Add

มันจะเพิ่มคอลัมน์ใหม่ทางขวาของตาราง MyTable

อีกครั้ง เพื่อเพิ่มแถวหรือคอลัมน์ที่ตำแหน่งเฉพาะของ Table ให้ระบุตำแหน่งภายในคุณสมบัติ ListRows.Add หรือ ListColumns.Add

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Table1.ListRows.Add (4)

มันเพิ่มแถวใหม่เป็นแถว ที่ 4 ของตาราง MyTable

เหมือนกันสำหรับการเพิ่มคอลัมน์ใหม่

อ่านเพิ่มเติม: วิธีแทรกหรือลบแถวและคอลัมน์จาก Excel Table

5. อ่านข้อมูลจาก Table ด้วย VBA

เพื่ออ่านข้อมูลใดๆ จากตารางด้วย VBA ให้ใช้คุณสมบัติ Range.Cells ของ VBA

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
MsgBox Table1.Range.Cells(3, 1)

รันโค้ด มันจะแสดงค่าจากแถว ที่ 3 และคอลัมน์ ที่ 1 ของตาราง MyTable ซึ่งคือ The Forty Rules of Love

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

• VBA Refresh Pivot Table ใน Excel (5 ตัวอย่าง)
• วิธี Refresh All Pivot Tables ด้วย VBA (4 วิธี)
• แก้ไข Pivot Table ใน Excel (5 วิธี)
• วิธีอัปเดต Pivot Table Range (5 วิธีที่เหมาะสม)

6. วนลูปผ่านแถวหรือคอลัมน์ของ Table ด้วย VBA

เพื่อวนลูปผ่านแต่ละแถวของตารางด้วย VBA ให้ใช้คุณสมบัติ ListRows.Count

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
For i = 1 To Table1.ListRows.Count
    Table1.ListRows(i).Range.Select
Next i

มันจะวนผ่านแต่ละแถวของตาราง Table1 และเลือกมัน

คล้ายกัน เพื่อวนผ่านแต่ละคอลัมน์ของตาราง ให้ใช้คุณสมบัติ ListColumns.Count

7. ค้นหาค่าเฉพาะใน Table

เพื่อค้นหาค่าเฉพาะในแถวหรือคอลัมน์ของตารางด้วย VBA ให้ใช้ DataBodyRange.Rows.Find หรือคุณสมบัติ DataBodyRange.Columns.Find ของ VBA

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Set Value = Table1.DataBodyRange.Columns(1).Find("Mother", LookAt:=xlWhole)
MsgBox Value

มันจะค้นหาค่า "Mother" ในคอลัมน์ที่ 1 ของตาราง MyTable และคืนค่า หากพบ

8. ปรับขนาด Table ด้วย VBA ใน Excel

เพื่อปรับขนาดตารางให้เป็นรูปทรงใหม่ ก่อนอื่น ให้ตั้งค่า Range ที่ Table จะถูกปรับขนาด

จากนั้น ปรับขนาด Table ให้เป็น Range นั้น

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Dim Rng As Range
Set Rng = Range("B4:C8")
Table1.Resize Rng

มันตั้งค่า Range เป็น B4:C8 ก่อน แล้วปรับขนาดตาราง MyTable ให้เป็น Range ใหม่นั้น

9. ลบ Table ด้วย VBA ใน Excel

เพื่อลบตารางด้วย VBA ให้ใช้คุณสมบัติ Delete ของ VBA

Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("MyTable")
Table1.Delete

มันจะลบตาราง MyTable จาก Active Worksheet

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

A Table เป็น ListObject ใน VBA แต่ถ้าคุณต้องการเข้าถึงมันเป็น Range object ธรรมดา ให้ใช้คุณสมบัติ Table.Range ของ VBA มันจะคืนค่าตารางเป็น Range object และคุณจะสามารถเข้าถึงคุณสมบัติทั้งหมดของ Range object ของ VBA ได้

สรุป

ด้วยวิธีเหล่านี้ คุณสามารถใช้ตารางกับ VBA ได้ มีคำถามไหม? ถามได้เลยครับ

เพิ่มเติม

• วิธี Auto Refresh Pivot Table ใน Excel (2 วิธี)
• จัดกลุ่มคอลัมน์ใน Excel Pivot Table (2 วิธี)
• Pivot Table Custom Grouping: ด้วย 3 เกณฑ์
• ใช้สูตรใน Excel Table อย่างมีประสิทธิภาพ (พร้อม 4 ตัวอย่าง)