เคล็ดการใช้ Macro Recorder

Macro Recorder เป็นเครื่องมือช่วยสร้างรหัส VBA ให้เอง ซึ่งรหัส VBA ที่เกิดขึ้นนั้น บางครั้งเป็นรหัสที่แม้แต่คนที่เก่ง VBA มากก็ยังคิดไม่ถึง และยังเป็นเครื่องมือช่วยในการเรียนรู้ VBA แต่โดยทั่วไปมักกล่าวกันว่า เราสามารถนำ Macro Recorder มาใช้กับงานง่ายๆได้เท่านั้น หากเป็นงานยากๆยังไงๆก็ยังต้องหันมาเขียนรหัสอยู่ดี เนื้อหาในส่วนนี้จะแนะนำเคล็ดการใช้ Macro Recorder ให้เหนือกว่าที่คิดกัน

คลิก .... อันตราย

พยายามหลีกเลี่ยงการใช้ Macro Recorder บันทึกการใช้เมาส์คลิก ไม่ว่าจะคลิกเลือกเซลล์ คลิกเลือกชีท หรือแม้แต่การคลิกเลือกคำสั่งบนเมนู เพราะถ้าคลิกพลาด ตัว Macro Recorder จะบันทึกการทำงานพลาดของเราไปด้วย

ถ้าคลิกพลาดไปแล้ว Macro Recorder ก็จะสร้างรหัสซึ่งทำหน้าที่ตามที่พลาดตาม แล้วถ้าคลิกต่อไปเพื่อคลิกแก้ให้ถูก ชุดรหัสที่บันทึกจึงมีความยาวกว่าจำเป็น เพราะบันทึกตอนที่เราทำพลาด และบันทึกตอนที่แก้ไขต่อไปด้วย

ดังนั้นก่อนที่จะเริ่มบันทึก เราควรฝึกคลิกสิ่งที่ต้องการให้คล่องก่อน เมื่อบันทึกจริงจะได้ไม่พลาด
ยิ่งกว่านั้นถ้าไม่จำเป็นแล้ว อย่าใช้วิธีคลิก แต่ให้ใช้วิธีกดปุ่มบนแป้นพิมพ์แทน เพื่อลดโอกาสที่อาจจะคลิกพลาด เพราะปุ่มบนแป้นพิมพ์เห็นชัด และมีขั้นตอนเดียวตรงกับคำสั่งที่ต้องการ เช่น

•กดปุ่ม Ctrl+c, Ctrl+x, หรือ Ctrl+v เพื่อสั่ง copy, cut, หรือ paste
•กดปุ่ม F5 เพื่อเลือกไปที่ Range Name ที่ตั้งไว้
•กดปุ่ม Ctrl+PageUp เพื่อเลื่อนไปเลือกชีทก่อนหน้า
•กดปุ่ม Ctrl+PageDown เพื่อเลื่อนไปเลือกชีทถัดไป

ตัวอย่างเช่น หากต้องการ copy ข้อมูลจากตารางที่มี Range Name ชื่อ Source ไป paste ลงไปในตารางชื่อ Target ซึ่งมีพื้นที่เซลล์ขนาดเท่ากัน ให้กำหนดขั้นตอนการบันทึก Macro ดังนี้

1.กดปุ่ม F5 เลือกตารางชื่อ Source
2.กดปุ่ม Ctrl+c เพื่อสั่ง Copy
3.กดปุ่ม F5 เลือกตารางชื่อ Target
4.กดปุ่ม Ctrl+v หรือกด Enter เพื่อสั่ง Paste

จะเกิดชุดคำสั่งที่มีรหัสตรงตามแต่ละขั้น ดังนี้

Application.Goto Reference:="Source"
Selection.Copy
Application.Goto Reference:= "Target"
ActiveSheet.Paste

เมื่อปรับปรุงรหัสเองให้สั้นลง
Range("Source").Copy Range("Target")

