บทที่ 5 การเขียนโปรแกรมใน Excel

บทท่ี 5 การเขยี นโปรแกรมใน Excel การเขียนโปรแกรมใน Microsoft Excel เปนการนําเอา Utilities ของโปรแกรมขึ้นมาใชงาน ซ่ึงเราเรียก คุณสมบัตินี้วา VBA หรือ Visual Basic for Applications ที่ใชในการเขียนโปรแกรมประยุกตของ Windows และ รวมอยูในโปรแกรมประยกุ ตของ Microsoft หลายโปรแกรม การเอา VBA มาชวยในการทาํ งาน จะทาํ ใหง านเสรจ็ เรว็ ข้ึน การเขียนโปรแกรมดวย VBA ตองกระทําผานหนาตางวิชวลเอดิเตอร (Visual Basic Editor: VBE) จึง จําเปน อยางยิง่ ท่ีตองทําความรจู กั กบั หนาตางวชิ วลเอดเิ ตอรเ สยี กอน 5.1 การเรียนใชงานหนาตางวชิ วลเบสิกเอดเิ ตอรเพื่อพฒั นาโปรแกรม วธิ ที ี่ 1 ไปทเ่ี มนูบาร เครือ่ งมือแมโคร Visual Basic Editor หรอื Alt+F11 วธิ ที ่ี 2 ไปที่เมนบู าร นกั พฒั นา (Developer) วิธที ี่ 3 ไปท่ี Tab Sheet ดานลาง คลิกขวาคลกิ แสดงรหสั 82

5.2 This is your first VBA programmer เร่ิมตน กับโปรแกรมแรกกนั เลย โดยมมี แี นวคดิ วาจะทําการสราง Message box pops up พรอมกบั คําวา “Hello World!\" เปน การทักทาย กนั กอ น 1. ไปทเี่ มนูบาร เครอื่ งมอื แมโคร Visual Basic Editor หรือ กด Alt+F11 เขาสูหนาตาง VBA 2. คลกิ Insert  Module อธบิ าย การพฒั นาตอ งแทรกแผน โมดลู (Module) กอ น แลว จึงเขยี น Code 3. In the Module window, type the following: Sub showMessage() MsgBox \"Hello World!\" End Sub 83

4. คลิก Run  Run Sub/UserForm หรอื กด F5 84

5. The message box pops up with the \"Hello World!\" greeting. 6. บันทึก ช่ือ message.xls ไดรฟ D:\\ 7. การเรียกใชง าน การเรียกใชง าน อาจถกู ปอ งกนั โดย หนวยรักษาความปลอดภัยของ Microsoft ใหป ฏบิ ัติดงั น้ี 1. ไปทเี่ มนบู าร เครื่องมือ แมโคร ความปลอดภัย 2. ปรากฏหนาตา ง ความปลอดภยั ดงั รูป ท่ี Tab ระดบั ความปลอดภัย คลกิ ตวั เลือก ปานกลาง ดงั รปู (ระดบั ปานกลางการปองกนั จะมคี าํ เตือนทุกครง้ั ที่มกี ารใชง าน) 85

