เนื้อหา
ฟังก์ชัน VBA ในตัว
ก่อนที่คุณจะเริ่มสร้างฟังก์ชัน VBA ของคุณเอง ถือเป็นการดีที่จะรู้ว่า Excel VBA มีคอลเลกชั่นของฟังก์ชันในตัวที่สร้างไว้ล่วงหน้ามากมาย ซึ่งคุณสามารถใช้ในขณะที่เขียนโค้ดของคุณ
รายการฟังก์ชันเหล่านี้สามารถดูได้ในโปรแกรมแก้ไข VBA:
- เปิดสมุดงาน Excel และเปิดตัวแก้ไข VBA (คลิกเพื่อทำสิ่งนี้ Alt + F11) แล้วกด F2.
- เลือกห้องสมุดจากรายการดรอปดาวน์ที่ด้านบนซ้ายของหน้าจอ VBA.
- รายการคลาสและฟังก์ชัน VBA ในตัวจะปรากฏขึ้น คลิกที่ชื่อฟังก์ชันเพื่อแสดงคำอธิบายสั้นๆ ที่ด้านล่างของหน้าต่าง กด F1 จะเปิดหน้าความช่วยเหลือออนไลน์สำหรับคุณสมบัตินั้น
นอกจากนี้ คุณสามารถดูรายการฟังก์ชัน VBA ในตัวพร้อมตัวอย่างได้ที่ Visual Basic Developer Center
ขั้นตอนแบบกำหนดเอง "Function" และ "Sub" ใน VBA
ใน Excel Visual Basic ชุดคำสั่งที่ทำงานเฉพาะจะถูกวางไว้ในกระบวนงาน ฟังก์ชัน (ฟังก์ชัน) หรือ ต (รูทีนย่อย). ความแตกต่างที่สำคัญระหว่างขั้นตอน ฟังก์ชัน и ต คือขั้นตอน ฟังก์ชัน ส่งคืนผลลัพธ์ ขั้นตอน ต - ไม่.
ดังนั้น หากคุณต้องการดำเนินการและได้ผลลัพธ์ (เช่น รวมตัวเลขหลายๆ ตัว) มักจะใช้ขั้นตอนนั้น ฟังก์ชันและเพื่อดำเนินการบางอย่าง (เช่น เปลี่ยนการจัดรูปแบบกลุ่มเซลล์) คุณต้องเลือกขั้นตอน ต.
ข้อโต้แย้ง
ข้อมูลต่างๆ สามารถส่งผ่านไปยังขั้นตอน VBA โดยใช้อาร์กิวเมนต์ รายการอาร์กิวเมนต์ถูกระบุเมื่อประกาศขั้นตอน ตัวอย่างเช่น ขั้นตอน ต ใน VBA จะเพิ่มจำนวนเต็มที่กำหนด (จำนวนเต็ม) ให้กับแต่ละเซลล์ในช่วงที่เลือก คุณสามารถส่งหมายเลขนี้ไปยังโพรซีเดอร์โดยใช้อาร์กิวเมนต์ดังนี้:
Sub AddToCells(i As Integer) ... End Sub
โปรดทราบว่ามีข้อโต้แย้งสำหรับขั้นตอน ฟังก์ชัน и ต ใน VBA เป็นทางเลือก ขั้นตอนบางอย่างไม่ต้องการการโต้แย้ง
อาร์กิวเมนต์ทางเลือก
ขั้นตอน VBA สามารถมีอาร์กิวเมนต์ที่เป็นทางเลือก นี่เป็นอาร์กิวเมนต์ที่ผู้ใช้สามารถระบุได้หากต้องการ และหากไม่ระบุ โพรซีเดอร์จะใช้ค่าเริ่มต้นสำหรับพวกเขา
กลับไปที่ตัวอย่างก่อนหน้านี้ เพื่อสร้างอาร์กิวเมนต์จำนวนเต็มให้กับฟังก์ชันที่ไม่บังคับ จะมีการประกาศดังนี้:
Sub AddToCells (ตัวเลือก i As Integer = 0)
ในกรณีนี้ อาร์กิวเมนต์จำนวนเต็ม i ค่าเริ่มต้นจะเป็น 0
สามารถมีอาร์กิวเมนต์ที่เป็นทางเลือกได้หลายอาร์กิวเมนต์ในโพรซีเดอร์ ซึ่งทั้งหมดจะแสดงอยู่ที่ส่วนท้ายของรายการอาร์กิวเมนต์
ส่งผ่านอาร์กิวเมนต์ตามค่าและโดยการอ้างอิง
อาร์กิวเมนต์ใน VBA สามารถส่งต่อไปยังโพรซีเดอร์ได้สองวิธี:
- บายวัล – ส่งผ่านอาร์กิวเมนต์ตามค่า ซึ่งหมายความว่าเฉพาะค่า (นั่นคือ สำเนาของอาร์กิวเมนต์) ที่ถูกส่งไปยังโพรซีเดอร์ ดังนั้นการเปลี่ยนแปลงใดๆ ที่เกิดขึ้นกับอาร์กิวเมนต์ภายในโพรซีเดอร์จะสูญหายไปเมื่อโพรซีเดอร์ออก
- โดยRef - ผ่านอาร์กิวเมนต์โดยการอ้างอิง นั่นคือที่อยู่จริงของตำแหน่งอาร์กิวเมนต์ในหน่วยความจำจะถูกส่งไปยังโพรซีเดอร์ การเปลี่ยนแปลงใดๆ ที่เกิดขึ้นกับอาร์กิวเมนต์ภายในโพรซีเดอร์จะถูกบันทึกเมื่อโพรซีเดอร์ออก
การใช้คำหลัก บายวัล or โดยRef ในการประกาศโพรซีเดอร์ คุณสามารถระบุวิธีส่งอาร์กิวเมนต์ไปยังโพรซีเดอร์ได้ ซึ่งแสดงในตัวอย่างด้านล่าง:
Sub AddToCells(ByVal i As Integer) ... End Sub | ในกรณีนี้ อาร์กิวเมนต์จำนวนเต็ม i ผ่านไปด้วยค่า หลังจากออกจากขั้นตอน ต ทั้งหมดทำด้วย i การเปลี่ยนแปลงจะหายไป |
Sub AddToCells(ByRef i As Integer) ... End Sub | ในกรณีนี้ อาร์กิวเมนต์จำนวนเต็ม i ผ่านโดยการอ้างอิง หลังจากออกจากขั้นตอน ต ทั้งหมดทำด้วย i การเปลี่ยนแปลงจะถูกเก็บไว้ในตัวแปรที่ส่งผ่านไปยังโพรซีเดอร์ ต. |
โปรดจำไว้ว่าอาร์กิวเมนต์ใน VBA จะถูกส่งผ่านโดยการอ้างอิงโดยค่าเริ่มต้น กล่าวอีกนัยหนึ่งถ้าไม่ใช้คำหลัก บายวัล or โดยRefจากนั้นอาร์กิวเมนต์จะถูกส่งผ่านโดยการอ้างอิง
ก่อนดำเนินการตามขั้นตอน ฟังก์ชัน и ต ในรายละเอียดเพิ่มเติม จะเป็นประโยชน์ในการดูคุณสมบัติและความแตกต่างระหว่างขั้นตอนทั้งสองประเภทนี้อีกครั้ง ต่อไปนี้เป็นการสนทนาสั้น ๆ เกี่ยวกับขั้นตอน VBA ฟังก์ชัน и ต และแสดงตัวอย่างง่ายๆ
ขั้นตอน VBA «ฟังก์ชั่น»
ตัวแก้ไข VBA รู้จักขั้นตอน ฟังก์ชันเมื่อพบกลุ่มคำสั่งที่อยู่ระหว่างคำสั่งเปิดและปิดต่อไปนี้:
ฟังก์ชัน ... สิ้นสุดฟังก์ชัน
ดังที่ได้กล่าวไว้ก่อนหน้านี้ ขั้นตอน ฟังก์ชัน ใน VBA (ตรงข้ามกับ ต) ส่งคืนค่า กฎต่อไปนี้ใช้กับค่าส่งคืน:
- ต้องประกาศชนิดข้อมูลของค่าตอบแทนในส่วนหัวของขั้นตอน ฟังก์ชัน.
- ตัวแปรที่มีค่าส่งคืนจะต้องตั้งชื่อเหมือนกับโพรซีเดอร์ ฟังก์ชัน. ตัวแปรนี้ไม่จำเป็นต้องประกาศแยกกัน เนื่องจากมีอยู่แล้วเป็นส่วนสำคัญของขั้นตอน ฟังก์ชัน.
นี่เป็นตัวอย่างที่ดีในตัวอย่างต่อไปนี้
ตัวอย่างฟังก์ชัน VBA: การดำเนินการทางคณิตศาสตร์กับตัวเลข 3 ตัว
ต่อไปนี้เป็นตัวอย่างของรหัสขั้นตอน VBA ฟังก์ชันซึ่งรับสามอาร์กิวเมนต์ประเภท สอง (ตัวเลขทศนิยมความแม่นยำสองเท่า) เป็นผลให้ขั้นตอนส่งกลับจำนวนอีกประเภท สองเท่ากับผลรวมของสองอาร์กิวเมนต์แรกลบอาร์กิวเมนต์ที่สาม:
ฟังก์ชัน SumMinus (dNum1 เป็นสองเท่า, dNum2 เป็นสองเท่า, dNum3 เป็นสองเท่า) เป็น Double SumMinus = dNum1 + dNum2 - dNum3 สิ้นสุดฟังก์ชัน
ขั้นตอน VBA ที่ง่ายมากนี้ ฟังก์ชัน แสดงให้เห็นว่าข้อมูลถูกส่งไปยังโพรซีเดอร์ผ่านอาร์กิวเมนต์อย่างไร คุณจะเห็นว่าประเภทข้อมูลที่ส่งคืนโดยขั้นตอนนั้นถูกกำหนดเป็น สอง (คำว่า เป็นสองเท่า หลังรายการโต้แย้ง) ตัวอย่างนี้ยังแสดงให้เห็นว่าผลลัพธ์ของขั้นตอนเป็นอย่างไร ฟังก์ชัน เก็บไว้ในตัวแปรที่มีชื่อเดียวกับชื่อขั้นตอน
การเรียกขั้นตอน VBA "ฟังก์ชัน"
หากขั้นตอนง่ายๆข้างต้น ฟังก์ชัน แทรกลงในโมดูลในตัวแก้ไข Visual Basic สามารถเรียกจากขั้นตอน VBA อื่น ๆ หรือใช้บนแผ่นงานในสมุดงาน Excel
เรียกขั้นตอน VBA "ฟังก์ชัน" จากขั้นตอนอื่น
การรักษาอื่นๆ ฟังก์ชัน สามารถเรียกจากขั้นตอน VBA อื่นได้โดยเพียงแค่กำหนดขั้นตอนนั้นให้กับตัวแปร ตัวอย่างต่อไปนี้แสดงการเรียกโพรซีเดอร์ ซัมมินัสซึ่งกำหนดไว้ข้างต้น
Sub main() Dim Total เป็น Double Total = SumMinus(5, 4, 3) End Sub
เรียกขั้นตอน VBA "ฟังก์ชัน" จากแผ่นงาน
ขั้นตอน VBA ฟังก์ชัน สามารถเรียกจากเวิร์กชีต Excel ในลักษณะเดียวกับฟังก์ชัน Excel ในตัวอื่นๆ ดังนั้นขั้นตอนที่สร้างขึ้นในตัวอย่างที่แล้ว ฟังก์ชัน - ซัมมินัส สามารถเรียกได้โดยป้อนนิพจน์ต่อไปนี้ลงในเซลล์เวิร์กชีต:
=SumMinus(10, 5, 2)
ขั้นตอน VBA «ย่อย»
ตัวแก้ไข VBA เข้าใจว่ามีขั้นตอนอยู่ข้างหน้ามัน ตเมื่อพบกลุ่มคำสั่งที่อยู่ระหว่างคำสั่งเปิดและปิดต่อไปนี้:
ย่อย ... สิ้นสุด ย่อย
ขั้นตอน VBA "ย่อย": ตัวอย่างที่ 1 การจัดตำแหน่งกึ่งกลางและการเปลี่ยนแปลงขนาดฟอนต์ในช่วงเซลล์ที่เลือก
พิจารณาตัวอย่างขั้นตอน VBA อย่างง่าย ตซึ่งมีหน้าที่เปลี่ยนการจัดรูปแบบช่วงของเซลล์ที่เลือก เซลล์ถูกจัดกึ่งกลาง (ทั้งแนวตั้งและแนวนอน) และขนาดฟอนต์จะเปลี่ยนเป็นขนาดที่ผู้ใช้กำหนด:
รูปแบบย่อย_Centered_And_Sized (ตัวเลือก iFontSize As Integer = 10) การเลือก HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub
ขั้นตอนนี้ ต ดำเนินการ แต่ไม่ส่งคืนผลลัพธ์
ตัวอย่างนี้ยังใช้อาร์กิวเมนต์ที่เป็นตัวเลือก ขนาดตัวอักษร. ถ้าข้อโต้แย้ง ขนาดตัวอักษร ไม่ผ่านขั้นตอน ตค่าเริ่มต้นของมันคือ 10 อย่างไรก็ตาม หากอาร์กิวเมนต์ ขนาดตัวอักษร ผ่านขั้นตอน ตจากนั้นช่วงของเซลล์ที่เลือกจะถูกตั้งค่าเป็นขนาดฟอนต์ที่ผู้ใช้กำหนด
ขั้นตอนย่อย VBA: ตัวอย่างที่ 2: จัดกึ่งกลางและแบบอักษรตัวหนาในช่วงเซลล์ที่เลือก
ขั้นตอนต่อไปนี้คล้ายกับขั้นตอนที่กล่าวถึง แต่คราวนี้ แทนที่จะใช้รูปแบบฟอนต์ตัวหนากับช่วงของเซลล์ที่เลือกไว้ นี่เป็นขั้นตอนตัวอย่าง ตซึ่งไม่มีอาร์กิวเมนต์:
รูปแบบย่อย_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub
เรียกขั้นตอน "ย่อย" ใน Excel VBA
เรียกขั้นตอน VBA "ย่อย" จากขั้นตอนอื่น
ในการเรียกขั้นตอน VBA ต จากขั้นตอน VBA อื่น คุณต้องเขียนคีย์เวิร์ด โทร, ชื่อขั้นตอน ต และเพิ่มเติมในวงเล็บคือข้อโต้แย้งของขั้นตอน ดังแสดงในตัวอย่างด้านล่าง:
Sub main() Call Format_Centered_And_Sized(20) End Sub
ถ้าขั้นตอน Format_Centered_And_Sized มีอาร์กิวเมนต์มากกว่าหนึ่งอาร์กิวเมนต์ โดยต้องคั่นด้วยเครื่องหมายจุลภาค แบบนี้:
Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub
เรียกขั้นตอน VBA “ย่อย” จากแผ่นงาน
การรักษาอื่นๆ ต ไม่สามารถป้อนลงในเซลล์แผ่นงาน Excel ได้โดยตรง ซึ่งสามารถทำได้ด้วยขั้นตอน ฟังก์ชันเพราะขั้นตอน ต ไม่คืนค่า อย่างไรก็ตาม ขั้นตอน ตซึ่งไม่มีข้อโต้แย้งและประกาศเป็น สาธารณะ (ดังที่แสดงด้านล่าง) จะพร้อมใช้งานสำหรับผู้ใช้แผ่นงาน ดังนั้นหากขั้นตอนง่าย ๆ ที่กล่าวข้างต้น ต แทรกลงในโมดูลใน Visual Basic Editor ขั้นตอน Format_Centered_And_Bold จะสามารถใช้ได้ในแผ่นงาน Excel และขั้นตอน Format_Centered_And_Sized – จะใช้ไม่ได้เพราะมันมีข้อโต้แย้ง
นี่เป็นวิธีง่ายๆ ในการเรียกใช้ (หรือดำเนินการ) ขั้นตอน ต, สามารถเข้าถึงได้จากแผ่นงาน:
- ข่าวประชา Alt + F8 (กดแป้น อื่น ๆ และในขณะที่กดค้างไว้ให้กดปุ่ม F8).
- ในรายการมาโครที่ปรากฏขึ้น ให้เลือกมาโครที่คุณต้องการเรียกใช้
- ข่าวประชา วิ่ง (วิ่ง)
เพื่อดำเนินการตามขั้นตอน ต คุณสามารถกำหนดแป้นพิมพ์ลัดได้อย่างรวดเร็วและง่ายดาย สำหรับสิ่งนี้:
- ข่าวประชา Alt + F8.
- ในรายการมาโครที่ปรากฏขึ้น ให้เลือกรายการที่คุณต้องการกำหนดแป้นพิมพ์ลัดให้
- ข่าวประชา พารามิเตอร์ (ตัวเลือก) และในกล่องโต้ตอบที่ปรากฏขึ้น ให้ป้อนแป้นพิมพ์ลัด
- ข่าวประชา OK และปิดกล่องโต้ตอบ แมโคร (มาโคร).
เรียน เมื่อกำหนดแป้นพิมพ์ลัดให้กับมาโคร ตรวจสอบให้แน่ใจว่าไม่ได้ใช้เป็นมาตรฐานใน Excel (ตัวอย่างเช่น Ctrl + C). หากคุณเลือกแป้นพิมพ์ลัดที่มีอยู่แล้ว แป้นพิมพ์ลัดจะถูกกำหนดให้กับมาโครใหม่ และด้วยเหตุนี้ ผู้ใช้อาจเริ่มมาโครโดยไม่ได้ตั้งใจ
ขอบเขตขั้นตอน VBA
ส่วนที่ 2 ของบทช่วยสอนนี้กล่าวถึงขอบเขตของตัวแปรและค่าคงที่และบทบาทของคำหลัก สาธารณะ и ส่วนตัว. คำหลักเหล่านี้ยังสามารถใช้กับขั้นตอน VBA:
ย่อยสาธารณะ AddToCells(i As Integer) ... End Sub | หากการประกาศโพรซีเดอร์นำหน้าด้วยคีย์เวิร์ด สาธารณะจากนั้นขั้นตอนจะใช้ได้กับทุกโมดูลในโปรเจ็กต์ VBA นั้น |
ย่อยส่วนตัว AddToCells(i As Integer) ... End Sub | หากการประกาศโพรซีเดอร์นำหน้าด้วยคีย์เวิร์ด ส่วนตัวจากนั้นขั้นตอนนี้จะใช้ได้เฉพาะกับโมดูลปัจจุบันเท่านั้น ไม่สามารถเรียกได้ในขณะที่อยู่ในโมดูลอื่นหรือจากสมุดงาน Excel |
จำไว้ว่าถ้าก่อนที่จะประกาศขั้นตอน VBA ฟังก์ชัน or ต ไม่ได้แทรกคีย์เวิร์ด คุณสมบัติเริ่มต้นถูกตั้งค่าสำหรับโพรซีเดอร์ สาธารณะ (นั่นคือจะสามารถใช้ได้ทุกที่ในโครงการ VBA นี้) ซึ่งตรงกันข้ามกับการประกาศตัวแปร ซึ่งโดยค่าเริ่มต้นคือ ส่วนตัว.
ออกจากขั้นตอน VBA "Function" และ "Sub" ก่อนกำหนด
หากคุณต้องการยุติการดำเนินการตามขั้นตอน VBA ฟังก์ชัน or ตโดยไม่ต้องรอการสิ้นสุดตามธรรมชาติของมันจึงมีโอเปอเรเตอร์ ออกจากฟังก์ชัน и ออกจาก Sub. การใช้ตัวดำเนินการเหล่านี้แสดงไว้ด้านล่างโดยใช้ขั้นตอนง่ายๆ เป็นตัวอย่าง ฟังก์ชันA ที่คาดว่าจะได้รับอาร์กิวเมนต์ในเชิงบวกเพื่อดำเนินการต่อไป หากค่าที่ไม่เป็นบวกถูกส่งไปยังโพรซีเดอร์ จะไม่สามารถดำเนินการต่อไปได้ ดังนั้นผู้ใช้ควรได้รับข้อความแสดงข้อผิดพลาดและโพรซีเดอร์ควรออกทันที:
ฟังก์ชัน VAT_Amount(sVAT_Rate As Single) เป็น Single VAT_Amount = 0 ถ้า sVAT_Rate <= 0 จากนั้น MsgBox "คาดว่าค่าบวกของ sVAT_Rate แต่ได้รับ" & sVAT_Rate ออกจากฟังก์ชัน สิ้นสุด ถ้า ... สิ้นสุดฟังก์ชัน
โปรดทราบก่อนเสร็จสิ้นขั้นตอน ฟังก์ชัน - VAT_จำนวนเงินแทรกฟังก์ชัน VBA ในตัวลงในโค้ด กล่องข้อความซึ่งแสดงป๊อปอัปคำเตือนแก่ผู้ใช้