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

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 Offcice Excel Add-In (*.xla) จะได้แฟ้มใหม่ในชื่อเดียวกับแฟ้มเดิม โดยแฟ้มใหม่ที่เกิดขึ้นนี้มีนามสกุล xla

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

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

วิธีติดตั้งและข้อควรระวังในการใช้ Add-in

1. copy แฟ้ม Add-in ที่ต้องการไปเก็บไว้ใน folder ใดก็ได้
2. เปิด Excel แล้วสั่ง Tools > Add-ins > Browse หาแฟ้ม xla ที่เก็บไว้ จะพบว่ามีชื่อ Add-in ที่ต้องการถูกกาเครื่องหมายถูกไว้
3. เปิดแฟ้มที่มีสูตร Add-in สร้างไว้ จะพบว่าสูตรทำงานตามต้องการ (แต่ถ้าเปิดแฟ้มไว้ก่อนที่จะ Browse ในข้อ 2 จะพบว่าสูตร error ต้องเข้าไปกด F2 ที่เซลล์สูตรแล้วกด Enter เพื่อกระตุ้นให้สูตรทำงาน)
4. ถ้าในแฟ้ม ไม่ได้ใช้สูตร Add-in ควรเลิกใช้ Add-in เพื่อทำให้ไม่เปลืองหน่วยความจำ โดยสั่ง Tools > Add-ins > ตัดกาเครื่องหมายถูกทิ้ง ต่อเมื่อต้องการใช้ Add-in จึงกลับมากาถูกด้านหน้าเฉพาะ Add-in ที่ต้องการใช้งาน
5. ถ้าจำโครงสร้างสูตรไม่ได้ ให้ดูได้จาก Insert > Function แล้วเลือกสูตรแบบ User defined พอคลิกชื่อสูตรจะพบคำอธิบายแสดงไว้ด้านล่างสุด