5.3 หลกั การเขยี นโปรแกรม Visual Basic for Application (VBA) การเขยี นโปรแกรม VBA คลายกับการเขียนโปรแกรม Visual Basic กค็ อื 1. การใชคอนโทรล (Controls) ในการออกแบบ อินเทอรเฟส (Interface) 2. การกาํ หนดคุณสมบัติ (Properties) ใหก บั วตั ถุ (Object) 3. การเขยี นโปรแกรมผาน VBA  1. Controls ใชออกแบบ Interface ช่ือคอนโทรล หนาท่กี ารทํางาน Select Object สลบั ระหวา ง เมาสปรกติ กบั คลิกเลือกวัตถุ แสดงขอความ คาํ อธิบายบนฟอรม Label TextBox ชองกรอกขอ มลู เพื่อ รบั ขอมลู จาก user ComboBox Dropdown List รายการใหเ ลอื ก ListBox แสดงรายการ เพือ่ คลิกเลอื ก CheckBox ปุม ตัวเลอื ก สรางกี่ตวั ก็ได OptionBox ปุมตัวเลือก เลือกไดเ พียง 1 ตวั Frame กรอบจดั กลุม คอนโทรล/ปุม CommanBotton ปุมคําสง่ั เขียน Code สั่งโปรแกรมทํางาน Scrollbar แถบเลอ่ื น เพ่อื ดูขอ มูลถัดไป PictureBox ปมุ ดึงรูปภาพมาแสดงบนฟอรม  2. การกําหนดคุณสมบตั ิ (Properties) ใหกบั วัตถุ (Object) คอนโทรลทุกตวั รวมถึงฟอรม เปนวตั ถุ ซึ่งมคี ณุ สมบตั ใิ นตวั เอง เชน Name, Caption, Font, Methods และ Events “แลวมคี ุณสมบตั ไิ ปเพ่อื อะไรละ คะ ” น่ีคือคําถามของ ผูเรม่ิ ตนเขียนโปรแกรม ขอยกตัวอยางเลยนะคะ ตวั อยาง 1 ถา ตองการสราง UserForm ทีอ่ อกแบบใหมีรปู รา งหนา ตา ดงั รูป จะตองกาํ หนดคุณสมบตั อิ ะไรบา ง ขัน้ ตอนการกาํ หนดคณุ สมบตั ิ 1. สรา งฟอรม ข้ึนมากอ น ท่ีหนา ตา ง VBE Insert UserForm 2. ปรากฏฟอรมข้ึนมา มีชอื่ วา UserForm1 3. คลิกท่ี ฟอรม ใหเ กิดเปน กรอบส่เี หลย่ี มตรงมุม ลอมรอบ ฟอรม 4. ดานซา ยมือ จะพบกับ Properties ของฟอรม สังเกตจาก ชอง(Name) | UserForm1 เปนการระบวุ า นี่ คอื Properties ของฟอรม UserForm1 กรณีทมี่ ีการสรางฟอรมหรือคอนโทรลมากกวา 1 ตอ งสงั เกต จาก ชอง (Name) เปน สําคัญ วา ตรงกับ วตั ถุ ท่ีเราตองการกาํ หนดคณุ สมบัตหิ รอื ไม 86

 3. การเขียนโปรแกรมผา น หนา ตาง VBE (Visual Basic Editor) การเปดหนาตาง VBE เพื่อเขียน Code คําสง่ั ใหกับ วตั ถุ (Object) 1. Double Click ที่ วัตถุตวั นั้น เชน UserForm1,ปุม (Command) 2. ปรากฏ หนา ตาง VBE เพอื่ เขียน Code ใหก บั วตั ถทุ ี่ไดท ําการ Double Click 3. ในหนาตา งจะพบกบั ประโยค 2 บรรทัด Private Sub ช่ือวตั ถุทตี่ อ งเขียน Code_เหตกุ ารณ () เขียน Code End Sub ตัวอยา ง 2 เมอื่ มีการ Double Click ที่ ปุม CommandButton1 บน Form 87

สวนประกอบของหนา ตาง VBE 2 สวน ดังนี้ สว นที่ 1 รายชื่อ Object ทอ่ี ยบู น ฟอรม ทงั้ หมด รวมถงึ Useform ดวย สว นที่ 2 เหตกุ ารณ (Event) ท่เี กดิ กับ Object เชน การคลิก, Enter, DblClick ทาํ ความรจู กั กบั Methods เมธอด (Methods) ใชก ําหนดการทาํ งานหรือเพิม่ ความสามารถใหกับ Object เชน เมือ่ มเี หตกุ ารณคลิก เกดิ ข้ึนกับปุม ปุม มคี วามสามารถทําการปด Form, ปุมมีความสามารถขยายใหญขนึ้ และเล่ือนขึ้นไปขางบนของ ฟอรม ฯลฯ ตัวอยา งท่ี 3 การเรยี กใช เมธอด (Methods) เพ่ือกําหนดความสามารถใหกบั ปุม (Command) เพอื่ ทําการส่งั การ ไปทเี่ มนบู าร เครือ่ งมือแมโคร Visual Basic Editor หรือ กด Alt+F11 เขา สูหนา ตา ง VBA 1. คลิก Insert  UserForm ปรากฏ UserForm1 2. กําหนด Properties ใหกบั Form และสรางปุม (Command) ดังรูปตอ ไปนี้ 88

