เนื้อหา
แม้ว่าคุณจะไม่ทราบวิธีเขียนโปรแกรม แต่ก็มีสถานที่หลายแห่ง (หนังสือ เว็บไซต์ ฟอรัม) ที่คุณสามารถค้นหาโค้ดแมโคร VBA สำเร็จรูปสำหรับงานทั่วไปจำนวนมากใน Excel จากประสบการณ์ของผม ผู้ใช้ส่วนใหญ่ไม่ช้าก็เร็วจะรวบรวมคอลเลกชั่นแมโครส่วนตัวเพื่อทำให้กระบวนการประจำเป็นอัตโนมัติ ไม่ว่าจะเป็นการแปลสูตรเป็นค่า แสดงผลรวมเป็นคำ หรือรวมเซลล์ตามสี และนี่คือปัญหาที่เกิดขึ้น – โค้ดแมโครใน Visual Basic จะต้องถูกจัดเก็บไว้ที่ใดที่หนึ่งเพื่อใช้งานในภายหลัง
ตัวเลือกที่ง่ายที่สุดคือการบันทึกโค้ดแมโครลงในไฟล์การทำงานโดยตรง โดยไปที่โปรแกรมแก้ไข Visual Basic โดยใช้แป้นพิมพ์ลัด อื่น ๆ+F11 และเพิ่มโมดูลเปล่าใหม่ผ่านเมนู แทรก – โมดูล:
อย่างไรก็ตาม มีข้อเสียหลายประการสำหรับวิธีนี้:
- หากมีไฟล์งานจำนวนมากและจำเป็นต้องใช้มาโครในทุกๆ ที่ เช่น มาโครสำหรับแปลงสูตรเป็นค่า คุณจะต้องคัดลอกโค้ด ในหนังสือทุกเล่ม.
- ต้องไม่ลืม บันทึกไฟล์ในรูปแบบที่เปิดใช้งานมาโคร (xlsm) หรือในรูปแบบหนังสือไบนารี (xlsb)
- เมื่อเปิดไฟล์ดังกล่าว การป้องกันมาโคร แต่ละครั้งจะออกคำเตือนที่จำเป็นต้องได้รับการยอมรับ (ดีหรือปิดการใช้งานการป้องกันอย่างสมบูรณ์ซึ่งอาจไม่พึงปรารถนาเสมอไป)
ทางออกที่หรูหรากว่าคือการสร้าง Add-in ของคุณเอง (Excel Add-in) - ไฟล์แยกในรูปแบบพิเศษ (xlam) ที่มีมาโคร "โปรด" ทั้งหมดของคุณ ข้อดีของวิธีนี้:
- ก็พอแล้ว เชื่อมต่อส่วนเสริมหนึ่งครั้ง ใน Excel และคุณสามารถใช้ขั้นตอนและฟังก์ชัน VBA ในไฟล์ใดก็ได้บนคอมพิวเตอร์เครื่องนี้ การบันทึกไฟล์การทำงานของคุณใหม่ในรูปแบบ xlsm- และ xlsb- ไม่จำเป็นเพราะ ซอร์สโค้ดจะไม่ถูกเก็บไว้ในไฟล์ แต่อยู่ในไฟล์เสริม
- การป้องกัน คุณจะไม่ถูกรบกวนจากมาโครเช่นกัน ส่วนเสริมนั้นตามคำจำกัดความแหล่งที่มาที่เชื่อถือได้
- ทำได้ แท็บแยก บนริบบอนของ Excel พร้อมปุ่มที่ดีในการเรียกใช้แมโคร add-in
- ส่วนเสริมเป็นไฟล์แยกต่างหาก ของเขา ง่ายต่อการพกพา จากคอมพิวเตอร์สู่คอมพิวเตอร์ แบ่งปันกับเพื่อนร่วมงาน หรือแม้แต่ขายมัน 😉
มาดูขั้นตอนทั้งหมดในการสร้าง Microsoft Excel add-in ของคุณเองทีละขั้นตอน
ขั้นตอนที่ 1. สร้างไฟล์เสริม
เปิด Microsoft Excel ด้วยเวิร์กบุ๊กเปล่าและบันทึกไว้ในชื่อที่เหมาะสม (เช่น MyExcelAddin) ในรูปแบบ add-in ด้วยคำสั่ง ไฟล์ – บันทึกเป็น หรือกุญแจ F12ระบุประเภทไฟล์ โปรแกรมเสริม Excel:
โปรดทราบว่าตามค่าเริ่มต้น Excel จะเก็บ Add-in ไว้ในโฟลเดอร์ C:UsersYour_nameAppDataRoamingMicrosoftAddIns แต่โดยหลักการแล้ว คุณสามารถระบุโฟลเดอร์อื่นๆ ที่สะดวกสำหรับคุณได้
ขั้นตอนที่ 2 เราเชื่อมต่อ Add-in . ที่สร้างขึ้น
ตอนนี้ Add-in ที่เราสร้างขึ้นในขั้นตอนสุดท้าย MyExcelAddin ต้องเชื่อมต่อกับ Excel โดยไปที่เมนู ไฟล์ – ตัวเลือก – ส่วนเสริม (ไฟล์ — ตัวเลือก — ส่วนเสริม), คลิกที่ปุ่ม เกี่ยวกับเรา (ไป) ที่ด้านล่างของหน้าต่าง ในหน้าต่างที่เปิดขึ้น ให้คลิกปุ่ม รีวิว (เรียกดู) และระบุตำแหน่งของไฟล์เสริมของเรา
หากคุณทำทุกอย่างถูกต้องแล้ว .ของเรา MyExcelAddin ควรปรากฏในรายการส่วนเสริมที่มี:
ขั้นตอนที่ 3 เพิ่มมาโครให้กับ add-in
Add-in ของเราเชื่อมต่อกับ Excel และทำงานได้สำเร็จ แต่ยังไม่มีมาโครเดียวในนั้น มาเติมกันเถอะ ในการดำเนินการนี้ ให้เปิดตัวแก้ไข Visual Basic ด้วยแป้นพิมพ์ลัด อื่น ๆ+F11 หรือโดยปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา). ถ้าแท็บ ผู้พัฒนา มองไม่เห็นก็แสดงผลได้ผ่าน ไฟล์ – ตัวเลือก – การตั้งค่าริบบอน (ไฟล์ — ตัวเลือก — กำหนด Ribbon เอง).
ควรมีหน้าต่างที่มุมซ้ายบนของตัวแก้ไข โครงการ (ถ้ามองไม่เห็นให้เปิดผ่านเมนู ดู — Project Explorer):
หน้าต่างนี้แสดงเวิร์กบุ๊กที่เปิดอยู่ทั้งหมดและเรียกใช้ Add-in ของ Microsoft Excel รวมถึงของเราด้วย โครงการวีบีเอ (MyExcelAddin.xlam) เลือกด้วยเมาส์และเพิ่มโมดูลใหม่ผ่านเมนู แทรก – โมดูล. ในโมดูลนี้ เราจะจัดเก็บโค้ด VBA ของมาโคร Add-in ของเรา
คุณสามารถพิมพ์รหัสตั้งแต่ต้น (ถ้าคุณรู้วิธีเขียนโปรแกรม) หรือคัดลอกจากที่อื่นสำเร็จรูป (ซึ่งง่ายกว่ามาก) สำหรับการทดสอบ ให้ป้อนรหัสของมาโครที่เรียบง่ายแต่มีประโยชน์ในโมดูลว่างที่เพิ่มเข้ามา:
หลังจากป้อนรหัสแล้ว อย่าลืมคลิกที่ปุ่มบันทึก (ดิสเก็ตต์) ที่มุมซ้ายบน
มาโครของเรา สูตรถึงค่าอย่างที่คุณสามารถจินตนาการได้ แปลงสูตรเป็นค่าในช่วงที่เลือกไว้ล่วงหน้า บางครั้งมาโครเหล่านี้เรียกอีกอย่างว่า ขั้นตอน. เมื่อต้องการเรียกใช้ คุณต้องเลือกเซลล์ที่มีสูตรและเปิดกล่องโต้ตอบพิเศษ แมโคร จากแท็บ ผู้พัฒนา (ผู้พัฒนา — มาโคร) หรือแป้นพิมพ์ลัด อื่น ๆ+F8. โดยปกติ หน้าต่างนี้จะแสดงมาโครที่พร้อมใช้งานจากเวิร์กบุ๊กที่เปิดอยู่ทั้งหมด แต่จะไม่เห็นมาโคร Add-in ที่นี่ อย่างไรก็ตาม เราสามารถป้อนชื่อขั้นตอนของเราลงในฟิลด์ได้ ชื่อมาโคร (ชื่อมาโคร)จากนั้นคลิกปุ่ม วิ่ง (วิ่ง) – และมาโครของเราจะทำงาน:
| |
ที่นี่คุณยังสามารถกำหนดแป้นพิมพ์ลัดเพื่อเปิดใช้มาโครได้อย่างรวดเร็ว - ปุ่มมีหน้าที่รับผิดชอบ พารามิเตอร์ (ตัวเลือก) ในหน้าต่างก่อนหน้า แมโคร:
เมื่อกำหนดคีย์ โปรดทราบว่าคีย์จะคำนึงถึงขนาดตัวพิมพ์และเลย์เอาต์ของแป้นพิมพ์ ดังนั้นหากคุณกำหนดชุดค่าผสมเช่น Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally เปลี่ยนเพื่อรับตัวพิมพ์ใหญ่
เพื่อความสะดวก เรายังสามารถเพิ่มปุ่มสำหรับมาโครของเราลงในแถบเครื่องมือด่วนที่มุมซ้ายบนของหน้าต่าง ในการดำเนินการนี้ ให้เลือก ไฟล์ – ตัวเลือก – แถบเครื่องมือด่วน (ไฟล์ — ตัวเลือก — ปรับแต่งแถบเครื่องมือด่วน)จากนั้นในรายการดรอปดาวน์ที่ด้านบนของหน้าต่างตัวเลือก แมโคร. หลังจากนั้นมาโครของเรา สูตรถึงค่า สามารถวางบนแผงด้วยปุ่ม เพิ่ม (เพิ่ม) และเลือกไอคอนสำหรับมันด้วยปุ่ม เปลี่ยนแปลง (แก้ไข):
ขั้นตอนที่ 4. เพิ่มฟังก์ชันให้กับ add-in
แต่ มาโครขั้นตอนนอกจากนี้ยังมี มาโครฟังก์ชัน หรือที่เรียกกันว่า UDF (User Defined Function = ฟังก์ชันที่ผู้ใช้กำหนด) มาสร้างโมดูลแยกกันในโปรแกรมเสริมของเรา (คำสั่งเมนู แทรก – โมดูล) และวางโค้ดของฟังก์ชันต่อไปนี้ที่นั่น:
ง่ายที่จะเห็นว่าฟังก์ชันนี้จำเป็นสำหรับการดึงภาษีมูลค่าเพิ่มจากจำนวนเงินรวมภาษีมูลค่าเพิ่ม แน่นอนว่าไม่ใช่ทวินามของนิวตัน แต่มันจะทำให้เราเป็นตัวอย่างในการแสดงหลักการพื้นฐาน
โปรดทราบว่าไวยากรณ์ของฟังก์ชันแตกต่างจากโพรซีเดอร์:
- ใช้ก่อสร้าง การทำงาน …. ฟังก์ชั่นสิ้นสุด แทน ย่อย … สิ้นสุด ย่อย
- หลังชื่อของฟังก์ชัน อาร์กิวเมนต์ของฟังก์ชันจะแสดงในวงเล็บ
- ในเนื้อหาของฟังก์ชัน การคำนวณที่จำเป็นจะถูกดำเนินการ จากนั้นจึงกำหนดผลลัพธ์ให้กับตัวแปรที่มีชื่อของฟังก์ชัน
โปรดทราบด้วยว่าไม่จำเป็นต้องใช้ฟังก์ชันนี้ และไม่สามารถทำงานได้เหมือนกับขั้นตอนมาโครก่อนหน้าผ่านกล่องโต้ตอบ แมโคร และปุ่ม วิ่ง. ฟังก์ชันมาโครดังกล่าวควรใช้เป็นฟังก์ชันเวิร์กชีตมาตรฐาน (SUM, IF, VLOOKUP…) เช่น เพียงป้อนในเซลล์ใดๆ โดยระบุมูลค่าของจำนวนเงินที่มี VAT เป็นอาร์กิวเมนต์:
… หรือป้อนผ่านกล่องโต้ตอบมาตรฐานเพื่อแทรกฟังก์ชัน (button fx ในแถบสูตร) เลือกหมวดหมู่ กำหนดโดยผู้ใช้ (กำหนดโดยผู้ใช้):
ช่วงเวลาที่ไม่พึงประสงค์เพียงอย่างเดียวที่นี่คือไม่มีคำอธิบายปกติของฟังก์ชันที่ด้านล่างของหน้าต่าง ในการเพิ่มคุณจะต้องทำสิ่งต่อไปนี้:
- เปิด Visual Basic Editor ด้วยแป้นพิมพ์ลัด อื่น ๆ+F11
- เลือก Add-in ในแผงโครงการแล้วกดปุ่ม F2เพื่อเปิดหน้าต่าง Object Browser
- เลือกโครงการเสริมของคุณจากรายการดรอปดาวน์ที่ด้านบนของหน้าต่าง
- คลิกขวาที่ฟังก์ชันที่ปรากฏขึ้นและเลือกคำสั่ง อสังหาริมทรัพย์ .
- ป้อนคำอธิบายของฟังก์ชันในหน้าต่าง รายละเอียด
- บันทึกไฟล์เสริม และรีสตาร์ท excel.
หลังจากรีสตาร์ท ฟังก์ชันควรแสดงคำอธิบายที่เราป้อน:
ขั้นตอนที่ 5. สร้างแท็บ Add-on ในอินเทอร์เฟซ
ขั้นสุดท้ายแม้ว่าจะไม่ได้บังคับ แต่สัมผัสที่น่าพึงพอใจคือการสร้างแท็บแยกต่างหากพร้อมปุ่มเพื่อเรียกใช้มาโครของเรา ซึ่งจะปรากฏในอินเทอร์เฟซของ Excel หลังจากเชื่อมต่อ Add-in ของเรา
ข้อมูลเกี่ยวกับแท็บที่แสดงโดยค่าเริ่มต้นจะอยู่ภายในหนังสือและต้องจัดรูปแบบในโค้ด XML พิเศษ วิธีที่ง่ายที่สุดในการเขียนและแก้ไขโค้ดดังกล่าวคือการใช้โปรแกรมพิเศษ - ตัวแก้ไข XML หนึ่งในวิธีที่สะดวกที่สุด (และฟรี) คือโปรแกรมของ Maxim Novikov ตัวแก้ไข XML ของ Ribbon.
อัลกอริทึมสำหรับการทำงานกับมันมีดังนี้:
- ปิดหน้าต่าง Excel ทั้งหมดเพื่อไม่ให้ไฟล์ขัดแย้งกันเมื่อเราแก้ไขโค้ด XML ของ Add-in
- เปิดโปรแกรม Ribbon XML Editor และเปิดไฟล์ MyExcelAddin.xlam ในนั้น
- ด้วยปุ่ม แท็บ ที่มุมบนซ้าย ให้เพิ่มข้อมูลโค้ดสำหรับแท็บใหม่:
- คุณต้องใส่เครื่องหมายคำพูดเปล่า id แท็บและกลุ่มของเรา (ตัวระบุที่ไม่ซ้ำ) และใน ฉลาก – ชื่อของแท็บของเราและกลุ่มของปุ่มบนนั้น:
- ด้วยปุ่ม ปุ่ม ที่แผงด้านซ้าย ให้เพิ่มโค้ดเปล่าสำหรับปุ่มและเพิ่มแท็กเข้าไป:
- ฉลาก เป็นข้อความบนปุ่ม
— อิมเมจเอ็มเอสโอ — นี่คือชื่อตามเงื่อนไขของรูปภาพบนปุ่ม ฉันใช้ไอคอนปุ่มสีแดงชื่อ AnimationCustomAddExitDialog ชื่อของปุ่มที่มีอยู่ทั้งหมด (และมีหลายร้อยปุ่ม!) สามารถพบได้บนเว็บไซต์จำนวนมากบนอินเทอร์เน็ตหากคุณค้นหาด้วยคำหลัก "imageMso" สำหรับผู้เริ่มต้น คุณสามารถไปที่นี่
- บนการดำเนินการ – นี่คือชื่อของขั้นตอนการโทรกลับ – มาโครสั้นพิเศษที่จะเรียกใช้มาโครหลักของเรา สูตรถึงค่า. คุณสามารถเรียกขั้นตอนนี้อะไรก็ได้ที่คุณต้องการ เราจะเพิ่มในภายหลัง
- คุณสามารถตรวจสอบความถูกต้องของทุกอย่างที่ทำโดยใช้ปุ่มที่มีเครื่องหมายถูกสีเขียวที่ด้านบนของแถบเครื่องมือ ในที่เดียวกัน ให้คลิกที่ปุ่มที่มีฟลอปปีดิสก์เพื่อบันทึกการเปลี่ยนแปลงทั้งหมด
- ปิดตัวแก้ไข Ribbon XML
- เปิด Excel ไปที่ตัวแก้ไข Visual Basic และเพิ่มขั้นตอนการโทรกลับในมาโครของเรา ฆ่าสูตรเพื่อเรียกใช้มาโครหลักของเราเพื่อแทนที่สูตรด้วยค่า
- เราบันทึกการเปลี่ยนแปลงและกลับไปที่ Excel ตรวจสอบผลลัพธ์:
นั่นคือทั้งหมด – ส่วนเสริมพร้อมใช้งานแล้ว เติมด้วยขั้นตอนและฟังก์ชันของคุณเอง เพิ่มปุ่มที่สวยงาม และการใช้มาโครในงานของคุณจะง่ายขึ้นมาก
- มาโครคืออะไร ใช้งานอย่างไรในงานของคุณ รับโค้ดแมโครใน Visual Basic ได้ที่ไหน
- วิธีทำ splash screen เมื่อเปิดสมุดงานใน Excel
- หนังสือมาโครส่วนบุคคลคืออะไรและใช้งานอย่างไร