เมื่อเปรียบเทียบโปรแกรม Excel กับ VBE ในเรื่องความยืดหยุ่นแล้วจะพบว่า รหัสทั่วไปที่เขียนลงไปใน VBE จะไม่ปรับตัวเอง ให้สอดคล้องกับโครงสร้างตารางในชีทที่เปลี่ยนไป จึงถือเป็นจุดอ่อนสำคัญ และส่งผลเสียทำให้เราต้องเสียเวลาย้อนกลับมาตามแก้ไขรหัสซ้ำแล้วซ้ำอีกอยู่เสมอ
ลองพิจารณารหัสที่ได้จากการใช้ Macro Recorder บันทึกการไล่คลิกหาแฟ้ม ต่อด้วยคลิกหาชีท แล้วคลิกหาเซลล์ที่ต้องการพิมพ์เลข 123 ลงไป
Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "123"
จะพบว่า กว่าจะกำหนดตำแหน่งเซลล์ A1 ที่ต้องการ จะต้องระบุชื่อแฟ้มและชื่อชีทลงไปในตัวรหัส แล้วต่อมาถ้าเซลล์เก็บข้อมูลถูกย้ายไปเซลล์อื่นแทน A1 จะส่งผลให้รหัสชุดนี้ไม่ทำงานตามเดิม
นอกจากรหัสที่ได้จาก Recorder แล้ว รหัสที่เขียนขึ้นเอง เพื่อทำหน้าที่เช่นเดียวกับชุดรหัสข้างต้น แม้มีรหัสสั้นลงเหลือเพียงบรรทัดเดียว แต่ยังจำเป็นต้องระบุชื่อแฟ้ม ชื่อชีท และตำแหน่งเซลล์ ซึ่งเป็นเหตุให้ไม่ยืดหยุ่นเช่นกัน
Workbooks("Book1.xls").
Worksheets("Sheet1").Range("A1") = 123
ขอให้สังเกตว่า รหัสที่ได้จาก Recorder และรหัสที่เขียนเองนี้ แม้จะทำให้ได้ค่า 123 ใส่ลงไปในเซลล์ A1 ได้เช่นเดียวกันก็ตาม แต่รหัสที่เขียนเองจะช่วยส่งเลข 123 ลงไปในเซลล์ A1 ให้โดยไม่จำเป็นต้องย้ายไปเลือกเซลล์ A1 ส่งผลให้ Excel ทำงานได้เร็วขึ้นด้วย เนื่องจากไม่ต้องเสียเวลาเลือกเซลล์ซึ่งเกิดจากคำสั่ง Select
วิธีใช้ Range Name เพื่อทำให้รหัส VBA เกิดความยืดหยุ่น
Range Name เป็นชื่อที่เราตั้งขึ้นให้กับเซลล์ โดยจะเป็นเซลล์เดียว หรือหลายเซลล์ก็ได้ ซึ่งชื่อ Range Name ที่ตั้งขึ้นนี้นอกจากจะช่วยทำให้สูตรมีความหมายชัดเจนในตัว มากกว่าการใช้สูตรอ้างอิงจากเซลล์โดยตรง (เช่น เดิมใช้สูตรหากำไร =A1-B1 แต่ต่อมาใช้ Range Name แทนจะเห็นสูตร =Income-Cost) เรายังใช้ Range Name ช่วยทำให้รหัส VBA ที่มีอยู่นั้น ไม่จำเป็นต้องถูกแก้ไขเปลี่ยนแปลงอีกต่อไป
วิธีตั้งชื่อ Range Name ให้กับเซลล์ สามารถใช้วิธีง่ายๆโดยคลิกเลือกเซลล์หรือพื้นที่ตารางที่ต้องการก่อน จากนั้นให้พิมพ์ชื่อลงในช่อง Name Box ซึ่งอยู่ด้านซ้ายสุดของ Formula Bar หรือจะใช้เมนู Insert > Name > Define ตั้งชื่อแทนการใช้ Name Box ก็ได้
สมมติว่า ต้องการตั้งชื่อเซลล์ A1 ว่า Target ให้คลิกที่เซลล์ A1 ก่อนจากนั้นพิมพ์คำว่า Target ลงไปในช่อง Name Box แล้วกด Enter รับชื่อลงไป
หรือใช้วิธีตั้งชื่อผ่านเมนู Insert > Name > Define
หลักการตั้งชื่อ Range Name ที่ดี ให้ใช้ตัวอักษรภาษาอังกฤษตัวใหญ่ผสมตัวเล็ก อย่าใช้ตัวใหญ่ทั้งหมดหรือตัวเล็กทั้งหมด ถ้าอยากจะแทรกตัวเลขลงไปในชื่อก็ได้ เพียงแต่ต้องใช้ตัวอักษรเป็นตัวแรกในชื่อก่อน ส่วนชื่อที่ตั้งนั้นควรตั้งให้สื่อถึงความหมายตรงกับเรื่องที่เราต้องการ และพยายามอย่าตั้งชื่อที่อาจซ้ำกับชื่อที่ใช้เรียกภายใน Excel หรือ VBE เช่น Sheet, Cell, Range, AB123 เป็นต้น
วิธีนำชื่อ Range Name มาใช้ใน VBE
ถ้าใช้ Macro Recorder ให้บันทึกเริ่มจากคลิกเข้าไปหาแฟ้มที่ต้องการให้ได้ก่อน จากนั้นให้กดปุ่ม F5 แล้วดับเบิลคลิกชื่อที่ต้องการ จะได้รหัส VBA
Windows("Book1.xls").Activate
Application.Goto Reference:="Target"
ActiveCell.FormulaR1C1 = "123"
ถ้าเขียนรหัสเอง ให้ใช้ Target แทนลงไปในส่วนของชื่อชีทและตำแหน่งเซลล์ ดังนี้
Windows("Book1.xls").Activate
Range("Target") = 123
หมายเหตุ แม้จะใช้ Range Name ช่วยทำให้รหัส VBA ยืดหยุ่นตามการเปลี่ยนแปลงใน Excel ได้แล้วก็ตาม แต่ยังจำเป็นต้องใช้คำสั่ง Windows("Book1.xls").Activate พาไปยังแฟ้มที่มี Range Name ชื่อ Target ก่อนเสมอ หรือถ้าไม่ต้องการใส่บรรทัดนี้ เราต้องเตรียมให้แฟ้ม Book1.xls เป็นแฟ้มที่กำลังถูกเลือกใช้งานไว้ก่อน จากนั้นจึงจะใช้คำสั่งเพียง Range("Target") = 123 เพราะ VBE จะตามหา Range Name ชื่อ Target ได้จากแฟ้มที่มีชื่อ Target ตั้งไว้แล้วเท่านั้น
วิธีย่อรหัส VBA ที่ใช้ชื่อ Range Name ให้สั้นลงโดยใช้ [ ]
แทนที่จะต้องเสียเวลาพิมพ์ตำแหน่งเซลล์ที่ต้องการว่า Range("Target") เราสามารถใช้วิธี Evaluate ผ่านเครื่องหมาย [ ] ทำให้รหัสสั้นลงเหลือเพียง [Target] ดังนั้นหากเรากำลังเปิดแฟ้มที่มี Range Name ชื่อ Target อยู่แล้ว และต้องการส่งตัวเลข 123 ไปที่เซลล์ชื่อ Target จึงใช้รหัส VBA ที่เขียนสั้นๆง่ายๆ ดังนี้
[Target] = 123
การ Evaluate เป็นการสั่งให้ตัว VBE ประเมินว่า คำว่า Target คืออะไร ซึ่งเนื่องจากแฟ้มที่กำลังใช้งานอยู่มี Range Name ชื่อ Target ดังนั้น VBE จะทราบเองต่อไปว่า ให้ส่งเลข 123 ไปที่ Target ซึ่งพบแล้วว่าเป็น Range Name คือตำแหน่งเซลล์ที่ตั้งชื่อไว้ว่า Target นั่นเอง
การ Evaluate ทำให้ VBE ทำงานช้าลงไปบ้าง แต่ทำให้เราเขียนรหัสอ้างถึงตำแหน่งอ้างอิงได้ง่ายกว่าเดิมมาก