3. Double Click ท่ี ปมุ CommandButton1  ปรากฏหนาตาง VBE 4. พิมพข อ ความตามรูปภาพดงั นี้ 6. คลิกเครอื่ งหมาย Run Toolbar หรือ F5 เพ่ือดผู ลงาน ในระหวางการพิมพ Code เม่อื มกี ารใชเครอ่ื งหมาย จดุ (.) จะปรากฏ เมธอด (Methods) เพื่อกาํ หนดการ ทํางานของปมุ CommandButton1 ดังรปู จะเหน็ ได เมธอด มกี ารทาํ งานไดหลายรูปแบบแลวแตเราจะทาํ การกําหนด ในตัวอยางน้ี ใหเลอื กเมธอด เปน Move เพ่อื เลอื่ น ปมุ ไปมาได โดยการ Double Click เพือ่ เลอื กรายการดังกลาว จากนัน้ จะปรากฏรูปแบบแนะนํา การกําหนดคาให ปมุ (Command Button) วิ่งไปทางไหนดี ใหท าํ การกําหนดคา ใหก ับ เมธอด 89

5.4 การสรา งฟอรมและการเขียนโปรแกรม VBA ตัวอยางที่ 4 สรา งโปรแกรมการกรอกขอ มลู ใหกับ Excel โดยผา น Form ท่เี ขียนจาก VBA ขั้นตอนการปฏบิ ตั ิ 1. สรางตาราง และ ออกแบบตาราง ดังนี้ (อยา ลืม Save นะคะ ตง้ั ชือ่ วา D:\\ BookSystem) 2. เรียก เคร่อื งมือ Visual Basic ออกมาแสดง บน Toolbar วิธีเรียก เครอ่ื งมอื 2.1 ไปท่ีเมนบู าร คลกิ แถบเคร่ืองมือ Visual Basic คลกิ ที่ Icon VBE : Visual Basic Editor 3. เขา สู Windows ของ VBA เม่อื เขาสู หนา ตาง ของ VBA ใหส ํารวจเครื่องมอื ดังน้ี Project Explorer, Properties, Toolbox มหี รอื ไม กรณไี มม ี Toolbox ที่ใชใ นการออกแบบ Form สืบเน่อื งจาก ตองอยูในหนาตาง Form เสยี กอ น 90

4. สราง UserForm ใหม ไปท่เี มนูบาร คลกิ เลอื ก Insert Userform ปรากฏหนา ตาง UserForm1 ใหม 5. ออกแบบ UseForm1 ดงั รปู 6. กาํ หนด คณุ สมบตั ิ ใหกับ คอนโทรล ดงั นี้ คอนโทรล พร็อพเพอรต้ี คา ทกี าํ หนด TextBox1 Name txtID TextBox2 Name txtNameB TextBox3 Name txtNameT CommandButton1 Name cmdAdd Caption เพิ่มขอมลู CommandButton2 Name cmdClear Caption ลา งหนาจอ CommandButton3 Name cmdClose Caption ปด หนา ตา งฟอรม UserForm1 Name frmbook Caption ขอมูลหนงั สอื เมื่อกําหนดคณุ สมบัตจิ นครอบ หนา ตา Form เปน ดงั น้ี 91

5. เขยี น Code คําสั่ง เพอื่ ควบคมุ การทาํ งานดงั นี้ 7. เม่ือทุกอยา งเรยี บรอยแลว ทดลอง Run ดนู ะคะ 7.1 กรอกขอมูลใน TextBox ตามรปู  คลิกปมุ เพ่ิมขอ มูล  ขอ มูลจะวิ่งไปปรากฏที่ตาราง Excel 7.2 คลิกปุมลางหนาจอ หนา จอที่กรอกจะถูกลางออก 7.3 คลกิ ปุมปด ฟอรม ฟอรม จะถกู ปด 92

หากปรากฏ กลอ งขอความ ดังกลาวแสดงวา มกี ารอางขอมลู ไมถ ูกตอ ง, พมิ พผ ิด, มกี ารพมิ พ Method เอง 8. สรางปุม เพื่อเรียก ฟอรม ขอ มูลหนังสอื ข้ึนมาใช 1. เรยี กเครอ่ื งมอื ที่ช่ือวา ฟอรม ขึ้นมาแสดงที่ Toolbar โดยไปทเี่ มนบู าร เลอื ก มมุ มอง 2. คลิก ปมุ  แลว ลากใหไดดงั รูป แกไ ขขอความ โดย คลิก ขวาที่ ปมุ 93

