VBA เป็นส่วนหนึ่งของ Microsoft Office Suite มาหลายปี แม้จะไม่มีฟังก์ชันและพลังเต็มรูปแบบของแอปพลิเคชัน VB ทั้งหมด VBA ให้ความยืดหยุ่นแก่ผู้ใช้ Office ในการผสานรวมผลิตภัณฑ์ Office และทำอัตโนมัติงานที่คุณทำในพวกมัน
เครื่องมือทรงพลังที่สุดใน VBA หนึ่งคือความสามารถในการโหลดช่วงข้อมูลทั้งหมดเข้าไปในตัวแปรเดียว ที่เรียกว่า Array (อาร์เรย์) ด้วยการโหลดข้อมูลแบบนี้ คุณสามารถจัดการหรือคำนวณช่วงข้อมูลนั้นได้หลายวิธี
VBA Array คืออะไร? ในบทความนี้เราจะตอบคำถามนี้ และแสดงวิธีใช้ในสคริป्ट VBA ของคุณเอง
VBA Array คืออะไร
การใช้ VBA Array ใน Excel ง่ายมาก แต่วิธีคิดของ Array อาจซับซ้อนเล็กน้อยหากไม่เคยใช้
คิด Array เหมือนกล่องที่มีช่องแบ่งภายใน Array มิติเดียว (One-dimensional) คือกล่องที่มีแถวช่องเดียว Array สองมิติ (Two-dimensional) คือกล่องที่มีแถวช่องสองแถว
คุณสามารถใส่ข้อมูลในแต่ละช่องของ "กล่อง" นี้ได้ในลำดับใดก็ได้
ในตอนต้นของสคริปต์ VBA คุณต้องกำหนด "กล่อง" นี้โดยการประกาศ VBA Array ดังนั้น เพื่อสร้าง Array ที่เก็บชุดข้อมูลหนึ่งชุด (One-dimension Array) คุณจะเขียนบรรทัดต่อไปนี้
Dim arrMyArray(1 To 6) As String
ภายหลังในโปรแกรม คุณสามารถวางข้อมูลในช่องใดของ Array นี้ได้โดยอ้างอิงช่องภายในวงเล็บ
arrMyArray(1) = "Ryan Dube"
คุณสร้าง Array สองมิติได้ด้วยบรรทัดต่อไปนี้
Dim arrMyArray(1 To 6, 1 To 2) As Integer
ตัวเลขแรกแทนแถว ตัวที่สองแทนคอลัมน์ Array ด้านบนจึงเก็บช่วงที่มี 6 แถวและ 2 คอลัมน์ได้
คุณโหลดข้อมูลเข้า Element ใดของ Array นี้ได้ดังนี้
arrMyArray(1, 2) = 3
นี่จะโหลดตัวเลข 3 เข้าเซลล์ B1
Array สามารถเก็บข้อมูลประเภทใดก็ได้เหมือนตัวแปรทั่วไป เช่น String, Boolean, Integer, Float และอื่นๆ
ตัวเลขในวงเล็บก็สามารถเป็นตัวแปรได้ โปรแกรมเมอร์มักใช้ For Loop นับผ่านทุกช่องของ Array และโหลดเซลล์ข้อมูลในสเปรดชีตเข้า Array คุณจะเห็นวิธีทำในภายหลัง
วิธีเขียนโปรแกรม VBA Array ใน Excel
มาดูโปรแกรมง่ายๆ ที่คุณอาจต้องการโหลดข้อมูลจากสเปรดชีตเข้า Array หลายมิติ
ตัวอย่าง ให้ดูสเปรดชีตขายสินค้า ที่คุณต้องการดึงชื่อพนักงานขาย รายการสินค้า และยอดขายรวม ออกจากสเปรดชีต
โปรดทราบว่าใน VBA เมื่ออ้างอิงแถวหรือคอลัมน์ นับแถวและคอลัมน์เริ่มจากมุมซ้ายบนที่ 1 ดังนั้น คอลัมน์ Rep คือ 3, Item คือ 4, Total คือ 7
เพื่อโหลด 3 คอลัมน์นี้ทั้ง 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 Loop แรกต้องเริ่มที่ 2 แทน 1 หมายความว่าคุณต้องลบ 1 สำหรับค่าแถวของ Array เมื่อโหลดค่าเซลล์เข้า Array ด้วย Cells(i, j).Value
วางสคริปต์ VBA Array ไว้ที่ไหน
เพื่อเขียนโปรแกรม VBA ใน Excel คุณต้องใช้ VBA Editor เข้าถึงได้โดยเลือกเมนู Developer และเลือก View Code ในส่วน Controls บนริบบอน
หากไม่เห็น Developer ในเมนู ต้องเพิ่มมัน เข้าได้โดยเลือก File และ Options เปิดหน้าต่าง Excel Options
เปลี่ยน Choose commands from dropdown เป็น All Commands เลือก Developer จากเมนูซ้าย กดปุ่ม Add ย้ายไปแผงขวา เลือกเช็กบอกซ์เปิดใช้งาน คลิก OK เสร็จ
เมื่อหน้าต่าง Code Editor เปิด ตรวจสอบว่าชีตที่มีข้อมูลถูกเลือกในแผงซ้าย เลือก Worksheet ในดรอปดาวน์ซ้าย และ Activate ในขวา จะสร้าง Subroutine ใหม่ชื่อ Worksheet_Activate()
ฟังก์ชันนี้จะรันทุกครั้งที่เปิดไฟล์สเปรดชีต วางโค้ดในบานหน้าต่างสคริปต์ภายใน Subroutine นี้
สคริปต์นี้จะทำงานผ่าน 12 แถว และโหลดชื่อพนักงานขายจากคอลัมน์ 3, รายการจากคอลัมน์ 4, และยอดขายรวมจากคอลัมน์ 7
เมื่อลูป For ทั้งสองจบ Array สองมิติ arrMyArray จะมีข้อมูลทั้งหมดที่คุณระบุจากชีตต้นฉบับ
การจัดการ Array ใน 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
นี่จะ "ปลดโหลด" Array ทั้งหมดเข้า Sheet2 พร้อมแถวเพิ่มเติมที่มียอดรวมคูณ 5% สำหรับจำนวนภาษี
ชีตผลลัพธ์จะเป็นดังนี้
เห็นได้ว่า VBA Arrays ใน Excel มีประโยชน์มากและหลากหลายเท่ากับเทคนิค Excel อื่นๆ
ตัวอย่างข้างต้นเป็นการใช้ Array แบบง่าย คุณสร้าง Array ใหญ่กว่านี้ได้มาก และทำ Sorting, Averaging หรือฟังก์ชันอื่นๆ กับข้อมูลที่เก็บใน Array ได้
หากต้องการสร้างสรรค์จริง คุณสามารถสร้าง Array สองตัว ที่มีช่วงเซลล์จากสองชีตต่างกัน และคำนวณระหว่าง Element ของแต่ละ Array
แอปพลิเคชันจำกัดเพียงแค่จินตนาการของคุณ
