VBA เป็นส่วนหนึ่งของชุดโปรแกรม Microsoft Office มาหลายปีแล้ว แม้ว่าจะไม่มีฟังก์ชันการทำงานเต็มรูปแบบและพลังของแอปพลิเคชัน VB เต็มรูปแบบ แต่ VBA ก็ให้ความยืดหยุ่นแก่ผู้ใช้ Office ในการผสานรวมผลิตภัณฑ์ Office และทำให้งานของคุณเป็นไปโดยอัตโนมัติ
หนึ่งในเครื่องมือที่ทรงพลังที่สุดที่มีอยู่ใน VBA คือความสามารถในการโหลดข้อมูลทั้งหมดลงในตัวแปรเดียวที่เรียกว่าอาร์เรย์ เมื่อโหลดข้อมูลด้วยวิธีนี้ คุณจะจัดการหรือคำนวณช่วงข้อมูลนั้นได้หลายวิธี
ดังนั้นอาร์เรย์ VBA คืออะไร? ในบทความนี้ เราจะตอบคำถามนั้น และแสดงวิธีใช้ในสคริปต์ VBA ของคุณเอง
อาร์เรย์ VBA คืออะไร
การใช้อาร์เรย์ VBA ใน Excel ทำได้ง่ายมาก แต่การทำความเข้าใจแนวคิดของอาร์เรย์อาจซับซ้อนเล็กน้อยหากคุณไม่เคยใช้มาก่อน
ลองนึกถึงอาร์เรย์เหมือนกล่องที่มีส่วนต่างๆ อยู่ข้างใน อาร์เรย์หนึ่งมิติคือกล่องที่มีส่วนหนึ่งบรรทัด อาร์เรย์สองมิติคือกล่องที่มีส่วนสองบรรทัด
คุณสามารถใส่ข้อมูลลงในแต่ละส่วนของ “กล่อง” นี้ตามลำดับที่คุณต้องการ
ที่จุดเริ่มต้นของสคริปต์ VBA คุณต้องกำหนด "กล่อง" นี้โดยกำหนดอาร์เรย์ VBA ของคุณ ดังนั้น ในการสร้างอาร์เรย์ที่สามารถเก็บข้อมูลชุดหนึ่งได้ (อาร์เรย์หนึ่งมิติ) คุณจะต้องเขียนบรรทัดต่อไปนี้
Dim arrMyArray(1 To 6) As String
ภายหลังในโปรแกรมของคุณ คุณสามารถใส่ข้อมูลลงในส่วนใดก็ได้ของอาร์เรย์นี้โดยอ้างอิงส่วนในวงเล็บ
arrMyArray(1) = "Ryan Dube"
คุณสามารถสร้างอาร์เรย์สองมิติโดยใช้บรรทัดต่อไปนี้
Dim arrMyArray(1 To 6,1 to 2) As Integer
ตัวเลขตัวแรกแสดงถึงแถว และตัวที่สองคือคอลัมน์ ดังนั้นอาร์เรย์ด้านบนจึงสามารถเก็บช่วงที่มี 6 แถว 2 คอลัมน์ได้
คุณสามารถโหลดองค์ประกอบใด ๆ ของอาร์เรย์นี้ด้วยข้อมูลดังนี้
arrMyArray(1,2) = 3
นี่จะโหลด 3 ลงในเซลล์ B1
อาร์เรย์สามารถเก็บข้อมูลประเภทใดก็ได้เป็นตัวแปรปกติ เช่น สตริง บูลีน จำนวนเต็ม โฟลต และอื่นๆ
ตัวเลขในวงเล็บสามารถเป็นตัวแปรได้เช่นกัน โปรแกรมเมอร์มักใช้ For Loop เพื่อนับผ่านทุกส่วนของอาร์เรย์ และโหลดเซลล์ข้อมูลในสเปรดชีตลงในอาร์เรย์ คุณจะเห็นวิธีการดำเนินการนี้ในบทความนี้
วิธีการตั้งโปรแกรมอาร์เรย์ VBA ใน Excel
มาดูโปรแกรมง่ายๆ ที่คุณอาจต้องการโหลดข้อมูลจากสเปรดชีตลงในอาร์เรย์หลายมิติ
ตัวอย่างเช่น มาดูสเปรดชีตการขายผลิตภัณฑ์ ซึ่งคุณต้องการดึงชื่อตัวแทนขาย รายการ และยอดขายรวมออกจากสเปรดชีต
โปรดทราบว่าใน VBA เมื่อคุณอ้างอิงแถวหรือคอลัมน์ คุณจะนับแถวและคอลัมน์ที่เริ่มต้นจากด้านซ้ายบนที่ 1 ดังนั้นคอลัมน์ตัวแทนคือ 3 คอลัมน์รายการคือ 4 และคอลัมน์ทั้งหมดคือ 7
หากต้องการโหลดสามคอลัมน์นี้จากทั้ง 11 แถว คุณจะต้องเขียนสคริปต์ต่อไปนี้
Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i
คุณจะสังเกตเห็นว่าในการข้ามแถวส่วนหัว หมายเลขแถวใน For look แรกต้องเริ่มต้นที่ 2 แทนที่จะเป็น 1 ซึ่งหมายความว่าคุณต้องลบ 1 สำหรับค่าแถวอาร์เรย์เมื่อคุณโหลดค่าเซลล์ ลงในอาร์เรย์โดยใช้ Cells(i, j).Value.
จะแทรกสคริปต์ VBA Array ของคุณได้ที่ไหน
ในการวางโปรแกรมสคริปต์ VBA ใน Excel คุณต้องใช้ตัวแก้ไข VBA คุณสามารถเข้าถึงได้โดยเลือก นักพัฒนา เมนูและเลือก ดูโค้ด ในส่วนการควบคุมของริบบอน
หากคุณไม่เห็นผู้พัฒนาในเมนู คุณจะต้องเพิ่มเข้าไป ในการดำเนินการนี้ ให้เลือก ไฟล์ และ ตัวเลือก เพื่อเปิดหน้าต่างตัวเลือกของ Excel
เปลี่ยนคำสั่ง select จากดรอปดาวน์เป็น All Commands . เลือกนักพัฒนา จากเมนูด้านซ้ายและเลือก เพิ่ม ปุ่มเพื่อย้ายไปยังบานหน้าต่างด้านขวา เลือกช่องทำเครื่องหมายเพื่อเปิดใช้งานและเลือก ตกลง ให้เสร็จสิ้น
เมื่อหน้าต่างตัวแก้ไขโค้ดเปิดขึ้น ตรวจสอบให้แน่ใจว่าได้เลือกแผ่นงานข้อมูลของคุณในบานหน้าต่างด้านซ้าย เลือก แผ่นงาน ในเมนูแบบเลื่อนลงด้านซ้ายและเปิดใช้งาน ทางด้านขวา สิ่งนี้จะสร้างรูทีนย่อยใหม่ชื่อ Worksheet_Activate()
ฟังก์ชันนี้จะทำงานทุกครั้งที่เปิดไฟล์สเปรดชีต วางโค้ดลงในบานหน้าต่างสคริปต์ภายในรูทีนย่อยนี้
สคริปต์นี้จะทำงานใน 12 แถว และจะโหลดชื่อตัวแทนจากคอลัมน์ 3 รายการจากคอลัมน์ 4 และยอดขายรวมจากคอลัมน์ 7
เมื่อ For loops ทั้งคู่เสร็จสิ้น อาร์เรย์สองมิติ arrMyArray จะมีข้อมูลทั้งหมดที่คุณระบุจากชีตต้นฉบับ
การจัดการอาร์เรย์ใน Excel VBA
สมมติว่าคุณต้องการใช้ภาษีขาย 5% กับราคาขายสุดท้ายทั้งหมด จากนั้นเขียนข้อมูลทั้งหมดลงในชีตใหม่
คุณสามารถทำได้โดยเพิ่ม For loop หลังแรก โดยใช้คำสั่งให้เขียนผลลัพธ์ไปยังชีตใหม่
For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k
การดำเนินการนี้จะ "ยกเลิกการโหลด" อาร์เรย์ทั้งหมดลงใน Sheet2 โดยมีแถวเพิ่มเติมที่มียอดรวมคูณด้วย 5% สำหรับจำนวนภาษี
แผ่นงานที่ได้จะมีลักษณะดังนี้
อย่างที่คุณเห็น อาร์เรย์ VBA ใน Excel มีประโยชน์อย่างยิ่งและใช้งานได้หลากหลายเช่นเดียวกับเคล็ดลับอื่นๆ ของ Excel
ตัวอย่างข้างต้นเป็นการใช้อาร์เรย์ที่ง่ายมาก คุณสามารถสร้างอาร์เรย์ที่มีขนาดใหญ่กว่ามากและทำการจัดเรียง หาค่าเฉลี่ย หรือฟังก์ชันอื่นๆ มากมายกับข้อมูลที่คุณจัดเก็บไว้ในอาร์เรย์ได้
หากคุณต้องการสร้างสรรค์ผลงานอย่างแท้จริง คุณสามารถสร้างอาร์เรย์ 2 ชุดที่มีช่วงของเซลล์จากแผ่นงาน 2 แผ่น และทำการคำนวณระหว่างองค์ประกอบของแต่ละอาร์เรย์
แอปพลิเคชันถูกจำกัดด้วยจินตนาการของคุณเท่านั้น
