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

การสั่งพิมพ์ เอกสาร MS Excel จำนวนมากด้วยชุดคำสั่ง Excel VBA -- Auto print

ทำโปรแกรม Auto Print ง่ายๆใน MS Excel ด้วย VBA

ถ้าคุณเป็นคนหนึ่งที่ต้องทำงานเกี่ยวกับเอกสารโดยใช้ MS Excel เป็นหลัก และต้องมีการพิมพ์เอกสารจำนวนมหาศาลไม่รู้ตั้งกี่หน้าที่อยู่ในหนึ่งไฟล์ แล้วยิ่งคุณมีจำนวณไฟล์ที่ต้องพิมพ์เยอะแยะด้วยแล้วก็ คงไม่สนุกนักกับการต้องมาเปิด sheet แต่ละหน้าแล้วสั่งพิมพ์ ไปจนครบทุกแผ่น

วันนี้ผมมีทีเด็ด มาฝาก เกี่ยวกับการทำ Auto Print ใน MS Excel โดยเพียงแค่คุณเลือกว่าจะพิมพ์ไฟล์ใหนบ้าง เจ้าโปรแกรม Auto Print ก็จะพิมพ์ให้คุณครบทุกหน้า ครบทุกไฟล์ เลยทีเดียว ทีนี้คุณก็มีเวลาไปนั่งจิบกาแฟสบายๆ แล้วล่ะ ^^

เริ่มเลยนะครับ ผมใช้เครื่องมือที่สุดยอดมากที่มีมากลับ MS Excel อยู่แล้วนั่นก็คือ VBA (มาจากคำว่า Visual Basic for Application) คุณสามารถเข้าไปดูได้ว่าหน้าตาของเจ้า VBA นี้เป็นอย่างไรเพียงเปิดโปรแกรม MS Excel ขึ้นมา หลังจากนั้นก็กดปุ๋ม Alt+F11 (กดปุ่ม Altinate พร้อมกับ ปุ่ม F11) เท่านี้ก็จะมีหน้าต่างของ VBA ขึ้นมา หน้าตาก็จะแตกต่างกันไปตามเวอร์ชันของ MS Excel นะครับ

ไม่ว่าหน้าตาขอ VBA จะเป็นอย่างไรก็ตาม เพียงแค่คุณทำตามขั้นตอนต่อไปนี้คุณก็จะได้ Auto Print แล้ว

เปิดโปรแกรม MS Excel ขึ้นมา แล้ว กดคีย์ Alt+F11 เพื่่อเข้าสู่หน้าต่างของVBA
ในหน้าต่าง VBA ให้มองหาเมนูบนแถบเมนูบนสุดที่ชื่อว่า Insert (หรือ "แทรก" ในภาษาไทย) แล้วเลือกเมนูย่อย Module (โมดูล) ที่อยู่ในนั้นอีกที แล้วจะมีหน้าต่างอีกอันหนึ่งเปิดขึ้นมา
ให้ทำการ copy คำสั่งด้านล่างนี้ (ที่อยู่ระหว่างเส้นประ)ไปวางในหน้าต่างของ Module แล้วบันทึกไฟล์
Run หรือ เรียกใช้งานโปรแกรม โดยไปที่เมนู Run > Run Sub/Userform (หรืออาจใช้วิธีกดปุ่ม F5) แล้วจะมีอีกหน้าต่างหนึ่งเปิดขึ้นมาเพื่อให้เราเลือกไฟล์ที่จะพิมพ์ทั้งหมด เทคนิคในการเลือกหลายไฟล์นะครับ ก็กดปุ่ม Ctrl ค้างไว้ แล้วก็คลิ๊กเลือกไฟล์ที่ต้องการพิมพ์ไปเรื่อยๆจนครบ แล้วปล่อยปุ่ม Ctrl หลังจากนั้นก็ คลิกที่ปุ่ม Open File หรือเปิด (หรืออาจกดปุ่ม Enter แทนก็ได้) เท่านี้เครื่องพิมพ์ก็จะเริ่มทำงานแทนคุณแล้ว
หมายเหตุ: โปรแกรมนี้มันจะพิมพ์ทุกอย่างที่มีอยู่ในหน้าที่มีข้อมูลนะครับ ถ้าไม่มีข้อมูลไม่พิมพ์ ดังนั้น ก่อนสั่งพิมพ์ควรมีการจัดหน้าไว้ให้เรียบร้อยหมดก่อน ไม่งั้นโปรแกรมจะพิมพ์ทุกอย่างออกมาจริงๆครับ

ขอให้สนุกกับ Auto Print ใน MS Excel โดย VBA นะครับ^^
--------------------------------------------------------------------------------------------------------

Sub auto_print()
'
'This macro was written by Aj OP (http://thaiexcelvba.blogspot.com) .
'
Dim i As Integer, j As Integer
Dim varFile As Variant
Dim inFile As Integer
Dim OPENFILE
Dim currFile
Dim N
'
Application.ScreenUpdating = False
On Error GoTo 100
'
currFile = ActiveWorkbook.Name
varFile = _
Application.GetOpenFilename(FileFilter:="Microsoft Excel file, *.xlsx", MultiSelect:=True)
If (IsArray(varFile)) Then
For intfile = 1 To UBound(varFile)
Workbooks.OpenText Filename:=varFile(intfile)
OPENFILE = ActiveWorkbook.Name
'
For N = 1 To ActiveWorkbook.Sheets.Count
Sheets(N).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next N
Workbooks(OPENFILE).Close
Next intfile
Else
MsgBox "You did not select any file.", vbOKOnly + vbInformation
End If

100 End Sub