VBA สร้างฟังก์ชัน (function) หรือสูตรขึ้นมาใช้เองใน ไมโครซอฟท์เอ็กเซล

ห่างไปนานมาก... จนตอนนี้ผมอัปเกรดมาใช้ก excel 2013 แล้ว...
แรกสุดที่เคยจับเอ็กเซลคือ Excel 7 ที่มาพร้อมกับ windows 3.1 ...วันเวลาผ่านไปเร็วมาก ...อิอิ
มาเข้าเรื่องกัน....วันนี้มาอัปเดทบทความดีๆอีกสักเรื่อง..

เรื่อง..."การสร้างฟังก์ชัน หรือสูตรขึ้นมาใช้เอง ในไมโครซอฟท์ เอกเซล"

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

แต่ว่าบางที...มันก็ไม่มีสูตรที่เราก็ต้องการก็มีนะ!   หรือว่าเราอยากจะสร้างสูตรที่มีความซับซ้อนมากๆเพื่อลดขั้นตอนในการทำงานของเราเองได้ ...งานนี้ก็จำเป็นจะต้องเขียนมันขึ้นมาเอง...VBA ช่วยท่านได้...

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

สูตรสำหรับ หาพื้นที่วงกลม:    

เมื่อ r คือค่ารัศมีของวงกลม

เราสามารถใส่สูตรเพื่อหาค่าพื้นที่นี้ได้ในเซลได้โดยตรง เช่น ต้องการหาพื้นที่ของวงกลมที่มีรัศมีเท่ากับ 7 หน่วย เราก็ใส่สูตรในเซลล์เป็น  

=pi()*7^2

ก็จะได้คำตอบเป็น 154 

(หมายเหตุ: เอ็กเซลมีค่า ไพน์สำหรับให้เรียกใช้ได้เลยผ่านฟังก์ชัน pi()  โดยที่ไม่ต้องป้อนค่าไพน์โดยตรงเป็น 22/7 หรือ 3.14 ซึ่งทั้งสองค่านี้เป็นเพียงค่าโดยประมาณ สำหรับค่าไพน์ที่เอ็กเซลใช้นั้นจะเป็นตัวเลขที่มีความละเอียดระดับ Double Precision หรือทศนิยม 14  ตำแหน่ง โดยเซตไว้ที่ค่า 3.14159265358979 หากต้องการตรวจสอบดูว่าฟังก์ชัน pi() ให้ค่าเป็นเท่าไหร่ ก็ให้ลองใส่สูตรในเซลเป็น =pi() แล้วเคาะ Enter ก็จะได้ค่าเป็นตัวเลขออกมาเลย แล้วลองเพิ่มจำนวนทศนิยมของเซลนั้นให้มากที่สุดก็จะเห็นค่าในตำแหน่งทศนิยมอื่นๆ)

ทีนี้ลองมาหาพื้นที่วงกลมด้วยการเขียน VBA บ้าง

1. เปิดหน้าต่าง VBA ขึ้นมา (Alt+F11)

2. เพิ่ม module สำหรับเขียน VBA (เมนู Insert > Module)

3. เริ่มเขียน function ของเราเอง

4. ใช้งาน function

สำหรับการเริ่มเขียน function มีรูปแบบดังนี้

1. บรรทัดแรกของ function ให้ขึ้นต้นด้วยคำว่า function เว้นวรรคหนึ่งที แล้วตามด้วยชื่อฟังก์ชัน (ชื่อฟังก์ชันควรตั้งให้สอดคล้องกับการทำงานเพื่อให้สามารถจดจำได้ง่าย นอกจากนี้เราไม่สามารถตั้งชื่อฟังก์ชันที่ขึ้นต้นด้วยตัวเลขหรืออักขระพิเศษได้ และไม่สามารถเว้นวรรคได้ หากต้องการเว้นวรรค นิยมใช้เครื่องหมาย ขีด(ล่าง) หรือ underscore เพื่อเชือมคำให้ต่อกัน และชื่อของฟังก์ชันไม่ควรจะยาวเกินไป) หลังชื่อของฟังก์ชัน ให้เคาะ Enter 1 ที ก็จะปรากฏว่าหลังชื่อของฟังก์ชันจะมีวงเล็บ ( ) เกิดขึ้นเอง และจะมีบรรทัดจบของฟังก์ชันซึ่งเป็นบรรทัดสุดท้ายของฟังก์ชันปรากฏมาให้ด้วย

ตัวอย่าง เช่น

เมื่อพิมพ์คำว่า   

function circle_area  

แล้วเคาะ Enter ก็จะปรากฏเป็น

Function circle_area ( )

End Function

2. คำสั่งต่างๆของฟังก์ชันจะอยู่ระหว่างบรรทัดแรก และบรรทัดสุดท้ายนี้ครับ

3. ภายในวงเล็บหลังชื่อของฟังก์ชัน ให้กำหนดชื่อของตัวแปรที่จะส่งเข้ามาให้ฟังก์ชันคำนวนก่อนให้คำตอบออกไป ในที่นี้คือค่ารัศมีของวงกลมที่จำเป็นต้องใช้ในสูตรข้างต้น ถ้าฟังก์ชันต้องการมากกว่าหนึ่งตัวก็ให้คั่นแต่ละดัวด้วย เครื่องหมายจุลภาค (comma) ตัวอย่าง เช่น

Function abc (x)    

หรือ

Function abc (x, y)