4. คลกิ ขวา ท่ีปุม คลกิ กรอกขอ มูล กําหนดแมโคร 5. ปรากฏหนา ตา ง กําหนดแมโคร ดงั รูป ใหค ลิก ปุม สราง 6. ในตาง Module1 พิมพ Code ตามรปู 6. ทดลอง Run เลย 94

ทดสอบความเขาใจตง้ั แตเ ร่ิมตน กําหนดใหน ักศกึ ษา วาง Mind Map สง ภายใน 1 หนากระดาษ 95

5.4 การใชฟ ลเตอร (Filter) เพือ่ คน หา การคน หาขอมลู ที่ตอ งการในฐานขอมลู ของ Excel ทม่ี ีหลายรอยหลายพันบรรทดั เพือ่ นํามาใชใ นการ ตัดสินใจหรือทาํ รายงานสง ผูบ ริหาร คงไมง ายหากปราศจากเคร่ืองมอื อยา งฟล เตอร หรือตัวกรอง การสง่ั ใหต ารางขอมลู แสดงเฉพาะขอมูลทต่ี องการตามเง่อื นไข เรียกวา “การกรอง” หรอื “ฟลเตอร” ไมต อ ง ใชวิธีมองหาขอ มูลท่ีตอ งการจากตารางขอมูล เมือ่ พบขอ มูลทตี่ องการแลวจะนําไปทาํ รายงานกม็ านัง่ Copy ไปวาง Paste ใน Work Sheet ใหมซ งึ่ จะเสยี เวลามากและผิดพลาดสูง Excel มเี ครือ่ งมือตวั กรอง (Filter) เพือ่ ใชในการคน หาขอ มูล แลว แสดงเฉพาะขอมลู เฉพาะทตี่ องคน หา เทา น้ัน และสามารถ พมิ พแ ละจดั ทาํ รายงานไดเ ลย ฟล เตอร (Filter) หรือตวั กรองขอมูล คอื อะไร? การคนหาและแสดงเฉพาะขอมูลทต่ี องการ เรยี กวา “การกรองขอมลู ” โปรแกรม Excel จะกรองขอ มูล ออกมา ในรูปแบบของตาราง Excel หนา ตาของตวั กรอง เปนดังรูปตอ ไปน้ี ตวั กรอง เพือ่ คน หาขอมลู ตัวกรอง ถูกคลิก แสดงรายการใหเ ลือก ดงั รปู 96

5.5 การสรางตวั กรองขอมลู และการใชงาน เมื่อมกี ารสรางตัวกรองใหก ับตารางขอ มลู แลว กส็ ามารถ Update ขอ มูลเพมิ่ ไดเ รื่อย ๆ ไดต ามปรกติ และ ตัว กรองขอมูล สามารถคน หาขอมูลทเ่ี พมิ่ เขา ไปไดเ ลยทันที  ขัน้ ตอนการสรางตวั กรองขอ มูล 1. คลิกเลือกเซลลท่เี ปน ชื่อคอลมั น ทต่ี อ งการคน หาขอ มูล 2. ไปทเ่ี มนบู าร คลิกขอ มูล ตัวกรอง ตัวกรองอัตโนมัติ 3. ปรากฏปุมตัวกรอง ที่หวั คอลมั นข นึ้ มาทันที 4. คลกิ ทปี่ มุ ตวั กรอง  จะปรากฏรายการใหเ ลอื ก ในตวั อยางทผี่ า นมา คลิกทปี่ ุมตวั กรอง ที่มีช่ือคอลัมนคํานาํ โดยมรี ายละเอยี ดดงั นี้  การยกเลกิ การกรองและปดการใชง านตวั กรอง 1. เซลลค อลัมนไหนทไี่ มต อ งการกรองขอ มลู คลิกเลือกเซลลคอลมั นนัน้ (หากไมต อ งการท้ังหมด กไ็ มตอ งคลิกเลือกอะไร) 2. ไปที่เมนูบาร คลิกขอมูล ตัวกรอง ตัวกรองอัตโนมตั ิ เอาเครื่องถูกออก 3. ตวั กรองจะหายไปอตั โนมตั ิ  ทดลองใชงานตัวกรอง ตวั อยางที่1 คน หาชอ่ื นักศกึ ษา ทีม่ อี ักษร ก นาํ หนา 1. คลิกปุมตวั กรองท่ี คอลัมน ช่ือ  กําหนดเอง.... 97

