บทช่วยสอนฟังก์ชัน Excel VBA: ส่งคืน การโทร ตัวอย่าง

โดย: ชาร์ล็อตต์มิลเลอร์ ชาร์ล็อตต์มิลเลอร์
Hours วันที่อัพเดท

⚡ สรุปอย่างชาญฉลาด

ฟังก์ชัน VBA ใน Excel คือบล็อกของโค้ดที่ทำงานบางอย่างและส่งผลลัพธ์กลับไปยังตัวเรียกใช้ หน้านี้ครอบคลุมไวยากรณ์การประกาศฟังก์ชัน การส่งคืนค่า ตัวอย่างการบวก และการใช้ฟังก์ชันภายในเซลล์ในเวิร์กชีต

  • 🎯 ความหมาย: ฟังก์ชันทำหน้าที่เฉพาะอย่างและส่งผลลัพธ์เดียวกลับไปยังโค้ดที่เรียกใช้
  • 🧾 ไวยากรณ์: Function name(arguments) As Type เปิดบล็อก และ End Function ปิดบล็อก
  • ↩️ การส่งคืนค่า: กำหนดผลลัพธ์ให้กับชื่อฟังก์ชัน เช่น addNumbers = ตัวเลขแรก + ตัวเลขที่สอง
  • 🔢 ประเภทการส่งคืน: ประกาศว่ายาวหรือยาวเท่านั้น Double หลีกเลี่ยงตัวเลือกเริ่มต้นที่ทำงานช้ากว่า (Variant)
  • 🖱️ โทรศัพท์: ปุ่มคำสั่งจะส่งค่าตัวเลขสองตัวและแสดงผลรวมที่ได้ในกล่องข้อความ
  • 📊 การใช้งานแบบฝึกหัด: ฟังก์ชันสาธารณะในโมดูลมาตรฐานจะกลายเป็นสูตรที่ผู้ใช้กำหนดเองในเซลล์ใดก็ได้
อ่านเพิ่มเติม

ฟังก์ชัน VBA ใน Excel

ฟังก์ชั่นคืออะไร?

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

สมมติว่าคุณกำลังพัฒนา...ping โปรแกรมที่คำนวณดอกเบี้ยเงินกู้ คุณสามารถสร้างฟังก์ชันที่รับจำนวนเงินกู้และระยะเวลาชำระคืน จากนั้นฟังก์ชันจะใช้จำนวนเงินกู้และระยะเวลาชำระคืนในการคำนวณดอกเบี้ยและส่งคืนค่า

ทำไมต้องใช้ฟังก์ชัน

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

กฎของการตั้งชื่อฟังก์ชัน

กฎการตั้งชื่อก็เหมือนกับกฎการตั้งชื่อซับรูทีนทุกประการ ชื่อฟังก์ชันต้องไม่มีช่องว่าง ต้องขึ้นต้นด้วยตัวอักษรหรือเครื่องหมายขีดล่าง และต้องไม่ใช่คำสงวน VBA คำสำคัญ เช่น Function, Private หรือ End

ไวยากรณ์ VBA สำหรับการประกาศฟังก์ชัน

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

ที่นี่ในไวยากรณ์

Code การกระทำ
  • “ฟังก์ชั่นส่วนตัว myFunction(…)”
  • ที่นี่คีย์เวิร์ด "Function" ใช้เพื่อประกาศฟังก์ชันชื่อ "myFunction" และเริ่มต้นเนื้อหาของฟังก์ชัน
  • คำหลัก 'ส่วนตัว' ใช้เพื่อระบุขอบเขตของฟังก์ชัน
  • “ByVal arg1 เป็นจำนวนเต็ม, ByVal arg2 เป็นจำนวนเต็ม”
  • ประกาศพารามิเตอร์สองตัวของชนิดข้อมูลจำนวนเต็มที่ชื่อว่า 'arg1' และ 'arg2'
  • ฟังก์ชั่นของฉัน = arg1 + arg2
  • ประเมินนิพจน์ arg1 + arg2 และกำหนดผลลัพธ์ให้กับชื่อของฟังก์ชัน
  • “สิ้นสุดฟังก์ชั่น”
  • “End Function” ใช้สำหรับปิดส่วนเนื้อหาของฟังก์ชัน