เตรียมทุกอย่างให้ตรงข้ามกับสิ่งที่อยากคลิก

ขอให้เตรียมทำทุกอย่างซึ่งตรงข้ามกับคำสั่ง VBA ที่ต้องการไว้ก่อน เพื่อจะได้คลิกกลับมาเป็นสิ่งที่ต้องการ เช่น ถ้าอยากบันทึก Macro การคลิกเลือกเซลล์ A1 ดังนั้นก่อนที่จะบันทึก Macro ต้องคลิกเลือกเซลล์อื่นที่ไม่ใช่เซลล์ A1 แล้วตอนที่บันทึก Macro จะได้เห็นได้ชัดว่า เราคลิกเข้าไปที่เซลล์ A1 จริง และจะเกิดรหัสขึ้นตามนั้น

Range("A1").Select

ถ้าอยากได้ Macro บันทึกการเลือก Sheet3 ดังนั้นก่อนที่จะเริ่มบันทึก Macro ให้เลือกชีทอื่นไว้ก่อน

Sheets("Sheet3").Select

ถ้าอยากได้ Macro บันทึกการเลือกแฟ้มชื่อที่ต้องการ ดังนั้นก่อนที่จะเริ่มบันทึก Macro ให้เปิดแฟ้มแล้วเลือกอยู่ในแฟ้มอื่นไว้ก่อน

Windows("Book1").Activate

ถ้าอยากได้ Macro บันทึกการปรับระบบการคำนวณให้เป็น Manual ดังนั้นก่อนที่จะบันทึก Macro ให้ปรับระบบให้เป็น Automatic หรือตัวเลือกอื่นไว้ก่อน

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

เมื่อปรับปรุงรหัสเองให้สั้นลง
Application.Calculation = xlManual

คลิกเพื่อสร้างรหัสที่จำกัดตายตัว หรือไม่คลิกเพื่อสร้างรหัสยืดหยุ่น

เมื่อใดที่คลิกลงไปในชีทหรือเซลล์ เมื่อนั้น Macro Recorder จะสร้างรหัส VBA ที่ระบุชื่อชีทหรือตำแหน่งเซลล์ตายตัว เช่น หากต้องการควบคุมให้บันทึกเลข 123 ลงไปที่เซลล์ A5 ของ Sheet3 ให้เลือกเซลล์ที่อยู่ในชีทอื่นก่อน จากนั้นให้เริ่มบันทึก Macro โดยคลิกเลือก Sheet3 แล้วคลิกเซลล์ A5 พิมพ์เลข 123 ลงไปแล้วกด Enter ซึ่งจะเกิดรหัส VBA ซึ่งจะทำงาน ณ ตำแหน่งเซลล์เดิมของชีทชื่อเดิมด้วยเท่านั้น

Sheets("Sheet3").Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "123"

ตัวอย่างนี้หากใช้วิธีที่ไม่คลิก โดยให้คลิกเลือกเซลล์ A5 ไว้ก่อน แล้วเริ่มบันทึก Macro เพียงขั้นเดียว โดยพิมพ์เลข 123 ลงไปแล้วกด Enter จะเกิดรหัสซึ่งสามารถนำไปใช้ต่อได้กับทุกตำแหน่งเซลล์

ActiveCell.FormulaR1C1 = "123" 

ฝึกใช้คำสั่งลัดจากแป้นพิมพ์ เพื่อสร้างรหัสที่ยืดหยุ่น

ดังที่อธิบายไว้ในเบื้องต้นแล้วว่า วิธีคลิกเมนู คลิกเซลล์ หรือคลิกเพื่อเลือกสิ่งที่ต้องการบนหน้าจอ จะสร้างรหัสที่ตายตัว ไม่สามารถนำรหัสไปใช้ต่างที่ต่างเวลาแตกต่างจากสถานการณ์เดิม แทนที่จะคลิก ขอให้ใช้แป้นพิมพ์ในระหว่างการบันทึก Macro ซึ่งทั้งนี้วิธีใช้แป้นพิมพ์ที่ดูแล้วว่า ทำงานเหมือนกัน ไม่จำเป็นว่าจะเกิดรหัสจากการบันทึกที่ยืดหยุ่นเสมอไป

