การเขียนโปรแกรมให้ Excel ทำงานแบบอัตโนมัติด้วย VBA (Visual Basic for Application) เป็นหัวข้อทักษะขั้นสูงใน Excel ที่มีคนอยากเรียนรู้เยอะมากที่สุดตลอดกาลลลลลเลยล่ะ!
ผมจึงได้รับคำเรียกร้องจากแฟนเพจจำนวนมาก ให้ทำเนื้อหาเกี่ยวกับ VBA ได้แล้ว ดังนั้นวันนี้จึงถึงเวลาที่ผมจะมาพูดถึงเรื่อง VBA ซะทีครับ ซึ่งทำทั้งที ก็เริ่มปูพื้นบานให้ใหม่ตั้งแต่แรกเลย ^^
VBA คืออะไร?
มันก็คือการเขียนโปรแกรมแบบหนึ่ง ซึ่งแทนที่เราจะนั่งเอาเม้าส์หรือคีย์บอร์ดกดคำสั่ง Excel ทีละ Step ตามปกติ เราสามารถเขียนคำสั่งใน VBA เอาไว้ เพื่อให้ Excel สามารถทำงานต่างๆ ตามที่เรากำหนดได้เลย แถมยังใส่ความฉลาดให้มันได้ด้วยนะ เช่น ถ้าสถานการณ์เป็นแบบนี้ๆๆ จะให้มันทำงานยังไงต่อ (คล้ายๆ ใส่ฟังก์ชั่น IF นั่นแหละครับ แต่เป็นการใส่ให้กับชุดคำสั่งแทน)
แนวทาง VBA ที่ผมจะสอน
การเรียนรู้ VBA ตามแนวทางที่ผมจะสอน ต้องบอกก่อนว่าจะเป็นแนวทางที่ใช้ VBA เป็นตัวเสริมการทำงานเท่านั้น นั่นคือจะใช้มันทำงานถึกๆ งานที่ไม่ต้องใช้ความคิดอะไร หรือ ใช้เมื่อไม่สามารถใช้สูตรหรือเครื่องมือปกติของ Excel ทำได้
งานอะไรที่ Excel ทำได้ เราจะปล่อยให้ Excel ทำไป (เช่น พวกสูตรคำนวณ หรือเครื่องมือ Ribbon อย่าง Pivot Table) ส่วนงานไหนที่ทำไม่ได้ เราถึงจะใช้ VBA มาช่วย!!
ดังนั้นก่อนอื่นคุณควรมีความรู้พื้นฐาน Excel ให้ดีพอสมควรก่อน แล้วค่อยมาเรียนรู้ VBA ในแบบที่ผมจะสอนให้นะครับ
และต้องขอบอกไว้ก่อนว่า ในบทความนี้เราจะยังไม่ลงลึกถึงการเขียนเชิงเทคนิคจริงๆ แต่จะเน้นให้เห็นแนวคิดและภาพรวมก่อน (ซึ่งสำคัญกว่าความรู้เชิงเทคนิคมาก!) สำหรับความรู้เชิงเทคนิคจะอยู่ในบทความถัดๆ ไปครับ
เอาล่ะ…อ่านมาถึงตรงนี้ก็เริ่มยาวแล้ว เพื่อไม่ให้เสียเวลา เรามาดูกันเลยว่า 10 Concepts ที่ผมจะบอกมีอะไรบ้าง?
Concept 1 : งาน 3 ส่วนของการเขียน VBA ใน Excel
การเขียนโปรแกรม VBA ขึ้นมาซักชิ้นหนึ่ง ผมอยากให้แบ่งงานออกเป็น 3 ส่วนดังนี้
- งานวางแผน/ออกแบบ : เป็นการวางแผนภาพใหญ่ ว่าจะให้แต่ละส่วนทำงานร่วมกันยังไง เปรียบได้กับผู้บริหารขององค์กร
- งานควบคุม : งานที่คอยควบคุมการทำงานว่า จะให้ทำงานชิ้นไหนก่อนหลัง ทำซ้ำกี่ที หรือทำตามเงื่อนไขที่กำหนดก็ได้ เปรียบได้กับเป็นหัวหน้าทีมที่พร้อมคุมพนักงานปฏิบัติการ โดยรับนโยบายแผนงานภาพรวมมากจากผู้บริหารอีกที
- งานปฏิบัติการ : เป็นงานที่ต้องลงแรง มี action บางอย่างเกิดขึ้น ซึ่งเป็นการสั่งให้ Excel ทำงานแต่ละชิ้นตามที่เราต้องการ เปรียบได้กับพนักงานระดับปฏิบัติการทั่วไป
ถ้าลองพิจารณางานแต่ละส่วน จะมีความยากง่ายต่างกัน ดังนี้
- ส่วนที่ง่ายที่สุด ก็คือ ส่วนที่ 3 : งานปฏิบัติการ
- โชคดีที่ใน Excel มีเครื่องมือสุดเจ๋งที่เรียกว่า Macro Recorder ซึ่งสามารถ “บันทึก action ที่เราทำบน Excel ให้กลายเป็นภาษา VBA ได้เลย” ดังนั้นถึงคุณจะยังเขียนภาษา VBA ไม่เป็น คุณก็ให้ Macro Recorder ทำหน้าที่นี้ไปได้ (เป็นผู้ช่วยพนักงาน) นอกจากนี้คุณยังสามารถไปหา Code ในโลกออนไลน์ที่มีคนเขียนเอาไว้ มาประยุกต์ใช้กับ Code ของคุณก็ยังได้!
- ส่วนที่จะว่ายากก็ยาก จะว่าง่ายก็ง่าย ก็คือ ส่วนที่ 1 : งานวางแผน/ออกแบบ ซึ่งเป็นสิ่งที่ไม่ค่อยเกี่ยวกับเนื้อหาของ Excel หรือ VBA เท่าไหร่
- เป็นเรื่องของตรรกะในการทำงานของ Program ว่ามันควรจะทำงานอย่างไรเพื่อที่จะแก้ปัญหาที่กำลังเผชิญหน้าอยู่ ซึ่งแต่ละคนอาจมีวิธีแก้ที่แตกต่างกันได้
- แต่สิ่งสำคัญที่อยากให้ทำคือ คุณควรวางแผนการทำงานโดยรวมของโปรแกรมก่อนว่า หลักการทำงานจะทำยังไง โดยอาจจะวาดเป็น Flow Diagram หรือจะเขียนเป็น Concept แบบภาษาพูดไว้ก็ได้ไม่ว่ากัน ขอให้วางแผนเถอะ!
- เคล็ดลับคือ หากคุณนึกวิธีแก้ปัญหาหรือเงื่อนไขในสมองคุณได้ คุณก็ให้ VBA ทำตามแบบที่คุณคิดนั่นแหละ แต่ถ้ายังคิดไม่ออก คุณก็แค่ศึกษาทางแก้ไขของคนอื่นไปซักพัก คุณจะพบทางแก้ของตัวเองในที่สุด
- ส่วนที่เราจะต้องเรียนรู้เพิ่มเติมเป็นหลักก็คือ ส่วนที่ 2 : งานควบคุม
- ส่วนนี้เป็นภาษา VBA ที่ไม่สามารถใช้ Macro Recorder มาช่วยได้ ซึ่งผมมีหน้าที่ทำให้คุณสามารถเขียน VBA ส่วนนี้ให้ได้นั่นเอง
Concept 2 : ทุกอย่างใน Excel คือ พนักงานที่พร้อมจะทำงานให้คุณ!!
ผมจะบอกว่า องค์ประกอบทุกอย่างใน Excel ล้วนยอมเป็นพนักงานให้คุณสั่งงานได้หมดเลย ไม่ว่าจะเป็นตัวแต่ละ Sheet แต่ละ Cell เครื่องมือ Filter/Pivot ก็ยอมให้คุณสั่งงานได้เช่นกัน ซึ่งภาษาอย่างเป็นทางการของ “พนักงาน” ที่ผมพูดถึง เรียกว่า “Object” นั่นเองครับ
ถ้าเรามี Object หลายตัว (คนทำงานหลายคน) การที่เราจะสั่งงานได้นั้น เราจะต้องมีวิธีเรียกถึงสิ่งนั้นจริงมั้ยครับ? เช่น ถ้าคุณมีพนักงานอยู่ 3 คนที่พร้อมจะทำตามคำสั่งคุณได้ คุณก็ต้องมีวิธีเรียกแต่ละคนว่าจะเรียกเค้าว่าอะไร? เช่น พนักงาน A นัดหมายการประชุม, พนักงาน B เตรียมขนมเบรค, พนักงาน C จดบันทึกการประชุม เป็นต้น
แปลว่า เราจะต้องมีวิธีอ้างอิงเพื่อเรียกใช้พนักงานแต่ละคนนั่นเอง… (เรียกชื่อได้ก็ควบคุมได้)
เรื่องของเรื่องคือ เราจะอ้างอิงไปหาคนงานแต่ละคนใน Excel ยังไงดีล่ะ?
ข่าวดีคือ เรื่องนี้คุณสามารถใช้ Macro Recorder ลองบันทึกการสั่งการของเราที่ทำกับ Object นั้นๆ ใน Excel แล้วค่อยมาดู Code VBA ภายหลังได้ว่าจะต้องเรียก Object นั้นว่าอะไร แค่นี้ก็ไม่เป็นปัญหาแล้วครับ ดังนั้นเรื่องนี้จึงไม่ใช่สิ่งที่ต้องกังวลมากนัก
ซึ่งเมื่อคุณลองดู Code ที่โผล่มาจะพบว่า แต่ละ Object นั้นมีความเป็นแม่ลูกกันอยู่พอสมควร และจะอยู่ในรูปแบบของ Objectแม่.Objectลูก เช่น ถ้า Object แม่เป็นคน เราก็สามารถอ้างอิงไปที่แขนของนาย A ได้ว่า นายA.แขน หรือจะอ้างอิงไปที่แขนของนาย B ได้ว่า นายB.ขา เป็นต้น
Concept 3 : เราสั่งอะไรกับ Object ได้บ้าง?
เมื่อรู้แล้วว่าเราจะเรียก Object แต่ละอันว่าอะไร… คำถามถัดไปก็คือ แล้วเราจะสั่งงานอะไรมันได้บ้าง?
การสั่งงาน Object นั้นมีอยู่ 2 ลักษณะด้วยกัน คือ
- สั่งให้มันทำงานบางอย่าง (มีการกระทำ เช่น ลบ sheet, เพิ่มแถว, sort ข้อมูล)
ซึ่ง action ที่ Object ทำได้ มีชื่ออย่างเป็นทางการว่า Method ครับ (คล้ายๆ กับ Verb ในภาษาอังกฤษ) - สั่งให้เปลี่ยนลักษณะ/คุณสมบัติ (ทำให้คุณสมบัติเปลี่ยนไป เช่น เปลี่ยนค่า เปลี่ยนสี)
ซึ่งคุณสมบัติของ Object นั้นมีชื่ออย่างเป็นทางการว่า Property ครับ (คล้ายๆ กับ Adjective ในภาษาอังกฤษ)
ซึ่งแต่ละ Object จะมี Property และ Method เป็นของตัวเอง เช่น เครื่องมือ Filter มันทำการ Filter ได้ แต่ไม่สามารถสั่ง Filter ให้ Pivot สรุปข้อมูลได้ (เพราะไม่ใช่หน้าที่ของมัน) เป็นต้น
แล้วเราจะรู้ได้ยังไงว่า Object แต่ละตัวมี Method หรือ Property อะไรบ้าง?
วิธีที่ง่ายที่สุดก็คือใช้ Macro Recorder (อีกแล้ว!) บันทึกสิ่งที่เราทำใน Excel แล้วมาดู Code เช่นเดิมครับ แต่วิธีสังเกตคือ ชื่อของ Method และ Property จะตามอยู่หลังจากชื่อ Object ครับ ซึ่งจะอยู่ในรูปแบบ Object.Method หรือ Object.Property ครับ
ยกตัวอย่างเช่น คุณสามารถกดบันทึก Macro แล้วลองสั่ง Filter ให้ทำการกรองข้อมูล จากนั้นค่อยไปดู Code VBA ที่ถูกสร้างขึ้นมาได้ซึ่งคุณจะเห็น Property และ Method ที่ถูกสร้างขึ้นมาอย่างแน่นอน ดังนั้นเรื่องนี้จึงไม่ใช่สิ่งที่ต้องกังวลอีกเช่นกัน
Concept 4 : ตัวแปร และ Operator
ตัวแปร
การเขียนโปรแกรมนั้นมี Concept หนึ่งที่สำคัญนั่นก็คือ ตัวแปร
คำว่าตัวแปร ตัวที่ค่าสามารถ “แปรเปลี่ยน” ไปได้เรื่อยๆ… พูดเหมือนขวานผ่าซาก แต่ก็เป็นแบบนั้นจริงๆ…
หน้าที่ของมัน… ถ้าจะให้เข้าใจง่ายๆ ก็เป็นเหมือน กล่องที่เอาไว้เก็บค่าต่างๆ ซึ่งเราสามารถอ้างอิงค่าจากกล่องตัวแปรนี้ได้ ตรงนี้จะคล้ายๆกับการใช้ Cell Reference ใน Excel ที่เราสามารถเก็บค่าที่คำนวณแล้วเอาไว้ใน Cell เช่น A1 แล้วสามารถเอาค่าไปใช้ต่อได้โดยการใช้ Cell Reference ได้
แต่ VBA เจ๋งกว่าตรงทีเราเปลี่ยนค่าของตัวแปรไปเรื่อยๆ ได้ด้วย! ทำยังไงมาดูกัน
วิธีการใส่ค่าใหม่ลงไปในตัวแปร
ให้ใช้เครื่องหมายเท่ากับ ในรูปแบบ ตัวแปร=ค่าที่จะใส่ลงไปในตัวแปร เช่น
x=30
ถ้าต่อมาเราแสดงค่า x+5 ก็จะได้ค่า 35 ออกมานั่นเอง
การสั่งงาน VBA จะเอาค่าฝั่งขวา มาใส่ในตัวแปรฝั่งซ้าย ดูแล้วเหมือนไม่มีอะไร แต่ถ้าเจอแบบนี้อาจมึนได้ เช่น
x=x+1
เฮ้ย ท่าจะเพี้ยนแล้ว x จะเท่ากับ x +1 ได้ไงกัน!?
ใจเย็นครับ วิธีตีความที่ถูกต้องของคำสั่งบรรทัดนี้ก็คือ ให้เพิ่มค่า x จากเดิมไป 1 หน่วยต่างหากล่ะ!
ก่อนจะจบในเรื่องของตัวแปร ผมต้องบอกก่อนว่า ตัวแปรใน VBA นั้นมีด้วยกันหลายประเภท (คล้ายๆ ใน Excel แหละครับ) เช่น เป็นตัวหนังสือ ตัวเลข ตรรกะ วันที่ แต่ละประเภทก็จะรองรับข้อมูลได้ต่างกันทำให้กินหน่วยความจำต่างกัน แต่ในเบื้องต้นนี้คุณยังไม่ต้องซีเรียสเรื่องนี้มากก็ได้ครับ
Operator
นอกจากตัวแปรแล้ว ใน VBA ก็จะมีพวกตัวดำเนินการ (Operator) คล้ายๆ Excel เลย เช่น +,-,*,/, =,>,<,& …และอีกมายมาย ซึ่งเราสามารถนำมาใช้เขียนคำสั่งต่างๆ ได้ อีกเยอะแยะครับ เช่น เครื่องหมายทางคณิตศาสตร์เอาไว้คำนวณ, เครื่องหมายเปรียบเทียบ ทำให้เกิดค่า TRUE/FALSE ได้, เครื่องหมาย & เอาไว้เชื่อมข้อความ เป็นต้น
Concept 5 : คำสั่งภาษา VBA จะทำงานจากบนลงล่างทีละบรรทัด
VBA มันจะทำจากบรรทัดบนลงล่างครับ (สั่งก่อนทำก่อนว่างั้นเถอะ)
ดังนั้น ถ้าเขียนว่า
พนักงาน A นัดหมายการประชุม
พนักงาน B เตรียมขนมเบรค
พนักงาน C จดบันทึกการประชุม
แปลว่า พนักงาน A นัดหมายการประชุม ก่อน จากนั้น พนักงาน B เตรียมขนมเบรค แล้วจบด้วย พนักงาน C จดบันทึกการประชุม เรียงตามลำดับแบบนี้ ทีละบรรทัด ทีละคำสั่ง ไม่มีอะไรยุ่งยากครับ
ไม่อยากสั่งทีละคำสั่ง อยากสั่งเป็นชุดเลยได้มะ?
ในหลายๆ สถานการณ์ ถ้าผมขี้เกียจสั่งคำสั่ง 3 อันแยกกันแบบนั้นทุกครั้ง ผมก็สามารถรวบให้มันอยู่ภายใต้ชุดคำสั่งเดียวกันได้เลยครับ ซึ่งใน VBA เราจะเรียกชุดคำสั่งว่า Sub (ซึ่งจะ Run ชุดคำสั่งย่อยที่อยู่ภายในนั้นจากบนลงล่าง)
เช่น ถ้าผมรวม 3 อันนั้นเข้าไปใน Sub ที่ชื่อว่า จัดงานประชุม ดังนี้
Sub จัดงานประชุม()
พนักงาน A นัดหมายการประชุม
พนักงาน B เตรียมขนมเบรค
พนักงาน C จดบันทึกการประชุม
End sub
แล้วครั้งต่อไปเมื่อผมสั่งคำสั่งว่า Call จัดงานประชุม() คราวนี้ VBA ก็จะทำ 3 คำสั่งนั้นตามลำดับเลย! ไม่ต้องมาสั่งแยกอีกต่อไป
แล้วถ้ามีอยู่ Action เดียวยังต้องมี Sub อีกมั้ย?
ใน VBA เราไม่สามารถใส่คำสั่งโดยที่ไม่มีที่อยู่ได้ แปลว่าถึงมีอยู่คำสั่งเดียว เราก็ต้องเอาไว้ใน Sub อยู่ดี
แต่ในความจริงแล้ว ยังมีชุดคำสั่งอีกแบบนอกจาก Sub นั่นก็คือ Function ซึ่งจะพูดถึงใน Concept ถัดไป
Concept 5 : เราสร้างฟังก์ชั่นใช้เองใน Excel ได้ด้วย VBA
อย่างที่ผมบอกว่านอกจาก Sub แล้ว ใน VBA เรายังมีชุดคำสั่งอีกประเภทนั่นก็คือ Function นั่นเอง แล้วมันต่างจาก Sub ยังไงล่ะ?
คำตอบคือ Function ต่างจาก sub ตรงที่สุดท้ายแล้วจะส่งค่าผลลัพธ์บางอย่างกลับออกมายังตัวแปร (ที่ชื่อเดียวกับฟังก์ชั่น) ด้วย
พูดง่ายๆ Sub คือชุดคำสั่งที่ทำ Action ต่างๆ แล้วจบไป…
แต่ Function ทำอะไรบางอย่าง (เช่นคำนวณค่า) แล้วส่งผลลัพธ์กลับมาไว้ในตัวแปร…
ถ้าฟังแล้วยังดูงงๆ อีก ผมอยากให้นึกถึงฟังก์ชั่นที่เราใช้ในสูตร Excel ตามปกติครับ มันคือสิ่งเดียวกันเลย!
เช่น ฟังก์ชั่น LEFT จะเห็นว่ามี Argument อยู่ 2 ตัวด้วยกัน ซึ่งพอใส่ Input เข้าไปแล้ว ฟังก์ชั่น LEFT ก็จะทำงานอะไรซักอย่าง สุดท้ายก็จะได้ผลลัพธ์คือ อักขระจำนวน num_char จากทางซ้ายของข้อความ text ออกมาใน Cell จนได้
ซึ่งอันนี้แหละที่คล้ายกับการใช้ชุดคำสั่ง Function ใน VBA ซึ่งจะได้ผลลัพธ์เก็บไว้ในตัวแปร
เช่น ถ้าผมอยากสร้างฟังก์ชั่นไว้หา % ที่เปลี่ยนแปลงไป โดยจะให้ชื่อว่า PERDIF ผมก็ทำได้ดังนี้
Function PERDIF(ค่าก่อน,ค่าหลัง)
ค่าที่เปลี่ยน = ค่าหลัง-ค่าก่อน
PERDIF = ค่าที่เปลี่ยน/ค่าก่อน
End Function
เพียงแค่นี้ คุณก็สามารถใส่สูตร =PERDIF(100,150) ใน Cell แล้ว Excel ก็จะคำนวณออกมาจนได้เป็น 0.5 หรือ 50% ได้ทันที!
เห็นมั้ยว่าการสร้างฟังก์ชั่นใช้เองไม่ยากเลย นี่แหละคือพลังของ VBA ล่ะ นี่ถ้าเอาไปใช้กับฟังก์ชั่นซับซ้อนๆ จะเจ๋งขนาดไหน!!
Concept 6 : ชุดคำสั่ง VBA จะเริ่มทำงานเมื่อมีเหตุการณ์ (Event) บางอย่าง
ชุดคำสั่งทั้ง Sub และ Function อยู่ดีๆ มันจะเริ่มทำงานเองไม่ได้ครับ มันจะต้องมีกลไกบางอย่างไปสั่งการให้ VBA Run ชุดคำสั่งดังกล่าว ซึ่งเราจะเรียกกลไกหรือเหตุการณ์ที่มาสั่งการนี้ว่า Event ครับ
ตัวอย่างของ Event เช่น การคลิ๊กปุ่มบนหน้าจอ, การกดปุ่มบน Keyboard, การเปิดไฟล์ขึ้นมา, การแก้ไขข้อมูล, การเรียกใช้ Sub , การเรียกใช้ Function เป็นต้น
แปลว่าเราสามารถกำหนดได้ว่า เมื่อเกิดเหตุการณ์ xx จะใช้สั่งคำสั่ง yy เป็นต้น ซึ่งสิ่งเหล่านี้มีประโยชน์มากในการทำงานอัตโนมัติ เมื่อผู้ใช้งานทำงานต่างๆ ใน Excel ก็ล้วนสามารถสั่งให้ VBA ทำงานได้ทั้งสิ้น (ที่ฮิตมากคือการกดปุ่ม, การเปิดไฟล์เข้ามา)
เช่น
Private Sub Workbook_Open()
คำสั่ง A (ที่จะให้ทำ เมื่อเปิดไฟล์ขึ้นมา)
End Sub
เมื่อเราใส่คำสั่งลงไปใน Event Workbook_Open() แปลว่า
เมื่อผู้ใช้งานเปิดไฟล์นี้ขึ้นมา มันจะรันคำสั่ง A โดยอัตโนมัติเลย เจ๋งป่ะล่ะ!!
เรียกใช้ Sub หรือ Function ซ้อนกันก็ได้นะ
สิ่งที่ผมอยากจะบอกเพิ่มเติมก็คือ เราสามารถเรียกใช้ Sub หนึ่งภายใต้อีก Sub/Function ได้ เช่น เรียก SubA ถายใต้ Sub B ได้ และในทำนองเดียวกัน เราก็สามารถเรียกใช้ Function หนึ่งภายใต้อีก Sub/Function ได้เช่นกัน!!
จะเห็นได้ว่า ถ้ามันซ้อนกันได้ขนาดนี้ VBA ก็จะทรงพลังมากขึ้นอีก เพราะเราใช้หลักการของ Divide & Conquer หรือ แบ่งหน้าที่กันทำทีละส่วนได้ แล้วค่อยสั่งคำสั่งให้มันมาทำงานร่วมกันอีกที!
Concept 7 : ถ้าอยากให้ VBA ทำงานตามเงื่อนไขทำไง?
อย่างที่ผมบอกใน Concept 5 ไปว่า VBA จะทำงานทีละบรรทัด.. แล้วถ้าเราไม่อยากให้มันทำงานทีละบรรทัด ต้องทำไงล่ะ?
เราก็ต้องใส่คำสั่งที่ทำงานประเภท งานควบคุม ได้ ซึ่งการควบคุมมีประเภทหลักๆ ดังนี้
- ประเภททำงานตามเงื่อนไข เช่น
- IF…Then…Else สามารถสั่งได้ว่า ถ้าเงื่อนไขเป็นจริง ให้ใช้ชุดคำสั่งอะไร เป็นเท็จจะใช้ชุดคำสั่งอะไร (มีทางแยก 2 ทาง) คล้ายๆ กับฟังก์ชั่น IF ของ Excel เลยครับ
- Select Case… :ทำได้คล้ายๆ IF แต่จะแยกได้หลายๆ ทางเลย กำหนดได้ว่า ถ้าตัวแปรที่สนใจมีค่าเป็น A, B, C แต่ละอันจะให้ทำอะไรต่อ ตัวนี้คล้ายๆ กับฟังก์ชั่น CHOOSE ของ Excel เลยครับ แต่ดีกว่าตรงที่ไม่ได้จำกัดว่าตัวแปรต้องเป็นตัวเลขเหมือนกับ CHOOSE
- On Error : เอาไว้จัดการเวลาเจอค่า Error ต่างๆ
- ประเภทให้ทำงานซ้ำ เช่น
- For … Next : สามารถกำหนดให้ทำงานซ้ำ (Loop) เป็นจำนวนรอบที่ต้องการได้ ซึ่งจะใช้ตัวแปร (เช่น i) มาช่วยนับจำนวนรอบ เช่น อยากทำงาน 100 รอบ เมื่อเริ่มรอบแรกตัวแปรมีค่าเป็น 1 พอทำจบ โปรแกรมจะเพิ่มค่าไปอีก 1 ซึ่งทำจนตัวแปร i มีค่าเป็น 100 ก็จะเลิกทำนั่นเอง
- For Each… : ใช้กับ Object ที่มีลูกๆ หลายตัว สามารถทำงานซ้ำจนกว่าจะวนครบลูกๆ ทุกคน
- Do While…..Loop : สามารถกำหนดให้ทำซ้ำขณะที่เงื่อนไขยังเป็นจริง (ไม่รู้จำนวนรอบที่แน่นอน)
- Do Until…..Loop : สามารถกำหนดให้ทำจนกว่าเงื่อนไขจะเป็นจริง (ไม่รู้จำนวนรอบที่แน่นอน)
เมื่อรู้คำสั่งที่จะใช้ควบคุม Code VBA ได้แล้ว เราก็สามารถที่จะใช้เทคนิค Macro Recorder เพื่อสร้าง Code ที่เป็นส่วนงานปฏิบัติการ จากนั้นค่อยเอา Code ที่เอาไว้ควบคุมงานไปครอบ เพียงเท่านี้เราก็สามารถสร้างโปรแกรมที่ทรงพลังได้!
Concept 8 : VBA เรียกใช้ฟังก์ชั่นของ Excel ได้ แถมควบคุมการทำงานนอก Excel ก็ยังได้!!
จริงๆ แล้วใน VBA ก็มีฟังก์ชั่นพื้นฐานหลายๆ อย่างให้ใช้โดยไม่เกี่ยวกับฟังก์ชั่นใน Excel เลย! บางตัวก็คล้ายกับฟังก์ชั่นใน Excel บางตัวก็ไม่เหมือนเลย เช่น LEN (นับจำนวนอักขระ), Cstr (แปลงให้เป็น Text), Datepart ( ดึงเอาส่วนของวันที่ออกมา เช่น วัน เดือน ปี), Instr (หาว่าอักขระที่ต้องการอยู่ตำแหน่งที่เท่าไหร่ของคำ) เป็นต้น
แม้จะมีฟังก์ชั่นให้เลือกเยอะแยะ แต่มันก็ไม่เยอะเท่าใน Excel แน่นอน! ข่าวดีคือ ใน VBA เราสามารถเรียกใช้ฟังก์ชั่นของ Excel ได้ด้วย โดยการใช้คำสั่งที่ชื่อว่า WorksheetFunction ในรูปแบบ Application.WorksheetFunction.ชื่อฟังก์ชั่นในExcel(argument) เพียงเท่านี้คุณก็สามารถดึงพลังของ Excel มาช่วยใน VBA ได้อีกเยอะแยะ
และนอกจากจะเอาพลังของฟังก์ชั่นใน Excel มาใช้ใน VBA ได้แล้ว เรายังสามารถใช้ VBA ควบคุมโปรแกรมอื่นๆ ได้ด้วย เช่น คุณสามารถสั่งให้ VBA ลบไฟล์ในเครื่องทิ้งได้เลยแบบไม่รู้เนื้อรู้ตัว (น่ากลัวมาก! ดังนั้นถ้าใครไม่แน่ใจ อย่าไปเปิด Run File Excel ที่มี VBA ของคนที่ไม่รู้จักนะครับ) ซึ่ง Concept ในการไปควบคุมคนอื่นได้นั้น จะเป็นการเชื่อมต่อด้วยเครื่องมือที่ชื่อว่า Reference Library ซึ่งมีเครื่องมือให้เลือกอีกมากมายเลย
Concept 9 : ติดปัญหาไม่ต้องกลัว ใช้เครื่องมือ Debug สิ
การทำงานใน VBA นั้น แทบไม่มีทางที่คุณจะเขียน Code ทีเดียวแล้วใช้ได้เลยแบบไม่ผิดพลาด สิ่งสำคัญคือ ถ้าเกิด Error ขึ้นแล้วเราจะต้องหาทางแก้ไข
credit รูป : http://university.utest.com/writing-quality-bug-reports-and-utest-etiquette/
ซึ่ง VBA ก็มีเครื่องมือช่วยเหลือดีๆ มาช่วยมากมาย โดยเฉพาะเครื่องมือในหมวด Debug ( Bug เป็นศัพท์คอมพิวเตอร์ หมายถึงความผิดพลาดของโปรแกรม ดั้งนั้น Debug จึงหมายถึงการกำจัดข้อผิดพลาดนั่นเอง)
เครื่องมือ Debug จะช่วยหยุดการทำงานของ Code ในตำแหน่งที่เกิดความผิดพลาดให้ จากนั้นเราก็สามารถสั่งให้โปรแกรมค่อยๆ ทำงานทีละ Step แล้วค่อยตรวจดูผลลัพธ์ทีละขั้นได้ว่าผลลัพธ์เป็นไปตามที่คิดหรือไม่?
ซึ่งเมื่อแก้ไขข้อผิดพลาดได้แล้ว ก็สามารถลอง Run โปรแกรมใหม่ เพื่อตรวจดูว่าข้อผิดพลาดหายไปรึยัง? (หรือมีข้อผิดพลาดใหม่เพิ่มมาอีก!)
ปัญหาที่อาจจะพบได้บ่อยๆ และควรระวังไว้
- รูปแบบของ Error ในการเขียนโปรแกรม : มี 3 รูปแบบใหญ่ๆ คือ
- Error แบบจะๆ เขียนรูปแบบผิด ทำให้ Run ไม่ได้ : แบบนี้มันจะเตือนเราจะๆ เลย โดนเตือนก็แก้ไป เปิดหาวิธีการเขียนที่ถูกต้องตามเว็บ ตามหนังสือ หรือใน Help ก็ได้ ไม่ต้องคิดมากครับ
- Run ได้แล้ว แต่ไปหยุดชะงักที่บางที่ : แบบนี้เจอที่ผิดแล้วค่อยแก้ไขไป ด้วยการ Debug จะช่วยได้ครับ
- Run ได้ฉลุย แต่ผลลัพธ์ผิดโดยสิ้นเชิง : แบบนี้เรียกว่าผิดที่แนวคิดและตรรกะตั้งแต่แรก ต้องทบทวนความเข้าใจกันใหม่เลยทีเดียว
- VBA เมื่อ Run Code ไปแล้วจะ Undo ไม่ได้! : ข้อนี้เป็นข้อจำกัดของ VBA เลย ที่ว่าเมื่อทำงานตามคำสั่งไปแล้ว หากเราไม่พอใจ เราจะไม่สามารถ Undo ได้เลย หากเผลอสั่งลบไป ก็จะหายไปเลยตลอดกาล!
- ทางแก้ไข : หมุ่น Save Excel ไว้ก่อนจะ Run Code VBA เสมอ (Save ไหว้หลาย Version ได้ก็ดี 555)
- มีการย้ายที่ Cell แล้ว Error : มีการเขียน VBA อ้างอิงกับ Cell อันนึงไว้โดยใช้ Cell Reference แต่ภายหลัง Cell นั้นถูกย้ายที่ใน Sheet Excel (ไม่ว่าจะ Cut/Insert/Delete) ผลคือ VBA ยังอ้างอิงไปยังที่เก่าอยู่ทำให้เกิด Error ได้
- สาเหตุ : การเขียน Code ใน VBA มันไม่ฉลาดเหมือนสูตรใน Formula Bar ที่ว่าหากเราย้ายที่ Cell อ้างอิงแล้วสูตรจะย้ายตามให้โดยอัตโนมัติ เพราะ VBA จะยึดตามสิ่งที่เราเขียนไว้ ไม่มีการเปลี่ยนแปลง Code ให้เด็ดขาด
- ทางแก้ไข : ให้ใช้การอ้างอิงไปที่ Defined name แทนการอ้างไปที่ Cell Reference เพราะ Defined Name มันเป็นส่วนหนึ่งของ Excel เองที่มีความฉลาดในการย้ายที่ Cell อยู่แล้ว
Concept 10 : ความสามารถของ VBA ขึ้นกับจินตนาการของคุณ!
จริงๆ Concept หลักๆ ของ VBA ก็ไม่พ้น 9 ข้อข้างบนอย่างที่ผมบอกไปแหละครับ มีแค่นี้จริงๆ แต่การที่เราจะสร้างโปรแกรม VBA เจ๋งๆ ขึ้นมาได้นั้น มันขึ้นอยู่กับความคิดของเรามากกว่า ว่าจะแก้ปัญหาที่พบเจอด้วยวิธีไหนดี?
บางทีเราแก้ปัญหาหนึ่งๆ ได้เช่นกัน แต่ใช้เวลารันนานมาก หรือไม่คงทนต่อการเปลี่ยนแปลง (เช่น หากมีคนแทรกแถวปุ๊ป Code เราพังเลย) แบบนี้เมื่อเรามีประสบการณ์มากขึ้น เราก็จะหาวิธีเขียนที่ดีขึ้น รันเร็วขึ้น ยืดหยุ่นมากขึ้นได้เรื่อยๆ เรียกได้ว่า เรียนรู้กันทั้งชีวิตก็คงไม่หมด เพราะถึงจะดีแล้ว..ก็ย่อมมีสิ่งที่ดีกว่าเสมอ แต่ผมว่ามันสนุกดีออก! ที่เราจะสามารถพัฒนาฝีมือ พัฒนางานของเราไปได้เรื่อยๆ
ดังนั้นอย่าหยุดพัฒนาครับ ใครยังไม่ได้มีโอกาสลองใช้ VBA ก็เริ่มลองได้เลยครับ ไม่มีอะไรยากเกินความพยายาม!
Step ต่อไป?
ทั้งหมดในบทความนี้เป็นเพียง Concept ปูพื้นฐานทางความคิดก่อนครับ ตอนถัดๆ ไปจะมีตัวอย่างให้เห็นถาพชัดขึ้นนะ (โดยใช้ concepts ที่บอกนี่แหละ) อย่าลืมติดตามกันต่อนะครับ ^^