วิธีการส่งค่ากลับและกำหนดชนิดข้อมูลของฟังก์ชัน

ฟังก์ชันมีหน้าที่อย่างหนึ่งที่ซับรูทีนไม่มี นั่นคือ การส่งค่ากลับ ค่าดังกล่าวมีรายละเอียดอยู่สองอย่างที่มักมองข้ามไป

ประการแรกคือการกำหนดค่า VBA ไม่มีคำสั่ง Return แต่เราจะกำหนดผลลัพธ์ให้กับชื่อของฟังก์ชันเอง ซึ่งเป็นเหตุผลว่าทำไมบรรทัดนั้นจึงเขียนว่า ฟังก์ชั่นของฉัน = arg1 + arg2หากการกำหนดค่าดังกล่าวไม่เคยทำงาน ฟังก์ชันจะส่งคืนค่าว่างโดยไม่แสดงข้อผิดพลาด ดังนั้นทุกส่วนของโค้ดจะต้องกำหนดค่าให้กับฟังก์ชันนี้

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

การประกาศ คืนสินค้า ควรใช้เมื่อใด
ฟังก์ชัน f(x ตราบใดที่) ตัวแปร เฉพาะเมื่อประเภทของผลลัพธ์แตกต่างกันอย่างแท้จริง
ฟังก์ชัน f(x ตราบใดที่) ตราบใดที่ นาน จำนวนเต็ม เช่น จำนวนนับและหมายเลขแถว
ฟังก์ชัน f(x ตราบใดที่) เมื่อ Double Double การคำนวณใดๆ ที่ให้ผลลัพธ์เป็นทศนิยม
ฟังก์ชัน f(x ยาว) เป็นสตริง เชือก ข้อความที่จัดรูปแบบแล้วส่งคืนเพื่อแสดงผล
ฟังก์ชัน f(x ตราบใดที่) เป็นบูลีน บูลีน การตรวจสอบความถูกต้องโดยตอบว่าจริงหรือเท็จ

💡 เคล็ดลับ: ใช้ Exit Function เพื่อออกจากโปรแกรมก่อนกำหนดเมื่อกำหนดค่าส่งคืนแล้ว ในลักษณะเดียวกับที่ Exit Sub ออกจากซับรูทีน

ฟังก์ชั่นแสดงให้เห็นด้วยตัวอย่าง:

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

  1. สร้างส่วนติดต่อผู้ใช้
  2. เพิ่มฟังก์ชั่น
  3. เขียนโค้ดสำหรับปุ่มคำสั่ง
  4. ทดสอบรหัส

ขั้นตอน 1) ส่วนติดต่อผู้ใช้

เพิ่มปุ่มคำสั่งลงในแผ่นงานดังที่แสดงด้านล่าง

ฟังก์ชั่น VBA และรูทีนย่อย

ตั้งค่าคุณสมบัติของ CommandButton1 ดังต่อไปนี้

S / N Control อสังหาริมทรัพย์ ความคุ้มค่า
1 ปุ่มคำสั่ง1 ชื่อ btnเพิ่มNumbers
2 คำบรรยายภาพ เพิ่ม Numbers ฟังก์ชัน

อินเทอร์เฟซของคุณควรปรากฏดังนี้

ฟังก์ชั่น VBA และรูทีนย่อย

บทความที่เกี่ยวข้อง

ขั้นตอน 2) รหัสฟังก์ชั่น

  1. กด Alt + F11 เพื่อเปิดหน้าต่างโค้ด
  2. เพิ่มโค้ดดังต่อไปนี้:
Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

ที่นี่ในรหัส