สมมติว่า ในตารางมีข้อมูลบันทึกไว้ในเซลล์ A1:D2 แล้วเราต้องการสร้างรหัส VBA เพื่อเลือกพื้นที่ที่เก็บข้อมูล จะสามารถใช้แป้นพิมพ์เลือกพื้นที่เดียวกันนี้ได้หลายแบบ และรหัสแต่ละแบบจะมีความยืดหยุ่นมากน้อยต่างกัน

ก่อนที่จะเริ่มบันทึก Macro ขอให้คลิกเลือกเซลล์ A1 ไว้ก่อนจากนั้นจึงเริ่มบันทึก Macro ตามการใช้แป้นพิมพ์ได้หลายวิธี

วิธีที่ 1 กดปุ่ม Ctrl+* เพื่อเลือก Current Region รหัส VBA ที่เกิดขึ้นคือ

 Selection.CurrentRegion.Select

วิธีที่ 2 ต้องบันทึก 2 ขั้นตอน

1.กดปุ่ม Ctrl+Shift+ลูกศรขวา พร้อมกัน จากนั้น
2.กดปุ่ม Ctrl+Shift+ลูกศรชี้ลง พร้อมกัน 

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

วิธีที่ 3 ต้องใช้แป้นพิมพ์เลือกทีละเซลล์จากขวามาซ้าย ตามด้วยจากบนมาล่าง

1.กดปุ่ม Shift พร้อมกับกดปุ่มลูกศรขวา 3 ครั้ง
2.กดปุ่ม Shift พร้อมกับกดปุ่มลูกศรลง 1 ครั้ง 

Range("A1:D2").Select

วิธีที่ 4 กดปุ่ม Ctrl+Shift+End เพื่อเลือกจนถึงเซลล์ขวาล่างสุดของตาราง

Range(Selection, _
ActiveCell.SpecialCells(xlLastCell)).Select

วิธีที่ 5 วิธีนี้ใช้หลักเลือกย้อนจากล่างขึ้นบน

1.กดปุ่ม F5 ไปเลือกเซลล์ D65536 บรรทัดล่างสุดของตาราง
2.กดปุ่ม End แล้วตามด้วยปุ่มลูกศรชี้ขึ้น เพื่อเลือกเซลล์ D2
3.กดปุ่ม Ctrl+Shift พร้อมกับปุ่ม Home เพื่อเลือกย้อนกลับมาเซลล์ A1 

Application.Goto Reference:="R65536C4"
Selection.End(xlUp).Select
Range(Selection, Cells(1)).Select

ทั้ง 5 วิธีสามารถใช้เลือกพื้นที่ตารางจากเซลล์ A1:D2 คือ เซลล์ที่มีเลข 11 จนถึงเซลล์ที่มีเลข 88 ได้เหมือนกัน แต่วิธีที่ยืดหยุ่นคือวิธีที่ 5 วิธีเดียวเท่านั้น เพราะสามารถนำมาใช้เลือกพื้นที่ จากเซลล์ที่มีเลข 11 จนถึงเซลล์ที่มีเลข 88 ได้เหมือนเดิม แม้ว่าตารางจะมีลักษณะเปลี่ยนไป ไม่ได้มีข้อมูลในเซลล์ติดต่อกันเช่นเดิม

ตัวอย่างนี้แสดงให้เห็นว่า เป็นหน้าที่ของผู้ใช้ Excel ที่จะต้องเลือกใช้วิธีใช้แป้นพิมพ์อย่างชาญฉลาดด้วย จึงจะทำให้ Macro Recorder สร้างรหัส VBA ที่ยืดหยุ่น สามารถนำมาใช้กับงานที่มีโครงสร้างข้อมูลแตกต่างไปจากเดิม โดยที่เราไม่ต้องเสียเวลากลับมาเขียนปรับปรุงแก้ไขรหัสในภายหลัง 

ใช้ Range Name แทนตำแหน่งอ้างอิงโดยตรง

รหัสที่ถูกบันทึกหรือเขียนไว้ใน VBE จะคงที่ตามเดิมไปตลอด ไม่มีการเปลี่ยนแปลงแก้ไขส่วนหนึ่งส่วนใดอีกต่อไป จนกว่าเราจะเข้าไปแก้ไขเอง ซึ่งเป็นจุดอ่อนที่สำคัญของตัว VBE ทำให้เกิดปัญหาแทบทุกครั้งที่โครงสร้างข้อมูล ชื่อชีท หรือตำแหน่งเซลล์ในตารางเปลี่ยนที่ไปจากเดิม ส่งผลให้ VBA ทำงานผิดพลาด จนกว่าเราจะเข้าไปปรับปรุงรหัสที่อ้างถึงตำแหน่งต่างๆในตารางตามจริงอีกครั้ง

ตามตัวอย่างข้างต้น หากต้องการใช้ VBA เลือกพื้นที่ตั้งแต่เซลล์ที่มีเลข 11 จนถึงเซลล์ที่มีเลข 88 ให้ใช้เมนู Formulas > Define Name ตั้งชื่อ Range A1:D2 สมมติว่าใช้ชื่อ Range Name ว่า MyData

จากนั้นให้ใช้ Macro Recorder บันทึกการกดปุ่ม F5 แล้วเลือกชื่อ Range Name ชื่อ MyData ที่แสดงขึ้น จะทำให้เกิดรหัส VBA ดังนี้

Application.Goto Reference:="MyData"

ซึ่งจะพบว่ารหัสเพียงบรรทัดเดียวนี้สามารถใช้งานได้ตลอดไป โดยไม่ต้องเสียแรงเสียเวลากลับมาแก้ไขอะไรอีก ไม่ว่าจะมีการย้ายตำแหน่งข้อมูลจากเดิม A1:D2 ไปอยู่ที่เซลล์อื่น ชีทอื่น หรือแฟ้มอื่น หรือแม้ว่าตารางที่เก็บข้อมูลเดิมนั้น ไม่ได้มีเซลล์เก็บข้อมูลต่อเนื่องกันเช่นเดิมแล้วก็ตาม

บันทึกสั้นๆ แล้วนำมาต่อกันให้ยาวขึ้น

ในการบันทึก Macro แต่ละครั้ง ขอให้บันทึกขั้นตอนทำงานสั้นๆ พยายามบันทึกขั้นตอนให้สั้นที่สุด เพื่อทำให้เกิดรหัสแต่ละงานถูกบันทึกเป็น Sub Procedure แยกออกจากกัน ช่วยทำให้เราสามารถเรียนรู้และแก้ไขรหัสได้ง่าย ต่อเมื่อผ่านการทดลองทดสอบแล้วว่า รหัสทั้งหมดสามารถทำงานได้ตามต้องการ จึงนำชื่อ Sub Procedure ที่ได้นั้นมาเขียนตามลำดับที่ใช้งาน รวมไว้ใน Sub Procedure ชุดใหม่ เพื่อช่วยทำให้เราสามารถสั่งงานเพียงครั้งเดียว แล้วรหัสทั้งหมดจะทำงานต่อเนื่องกันให้เอง

ตัวอย่างเช่น เดิมมี Sub Procedure ที่เกิดจากการบันทึก 3 ครั้ง ตั้งชื่อ Macro Name ไว้ว่า MyWork1, MyWork2, และ MyWork3

ให้เขียน Sub Procedure ใหม่เพื่อรวมคำสั่งเดิมทั้ง 3 ชุดมาไว้ที่เดียวกัน ตามแบบดังนี้

Sub RunAllMyWorks()
   MyWork1
   MyWork2
   MyWork3
End Sub