Range Name ช่วย VBA ให้ยืดหยุ่นได้อย่างไร
รหัส VBA ที่อ้างอิงถึงแต่แหน่งเซลล์ พื้นที่ตาราง ชื่อชีท หรือชื่อแฟ้ม แตกต่างจากการสร้างสูตรลิงก์ไปยังเซลล์ สูตรลิงก์ไปยังพื้นที่ตารางที่อยู่ในชีทหรือแฟ้ม ตรงที่รหัส VBA จะไม่แก้ไขตามการเปลี่ยนแปลงใดๆที่เกิดขึ้นในแฟ้ม เรียกว่า ตายตัวและคงเดิมตลอดไปก็ได้ ส่วนสูตรลิงก์จะเปลี่ยนตำแหน่งที่อ้างไว้ในสูตรให้เองโดยอัตโนมัติ พอย้ายเซลล์ต้นทางไปที่ชีทอื่นหรือแฟ้มอื่น สูตรลิงก์จะแก้ชื่อชีทหรือชื่อแฟ้มตามไปให้เอง
ดังนั้นเมื่อใดที่กดปุ่ม Alt+F11 เพื่อเปิดโปรแกรม Visual Basic for Application ขึ้นมาแล้ว ถ้าสังเกตพบว่ามีตำแหน่งเซลล์แบบ reference ที่แสดงตำแหน่งเลข row หรือชื่อ column เช่น A1 ก็ย่อมแสดงว่า หากต้องการให้รหัส VBA ทำงานได้ถูกต้องตามเดิม ห้ามย้ายเซลล์ A1 ไปที่อื่น
Range("A1").Select
ถ้าในรหัสแสดงชื่อชีทไว้ก็แสดงว่าห้ามแก้ไขชื่อชีท
Sheets("DataBase").Select
ถ้าในรหัสมีชื่อแฟ้มไว้ก็ย่อมแสดงว่าห้ามเปลี่ยนชื่อแฟ้ม
Windows("VBA_Easy.xlsb").Activate
ถ้ามีชื่อโฟลเดอร์ชื่อไดรฟไว้ด้วยย่อมต้องห้ามย้ายที่เก็บแฟ้มไปที่โฟลเดอร์อื่นเป็นอันขาด
Workbooks.Open Filename:="D:\XL Training\DataSample.xlsb"
โดยทั่วไปรหัส VBA ที่สร้างขึ้นมาให้ใช้กันจึงมีข้อแม้สำคัญว่า ต้องใช้กับงานที่มีมาตรฐาน ทุกอย่างในแฟ้มนั้นหรือวิธีการใช้งานต้องตายตัว ผู้ที่สร้างรหัสขึ้นมาต้องแจ้งเรื่องนี้ให้ผู้ใช้งานทราบ ถ้าผู้ใช้แฟ้มคิดเองเออเองแล้วแก้อะไรไป แค่สั่ง Insert Row หรือ Move ย้ายบางเซลล์อาจทำให้ VBA ที่ใช้การนับจำนวนเซลล์เป็นเงื่อนไข แม้เห็นว่ายังทำงานอยู่ แต่อาจทำงานผิดพลาดโดยไม่รู้ตัว
สมมติว่าบันทึก Macro เพื่อ Copy ตารางจากเซลล์ B2:D2 ไป Paste ที่เซลล์ G2 จะเกิดรหัสที่ตายตัว ดังนี้
Range("B2:D2").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
จากรหัสนี้ พอเห็นตำแหน่งเซลล์ B2:D2 และ G2 แสดงว่าผู้ใช้แฟ้มห้ามย้ายเซลล์ที่อ้างไว้นี้เป็นอันขาด ถ้ามีการโยกย้ายเซลล์ไปที่อื่น VBA ก็ยังทำงานที่เซลล์เดิมที่ระบุไว้
Range Name เป็นสื่อกลางระหว่าง Excel กับ VBA
เนื่องจากตัวรหัส VBA ที่เขียนขึ้นหรือเกิดจากการบันทึก Macro ก็ตาม จะยังคงเดิมไปตลอด ไม่เปลี่ยนแปลงตามการเปลี่ยนแปลงที่เกิดขึ้นในแฟ้ม Excel ดังนั้นหากต้องการทำให้ตำแหน่งเซลล์ใดๆที่อ้างไว้ในรหัส VBA ปรับตัวตาม ก็ต้องหาสื่อกลางที่จะสื่อสารระหว่าง VBA และ Excel ซึ่งทางออกนั้นก็คือ การใช้ Range Name เข้ามาช่วย
สมมติว่าตั้งชื่อ Range Name ให้กับเซลล์ B2:D2 ว่า Source พอเข้าไปตรวจสอบชื่อในเมนู Name Manager จะพบว่า Excel รับรู้ตำแหน่งอ้างอิงของ Source ในช่อง Refers to : =Sheet1!$B$2:$D$2
หลังจากนั้นเมื่อ Move ตารางที่มีชื่อว่า Source ไปที่ชีท Sheet4 ที่เซลล์ E7 ตำแหน่งอ้างอิงของ Source ในช่อง Refers to จะกลายเป็น =Sheet4!$E$7:$G$7
พอย้าย Source ไปที่ชีทชื่อ Simple ในแฟ้มชื่อ DataSample เซลล์ F11 จะพบว่าในช่อง Refers to จะกลายเป็น =[DataSample.xlsb]Simple!$F$11:$H$11 แต่จะทำให้ชื่อ Source ในช่อง Name Box หายไป เนื่องจากชื่อในช่องนี้จะแสดงได้เฉพาะ Range Name ที่มีตำแหน่งอ้างอิงในแฟ้มเดิมเท่านั้น พอย้ายเซลล์ที่ตั้งชื่อไว้ไปที่แฟ้ม DataSample ก็จะไม่พบชื่อ Source ในแฟ้ม DataSample เพราะ Source ยังคงเป็นชื่อที่อยู่ในแฟ้มเดิม เพียงแต่ตอนนี้ Refers to แฟ้มอื่นไปแล้ว
ในแง่ของมนุษย์ที่ใช้แฟ้ม Excel ไม่จำเป็นต้องเสียเวลาไปตรวจสอบว่า Source มี Refers to อยู่ที่ไหน เวลาจะใช้ชื่อก็เพียงกดปุ่ม F3 นำชื่อ Source ไปใส่ในสูตร หรือกดปุ่ม F5 แล้วพิมพ์ชื่อ Source ลงไปในช่อง Reference แล้วกดปุ่ม OK ก็จะพบว่า Excel พาไปที่เซลล์ที่ตั้งชื่อว่า Source ซึ่งอยู่ต่างแฟ้มกันให้ทันที (จะไม่ปรากฏชื่อ Source ให้เห็นในช่อง Go to: เพราะไม่ได้มีตำแหน่งอ้างอิงในแฟ้มเดิมแล้ว เราต้องพิมพ์ชื่อ Source ลงไปเอง)
เมื่อนำชื่อ Source ไปใช้ใน VBA จะพบว่า VBA ฉลาดขึ้นทันทีเพราะเมื่อมีการย้าย Source ไปที่ชีทอื่นหรือแม้แต่แฟ้มอื่นก็ตาม VBA จะยังคงติดตามหา Range Name ชื่อ Source เจออยู่เสมอ เพราะ Excel จะจัดการแจ้งกับ VBA ให้เองว่าในขณะนั้นในช่อง Refers to แจ้งว่า Source อยู่ที่ไหน
แนะนำให้อ่านเคล็ดลับอื่นๆที่ควรทราบเกี่ยวกับชื่อที่บอกตำแหน่งได้จาก
https://excelexperttraining.com/home/online/manuals/expert-tips/415-range-name-tips-and-tricks