Code การกระทำ
  • “ฟังก์ชั่นส่วนตัวเพิ่มNumbers(... ) "
  • ประกาศฟังก์ชันส่วนตัว “เพิ่มNumbers” ที่ยอมรับพารามิเตอร์จำนวนเต็มสองตัว
  • “ByVal หมายเลขแรกเป็นจำนวนเต็ม ByVal หมายเลขที่สองเป็นจำนวนเต็ม”
  • ประกาศตัวแปรพารามิเตอร์สองตัวคือ firstNumber และ secondNumber
  • "เพิ่มNumbers = หมายเลขแรก + หมายเลขที่สอง”
  • โดยจะเพิ่มค่า firstNumber และ SecondNumber และกำหนดผลรวมที่จะบวกNumbers.

ขั้นตอนที่ 3) เขียน Code ที่เรียกใช้ฟังก์ชัน

  1. คลิกขวาที่ปุ่ม btnAddNumbers ปุ่มคำสั่ง
  2. เลือกมุมมอง Code
  3. เพิ่มโค้ดดังต่อไปนี้:
Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

ที่นี่ในรหัส

Code การกระทำ
“ข่าวสารเกี่ยวกับBox เพิ่มNumbers(หนึ่ง)"
  • มันเรียกฟังก์ชันเพิ่มNumbers และส่งผ่าน 2 และ 3 เป็นพารามิเตอร์ ฟังก์ชันส่งคืนผลรวมของตัวเลขสองตัวห้า (5)

ขั้นตอน 4) รันโปรแกรมจะได้ผลลัพธ์ดังนี้

ฟังก์ชั่น VBA และรูทีนย่อย

ดาวน์โหลด Excel ที่มีโค้ดด้านบน

ดาวน์โหลดไฟล์ Excel ด้านบน Code

ปุ่มด้านบนเรียกใช้ฟังก์ชันจากโค้ด VBA นอกจากนี้ยังสามารถเรียกใช้ฟังก์ชันจากเวิร์กชีตได้โดยตรง โดยไม่ต้องใช้ปุ่มใดๆ

วิธีใช้ฟังก์ชัน VBA ในเซลล์ของเวิร์กชีต

ฟังก์ชันที่เขียนด้วย VBA สามารถพิมพ์ลงในเซลล์ได้เหมือนกับคำสั่ง SUM หรือ VLOOKUP Excel เรียกฟังก์ชันนี้ว่า ฟังก์ชันที่ผู้ใช้กำหนดเอง หรือ UDF และนี่คือเหตุผลที่หลายคนเรียนรู้ฟังก์ชันก่อนซับรูทีน โดยต้องมีเงื่อนไขสามข้อต่อไปนี้

  • วางไว้ในโมดูลมาตรฐาน: แทรกโมดูลในโปรแกรมแก้ไข ฟังก์ชันที่จัดเก็บไว้หลังเวิร์กชีตหรือใน ThisWorkbook จะไม่ปรากฏในแถบสูตร
  • ประกาศให้เป็นสาธารณะ: ตัวอย่างข้างต้นใช้คำว่า "ส่วนตัว" ซึ่งจะซ่อนคำนั้นจาก Excel ค่าเริ่มต้นคือ "สาธารณะ" ดังนั้นการลบคำหลักออกก็เพียงพอแล้ว
  • ส่งคืนค่า ไม่เปลี่ยนแปลงสิ่งใด: ฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF) ไม่สามารถจัดรูปแบบเซลล์ ลบแถว หรือเขียนข้อมูลลงในเซลล์อื่นได้ Excel จะบล็อกการกระทำเหล่านั้น และเซลล์จะแสดงข้อความ #VALUE!

ฟังก์ชันด้านล่างนี้ใช้สำหรับแปลงค่าอุณหภูมิ และสามารถใช้ได้ทุกที่ในแผ่นงาน

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

