VBA Array ใน Excel คืออะไรและจะเขียนโปรแกรมได้อย่างไร
VBAเป็นส่วนหนึ่งของชุดโปรแกรม Microsoft Office(Microsoft Office)มาหลายปีแล้ว แม้ว่าจะไม่มีฟังก์ชันการทำงานเต็มรูปแบบและพลังของแอปพลิเคชัน VB เต็มรูปแบบ แต่VBA ก็ ให้ความยืดหยุ่นแก่ผู้ใช้Office ในการผสานรวมผลิตภัณฑ์ (Office)Officeและทำให้งานของคุณทำงานโดยอัตโนมัติ
หนึ่งในเครื่องมือที่ทรงพลังที่สุดที่มีอยู่ในVBAคือความสามารถในการโหลดข้อมูลทั้งหมดลงในตัวแปรเดียวที่เรียกว่าอาร์เรย์ การโหลดข้อมูลด้วยวิธีนี้ทำให้คุณสามารถจัดการหรือคำนวณช่วงข้อมูลนั้นได้หลากหลายวิธี
ดังนั้นอาร์เรย์VBA คืออะไร? (VBA)ในบทความนี้ เราจะตอบคำถามนั้น และแสดงวิธีใช้ในสคริปต์ VBA ของคุณ(your own VBA script)เอง
อาร์เรย์ VBA คืออะไร?
การใช้อาร์เรย์VBA ใน (VBA)Excelทำได้ง่ายมาก แต่การทำความเข้าใจแนวคิดของอาร์เรย์อาจซับซ้อนเล็กน้อยหากคุณไม่เคยใช้มาก่อน
ลองนึกถึง(Think)อาร์เรย์เหมือนกล่องที่มีส่วนต่างๆ อยู่ข้างใน อาร์เรย์หนึ่งมิติคือกล่องที่มีส่วนหนึ่งบรรทัด อาร์เรย์สองมิติคือกล่องที่มีส่วนสองบรรทัด
คุณสามารถใส่ข้อมูลลงในแต่ละส่วนของ "กล่อง" นี้ในลำดับใดก็ได้ที่คุณต้องการ
ที่จุดเริ่มต้นของ สคริปต์ VBAคุณต้องกำหนด "กล่อง" นี้โดยกำหนดอาร์เรย์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เมื่อคุณอ้างอิงแถวหรือคอลัมน์ คุณจะนับแถวและคอลัมน์ที่(rows and columns)เริ่มต้นจากด้านซ้ายบนที่ 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 ของคุณ(Your VBA Array Script)
ในการวางโปรแกรมสคริปต์VBA ใน (VBA)Excelคุณต้องใช้ตัวแก้ไขVBA คุณสามารถเข้าถึงได้โดยเลือก เมนู นักพัฒนา(Developer)และเลือกดูโค้ด(View Code)ใน ส่วน การควบคุม(Controls)ของริบบิ้น
หากคุณไม่เห็นผู้พัฒนา(Developer)ในเมนู คุณจะต้องเพิ่มเข้าไป ในการดำเนินการนี้ ให้เลือกไฟล์(File)และตัวเลือก(Options)เพื่อเปิดหน้าต่างตัวเลือกของ Excel
เปลี่ยนคำสั่ง select จาก dropdown เป็นAll Commands (All Commands)เลือก ผู้ พัฒนา(Developer)จากเมนูด้านซ้ายและเลือก ปุ่ม เพิ่ม(Add)เพื่อย้ายไปยังบานหน้าต่างด้านขวา เลือกช่องทำเครื่องหมายเพื่อเปิดใช้งานและเลือกตกลง(OK)เพื่อเสร็จสิ้น
เมื่อ หน้าต่าง ตัวแก้ไขโค้ด(Code Editor)เปิดขึ้น ตรวจสอบให้แน่ใจว่าได้เลือกแผ่นงานข้อมูลของคุณในบานหน้าต่างด้านซ้าย เลือกเวิ(Worksheet) ร์กชี ตในรายการดรอปดาวน์ด้านซ้ายและเปิดใช้งาน(Activate)ทางด้านขวา สิ่งนี้จะสร้างรูทีนย่อยใหม่ชื่อ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 ใน (VBA)Excelมีประโยชน์อย่างยิ่งและใช้งานได้หลากหลายเหมือนกับเคล็ดลับอื่นๆ(any other Excel trick)ของ Excel
ตัวอย่างข้างต้นเป็นการใช้อาร์เรย์ที่ง่ายมาก คุณสามารถสร้างอาร์เรย์ที่มีขนาดใหญ่กว่ามากและทำการเรียงลำดับ หาค่าเฉลี่ย หรือฟังก์ชันอื่นๆ มากมายกับข้อมูลที่คุณจัดเก็บไว้ในอาร์เรย์ได้
หากคุณต้องการสร้างสรรค์ผลงานอย่างแท้จริง คุณสามารถสร้างอาร์เรย์สองอาร์เรย์ที่มีช่วงของเซลล์(containing a range of cells)จากแผ่นงานที่แตกต่างกันสองแผ่น และทำการคำนวณระหว่างองค์ประกอบของแต่ละอาร์เรย์
แอปพลิเคชั่นถูก จำกัด ด้วยจินตนาการของคุณเท่านั้น
Related posts
วิธีการลบบรรทัดว่างใน Excel
วิธีใช้ฟีเจอร์ Speak Cells ของ Excel
วิธีแทรกแผ่นงาน Excel ลงใน Word Doc
วิธีใช้การวิเคราะห์แบบ What-If ของ Excel
วิธีแก้ไขแถวใน Excel
วิธีสร้างมาโครหรือสคริปต์ VBA ใน Excel
คู่มือ VBA ขั้นสูงสำหรับ MS Excel
วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel
จัดกึ่งกลางข้อมูลเวิร์กชีตของคุณใน Excel สำหรับการพิมพ์
วิธีใช้ฟังก์ชัน PMT ใน Excel
ทำไมคุณควรใช้ช่วงที่มีชื่อใน Excel
วิธีเปิด Word และ Excel ในเซฟโหมด
5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel
วิธีจัดเรียงตามวันที่ใน Excel
3 วิธีในการแบ่งเซลล์ใน Excel
วิธีการเขียนสูตร IF/คำสั่งใน Excel
วิธีการสร้างแผนภูมิวงกลมใน Excel
4 วิธีในการใช้เครื่องหมายถูกใน Excel
สะกดตัวเลขอย่างรวดเร็วใน Word และ Excel
วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP