วิธีสร้างสูตร Function VBA - Addin เพื่อนำมาใช้ในงานเฉพาะด้าน

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

ถ้าต้องการย่อสูตรยากๆยาวๆให้สั้นลง โดยหาทางใช้ VBA สร้างสูตรขึ้นมาใช้ จะมีแนวทางการใช้สูตรที่สร้างด้วย VBA (Function VBA หรือ User Defined Function - UDF) 2 แบบ คือ

1. สร้างสูตรเพื่อใช้กับแฟ้มใดแฟ้มหนึ่งโดยเฉพาะ
2. สร้างสูตรเพื่อใช้กับแฟ้มใดก็ได้ (Add-in)

หมายเหตุ แทนที่จะคิดใช้ Function VBA ขอให้พยายามใช้สูตรสำเร็จรูปของ Excel หรือนำสูตรสำเร็จรูปมาใช้คำนวณร่วมกัน เพื่อคำนวณหาคำตอบที่ต้องการให้ได้ก่อน เพราะสูตรสำเร็จรูปจะคำนวณเร็วกว่ามาก

Function VBA มิได้สร้างใน Sub Procedure แต่ให้สร้างไว้ใน Function Procedure ซึ่งมีโครงสร้างของชุดคำสั่งที่เป็นสูตร ดังนี้

Function ชื่อสูตร(ชื่อตัวแปร1,ชื่อตัวแปร2,,,,ชื่อตัวแปรn)
    รหัสที่ใช้ในการคำนวณ
End Function

วิธีสร้างสูตรเพื่อใช้กับแฟ้มใดแฟ้มหนึ่งโดยเฉพาะ

1. ให้เปิด VBE ขึ้นมาแล้ว Insert > Module
2. พิมพ์คำว่า function ตามด้วยชื่อสูตร และวงเล็บของชื่อตัวแปร
3. กดปุ่ม Enter จะพบคำว่า End Function พิมพ์เป็นบรรทัดสุดท้ายให้เอง
4. ให้พิมพ์รหัสที่ต้องการใช้คำนวณลงไปในบรรทัดระหว่าง Function...End Function

ตัวอย่างที่ 1 : สูตรที่จะแสดงสูตรของเซลล์ที่ต้องการ

Function FML(cell)
    FML = cell.Formula
End Function

เมื่อต้องการใช้สูตรในตาราง เช่น ต้องการแสดงสูตรจากเซลล์ A1 ให้พิมพ์ =FML(A1)

ตัวอย่างที่ 2 : สูตรที่ใช้ตรวจสอบว่ามีสูตรในเซลล์นั้นๆหรือไม่

Function HasFML(cell)
    HasFML = cell.HasFormula
End Function

เมื่อต้องการใช้สูตรในตาราง เช่น ต้องการตรวจสอบว่าเซลล์ A1 มีสูตรสร้างไว้หรือไม่ ให้พิมพ์ =HasFML(A1) จะได้คำตอบเป็น True/False

ตัวอย่างที่ 3 : สูตรหายอดรวมเฉพาะเซลล์ที่เป็นตัว Bold

Function BoldSum(rngCells)
    BoldSum = 0
    For Each c In rngCells
       If c.Font.Bold Then BoldSum = BoldSum + c.Value
    Next c
End Function

เมื่อต้องการหายอดรวมของตัวเลขในตาราง A1:B10 โดยให้รวมเลขจากเซลล์ที่ใช้ตัวเข้มหนาเท่านั้น ให้สร้างสูตร =BoldSum(A1:B10)*Now()/Now()

สาเหตุที่ต้องนำ Now()/Now() คูณเข้าไป เพื่อทำให้สูตรนี้คำนวณใหม่ทุกครั้งเมื่อเรากดปุ่ม F9

วิธีสร้างสูตรเพื่อใช้กับแฟ้มใดก็ได้ (Add-in)

Add-in มิได้มีรหัสอื่นใดที่แตกต่างจากตัวอย่างข้างต้น เพียงแค่สั่ง save แฟ้มโดยเลือก Save as type เป็น Microsoft Office Excel Add-In (*.xla) จะได้แฟ้มใหม่ในชื่อเดียวกับแฟ้มเดิม โดยแฟ้มใหม่ที่เกิดขึ้นนี้มีนามสกุล xla

ก่อนที่จะ save เป็น xla ควรปรับปรุงแฟ้มในส่วนต่อไปนี้

• เนื่องจากแฟ้ม xla ที่จะนำมาใช้งานต่อไปนั้น ใช้เป็นแฟ้มที่นำสูตร Function VBA ที่เราสร้างขึ้นมาใช้งาน โดยไม่ได้ใช้เนื้อที่ชีทแสดงขึ้นบนจอ ดังนั้นเพื่อทำให้ประหยัดหน่วยความจำ ควรลบชีททิ้งให้หมดจนเหลือเพียงชีทเดียว เพื่อทำให้แฟ้ม xla มีขนาดเล็กที่สุดเท่าที่จะทำได้
• ควรทำคำอธิบายประกอบสูตรแต่ละสูตร โดยสั่ง Developer > Macros ซึ่งจะไม่พบชื่อ Macro ที่เป็น Function VBA แสดงไว้ ให้พิมพ์ชื่อสูตรตามที่ตั้งชื่อไว้ลงไปในช่อง Macro name จะพบว่าปุ่ม Options แสดงขึ้น ให้คลิกเข้าไปแล้วพิมพ์คำอธิบายสูตรลงในช่อง Description (คำอธิบายสูตรนี้จะแสดงประกอบตัวสูตร เมื่อใช้คำสั่ง Formulas > Insert Function จะพบสูตรแสดงไว้ในส่วนของ User Defined)
• ควรใช้ตารางในชีทเดียวที่เหลืออยู่ สร้างตัวอย่างวิธีการใช้สูตร และบันทึกชื่อผู้สร้างไว้ด้วย แล้วสั่ง Protect Sheet
• ควรใช้คำสั่ง File > Info > Properties บันทึกรายละเอียดโดยย่อสั้นๆของแฟ้มสูตรลงในช่อง Title และบันทึกรายละเอียดเพิ่มเติม โดยเฉพาะข้อมูลเรื่องลิขสิทธิ์ ลงในช่อง Comments (เมื่อนำ Add-in มาใช้งาน จะพบข้อความที่บันทึกใน Properties นี้แสดงให้เห็นในช่อง Add-Ins Available แทนที่จะแสดงแค่ชื่อแฟ้มให้เห็นเท่านั้น)
• ควรสั่ง Protect Workbook เพื่อป้องกันไม่ให้แก้ไข Properties ที่บันทึกรายละเอียดไว้
• ในส่วนของรหัสที่สร้างไว้ใน VBE ควรสั่ง Tools > VBAProject Properties > Protection แล้วกาช่อง Lock project for viewing (เพื่อกันไม่ให้เห็นโครงสร้างภายในแฟ้มว่ามีชีทอย่างไร) และใส่รหัสป้องกันไว้ด้วย