นอกจากนี้ เรายังสามารถกำหนดชนิดของตัวแปรที่ส่งเข้ามาได้ด้วย เพื่อการทำงานของฟังก์ชันที่ถูกต้องมากขึ้น เช่น ให้เป็นจำนวนเต็ม (Integer) หรือ ให้เป็นจำนวนจริงหรือจำนวนที่มีทศนิยมได้ (Double) เช่น

Function abc (x As Integer)

หรือ

Function abc (x As Integer, y As Double)

และ เช่นเดียวกัน เราสามารถกำหนดชนิดของตัวแปรที่เป็นคำตอบของฟังก์ชันเราได้เช่นเดียวกัน เช่น

Function abc (x) As Double

หรือ

Function abc (x As Integer) As Double

หรือ

Function abc (x As Integer, y As Double) As Double

สำหรับการคำนวนของฟังก์ชันทั้งหมด สามารถทำได้ังนี้

Function cir_area (r As Double) As Double

   pi = 3.14159265358979          'กำหนดค่าให้กับไพน์ 

   cir_area = pi * r^2        'คำนวนพื้นที่ของวงกลม , เครื่องหมาย ^ คือการยกกำลัง

End Function

ข้อกำหนดอีกอย่างหนึ่งของการเขียนฟังก์ชันขึ้นมาใช้เอง คือจะต้องมีการกำหนดค่าให้ตัวแปรที่มีชื่อเดียวกันกับชื่อของฟังก์ชัน เพื่อให้ VBA นำค่าของตัวแปรนั้นซึ่งเป็นคำตอบส่งค่าออกไปเมื่อมีการเรียกใช้ฟังก์ชั้นนั้น (ในตัวอย่างข้างบน บรรทัดที่สามมีการกำหนดค่าให้ตัวแปรที่ชื่อ cir_area ซึ่งต้องเป็นชื่อเดียวกับชื่อฟังก์ชัน โดยใช้สูตรที่มีค่ารัศมีและค่าไพน์ มาคำนวณ

ทีนี้ลองตรวจสอบค่าของพื้นที่วงกลมโดยใช้สูตรในเซลของเอ็กเซลเป็น

=cir_area(7)  

ค่าที่ส่งมาคือ 7 จะกลายเป็นค่ารัศมีในรหัสคำสั่ง ก็จะได้คำตอบที่ได้ออกมาเป็น  154  ซึ่งตรงกับการใส่สูตรโดยตรง แสดงว่าถูกต้อง

ตัวอย่างที่ 2  จงเขียนฟังก์ชันสำหรับการคำนวณพื้นที่สามเหลี่ยมโดยใช้ VBA

สูตรการหาพื้นที่สามเหลี่ยม:  

Function tri_area (b As Double, h As Double) As Double 

   tri_area = 0.5 * b * h        'คำนวนพื้นที่ของสามเหลี่ยม

End Function

ลองตรวจสอบค่าของฟังก์ชันโดยพิมพ์สูตรต่อไปนี้

=tri_area (2, 4)

ซึ่ง 2 ก็คือตัวแปรแรก และ 4 คือตัวแปรที่สองที่ส่งให้ฟังก์ชัน จะได้เป็น b=2 และ h=4 ตามลำดับ และก็จะได้คำตอบออกมาเป็น 4 ซึ่งถูกต้อง [  (1/2)*2*4 = 4 ]

ทีนี้ก็ save ไฟล์ไว้ใช้งานต่อไป ฟังก์ชันก์นี้ก็จะอยู่ในไฟล์นี้ตลอดไปหากไม่ลบออกก่อน save

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

Tip: 

1. ในตอนเรียกใช้ฟังก์ชัน เราจะต้องใส่เครื่องหมายเท่ากับ ("=") ก่อนสูตรเสมอ
2. เมื่อเราพิมพ์ชื่อฟังก์ชันไปแล้วส่วนหนึ่ง จะมีรายการฟังก์ชันที่ชื่อใกล้เคียงกันขึ้นมาให้เราเลือกใช้ ถ้าท่านมองเห็นชื่อของฟังก์ชันนั้น ก็ใช้มือขว่ากดปุ่มลูกศร ขึ้น-ลง ให้ตรงกับชื่อนั้น แล้วใช้มือซ้ายกดปุ่ม Tab ก็จะทำให้ชื่อฟังก์ชันสมบูรณ์เอง ประหยัดเวลาในการพิมพ์ชื่อ โดยเฉพาะกรณีชื่อฟังก์ชันยาวๆ และแก้ปัญหาพิมพ์ชื่อฟังก์ชันผิดด้วย
3. ในการเรียกใช้ฟังก์ชันนั้น แทนที่เราจะใส่เป็นตัวเลขส่งไปยังฟังก์ชันโดยตรง เรายังสามารถลิงค์ค่าในเซลล์อื่นไปแทนได้ด้วยเหมือนสูตรที่มีมากับเอ็กเซลทั่วไป ซึ่งถ้าค่าในเซลล์ที่ลิงค์มาเปลี่ยนค่า ค่าในเซลล์ที่ใช้สูตรที่เราเขียนก็จะเปลี่ยนค่าตามการคำนวรในฟังก์ชันที่เขียนขึ้นเองอัตโนมัติ เพิ่มความสะดวกในการทำเวิร์กชีตยิ่งขึ้นไปอีก

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

ขอให้สนุกกับ VBA  

:D