2. ปรากฏ หนาตา ง ตัวกรองอตั โนมตั ิทีก่ ําหนดเอง 3. พิมพข อความตามรูปตอ ไปนี้ 4. คลิก ตกลง  จะไดผ ลลพั ธต ามรูป คอื แสดงขอ มลู เฉพาะชื่อที่นําหนา ก (เคร่อื งหมาย * อกั ษรใดๆ) การใชส ญั ลักษณใ นการคนหาขอมูล สญั ลักษณ ? แทนอกั ษร หนงึ่ ตัว สญั ลกั ษณ * แทนกลมุ ตัวอักษรใด ๆ รปู แบบการใช 1. ?ก* หมายถงึ ขนึ้ ตนดว ยอกั ษรอะไรก็ได 1 ตวั อกั ษร แตอักษรตัวที่ 2 ตองเปน ก เครอื่ งหมาย * หลัง ก เปน กลุม อักษรใด ๆ 2. ส*ก หมายถงึ ข้ึนตน ดวย ส ตามดวยกลุมตัวอักษรอะไรก็ได แต ตองลงทา ยดวย ก 3. ??*A หมายถึง ..................................................................................................................... .................................................................................................... ....................... 4. ว????ส หมายถงึ ................................................................................................................ ............................................................................................................................ 98

ตัวอยางท่ี 2 การคน หาขอมูลคดั คาทีซ่ ้ําออก โดยมีขอมลู ดงั รปู นี้ 1. เลอื ก เซลลเปาหมายท่ีตอ งการกอน ในท่นี ้ี เลอื ก คอลัมนท่ีชื่อ วนั เดอื นปเกิด 2. ไปท่เี มนูบาร เครือ่ งมือ  ตวั กรอง ตวั กรองขนั้ สูง 3. ปรากฏหนา ตาง ตวั กรองข้ันสงู  ใสเ คร่อื ง  เฉพาะระเบยี นท่แี ตกตา งเทานั้น คลกิ ตกลง 4. ผลลพั ธทไ่ี ด 99

แบบฝกหัด ท่ี 1 บริษัทแหง หนงึ่ มีการเก็บขอมลู ลูกคา ใน ตาราง Excel ดังนี้ 1. Name 7. Street Address 2. City 8. State 3. Postcode 9. Country 4. Email 10. เบอรโทรศัพท 5. เบอรม อื ถือ 11. ชอื่ ผูท ตี่ ดิ ตอ ได 6. เบอรผทู ่ตี ดิ ตอได 12. ท่ีอยขู องผตู ิดตอ ได การบนั ทึกขอ มูลดงั กลาวจะทาํ การบันทึกลงใน Excel ซึ่งเกดิ ความยงุ ยากมาก ไมส ามารถ คนหาขอ มลู ใน ตาราง Excel ไดเ ลย ในฐานะที่นักศึกษาไดศ ึกษาจบสาขาคอมพิวเตอรธ ุรกิจ นกั ศึกษาจะทาํ อยางไร คําสัง่ ทําเด่ียว 1. เขียนโปรแกรมสง เปน ไฟล 2. จดั ทําเปน รายงาน แบงเปน บท ๆ ดงั นี้ บทที่ 1. บทนาํ บทที่ 2. ทฤษฎีทีเ่ กย่ี วของ บทท่ี 3. วธิ ีการดําเนินงาน บทที่ 4. สรปุ เสนอแนะ 3. รายงานหนา ชั้นเรยี น 100

แบบฝกหัดที่ 2 สง แผน Disk มานะคะ 101

102

. คําส่งั โดยแบงเปน 3 Sheet ออกแบบใหส วยงามและ จดั ทาํ งบประมาณโดยใชส ูตร ฟง กช นั ท่เี รียนมา 1. งบแสดงฐานะเงนิ สด 2. งบเงินสะสม 3. งบทรัพยส นิ 103