บันทึกเวิร์กบุ๊กเป็นไฟล์ .xlsm ที่เปิดใช้งานมาโคร จากนั้นพิมพ์ =CelsiusToF(A1) ป้อนชื่อลงในเซลล์ใดก็ได้ ผลลัพธ์จะอัปเดตทุกครั้งที่มีการเปลี่ยนแปลงในเซลล์ A1 และชื่อจะปรากฏในรายการเติมคำอัตโนมัติของสูตรภายใต้หมวดหมู่ที่ผู้ใช้กำหนดเอง เนื่องจากเวิร์กบุ๊กนี้มีมาโครอยู่แล้ว ผู้ที่เปิดเวิร์กบุ๊กนี้จะต้องเปิดใช้งานเนื้อหาก่อนที่สูตรจะส่งคืนค่าแทนที่จะเป็น #NAME?

ข้อผิดพลาดทั่วไปของฟังก์ชัน VBA และวิธีแก้ไข

มีปัญหาหลักสี่ประการที่ทำให้ฟังก์ชันส่วนใหญ่สามารถคอมไพล์ได้แต่ให้ผลลัพธ์ที่ไม่ถูกต้อง

  • ฟังก์ชันนี้จะส่งคืนค่าว่างหรือ 0: ผลลัพธ์ไม่เคยถูกกำหนดให้กับชื่อฟังก์ชัน หรือเงื่อนไขในคำสั่ง If บางส่วนข้ามการกำหนดค่าไป กำหนดค่าส่งคืนในทุกเส้นทาง
  • #ชื่อ? ในเซลล์ของเวิร์กชีต: ฟังก์ชันนี้เป็นแบบส่วนตัว อยู่ในโมดูลชีตแทนที่จะเป็นโมดูลมาตรฐาน หรือเวิร์กบุ๊กถูกบันทึกโดยไม่ได้เปิดใช้งานมาโคร
  • โอเวอร์โฟลว์เมื่อใช้พารามิเตอร์ประเภทจำนวนเต็ม: ตัวอย่างนี้ใช้ As Integer ซึ่งจะหยุดที่ค่า 32,767 หากต้องการใช้ข้อมูลจริง ให้เปลี่ยนทั้งพารามิเตอร์และชนิดข้อมูลส่งคืนเป็น Long
  • การเปลี่ยนข้อโต้แย้งทำให้ผู้โทรประหลาดใจ: การละเว้น ByVal จะทำให้ VBA ส่งผ่านตัวแปรนั้นโดยตรง ส่งผลให้ฟังก์ชันสามารถเปลี่ยนแปลงค่าของผู้เรียกได้ ควรเขียน ByVal เว้นแต่ว่าต้องการผลลัพธ์ดังกล่าว

คำถามที่พบบ่อย

ไม่โดยตรง ให้ส่งคืนอาร์เรย์หรือประเภทที่กำหนดเองเพื่อเก็บค่าหลายค่าในผลลัพธ์เดียว หรือประกาศพารามิเตอร์เพิ่มเติมแบบ ByRef เพื่อให้ฟังก์ชันเขียนค่ากลับไปยังตัวแปรของผู้เรียกใช้

เพิ่มคีย์เวิร์ด Optional พร้อมค่าเริ่มต้น เช่น Optional ByVal Rate As Double = 0.05 พารามิเตอร์ทุกตัวหลังจากพารามิเตอร์ Optional จะต้องเป็น Optional เช่นกัน และต้องอยู่ท้ายสุดของรายการ

ใช่ครับ สามารถใช้ได้ผ่านทาง Application.WorksheetFunction ตัวอย่างเช่น Application.WorksheetFunction.Sum(Range(“A1:A10”)) ฟังก์ชันที่ VBA มีอยู่แล้ว เช่น Left หรือ Trim สามารถเรียกใช้ได้โดยตรงโดยไม่ต้องมีคำนำหน้าดังกล่าว

ใช่แล้ว วางสูตรในเวิร์กชีต แล้วผู้ช่วย AI จะส่งคืนฟังก์ชันสาธารณะที่เทียบเท่ากัน โดยมีอาร์กิวเมนต์ที่ระบุชื่อและประเภทการส่งคืนที่ประกาศไว้ เปรียบเทียบผลลัพธ์ทั้งสองในแถวตัวอย่างก่อนที่จะแทนที่สูตร

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

สรุปโพสต์นี้ด้วย: