VBA Excel ตอน User Function (Excel Add-Ins)

สวัสดีท่านผู้อ่าน How to Excel ในวันสิ้นเดือนสิงหาคมนะครับ บทความนี้ขอเล่าแบบสบายๆให้เข้าบรรยากาศเงินเดือนออกซะหน่อยครับ จากบทความ Excel Series ของการจัดการ Date Data ผมได้นำเสนอวิธีการสร้างฟังก์ชันขึ้นมาใช้เองด้วยการใช้ VBA และได้แสดงวิธีการเรียกฟังก์ชันมาใช้ใน Excel ไปแล้ว โดยได้ทิ้งท้ายไว้ในเรื่องของการนำฟังก์ชันที่ได้พัฒนาแล้วให้สามารถเรียกใช้จากไฟล์ Excel อื่น ดังนั้นบทความนี้จึงขอนำเสนอวิธีการดังกล่าวครับ

เริ่มจากให้ท่านผู้อ่านสร้างไฟล์ Excel ป่าวๆมาซักหนึ่งไฟล์ครับ เอาเป็นว่าผมจะขอนำเสนอฟังก์ชันที่เปลี่ยนข้อมูลจาก Text Format เป็นข้อมูลในรูปแบบ Time Format หล่ะกันนะครับ โดยรายละเอียดเกี่ยวกับ Time Format ขอให้ท่านผู้อ่านไปติดตามได้ในบทความ Excel Series เรื่องการจัดการ Time Data

มาดูปัญหากันก่อนครับ โดยปกติผมมักจะเจอข้อมูลการรูดบัตรจากเครื่องรูดบัตรในลักษณะนี้ครับ HM เช่น 0805 เป็นต้น ทีนี้รูปแบบของ Time Data ใน excel จะเป็นดังนี้ครับ H:M ฉะนั้นหากเราต้องการนำข้อมูลที่อ่านได้จากเครื่องรูดบัตรส่งให้ Excel คำนวณต่อ เช่น เวลาทำงาน ค่าโอที หรือคำนวณประสิทธิภาพเครื่องจักร ก็ต้องแปลงข้อมูลให้อยู่ในรูปแบบที่ Excel รู้จักกันก่อน มาเริ่มกันเลยครับ จากปัญหาที่พบไม่อยากเลยใช่มั้ยครับ แค่แยกข้อมูลของจำนวนชั่วโมงและจำนวนนาทีจากข้อมูลที่ถูกส่งผ่านมา และใช้ฟังก์ชัน Date ของ VBA ทำการแปลงและคืนค่ากลับเป็นข้อมูลแบบ Time ครับ ดังนั้นข้อมูลที่ถูกส่งผ่านฟังก์ชันของเราก็คือข้อมูลของเวลาในรูปแบบ text นั่นเอง ผมขอตั้งชื่อ User Function ของผมเป็น TextToTime โดยรับค่าข้อมูลเวลาผ่านตัวแปร text_time เอาหล่ะครับมาสร้างTime Data ใน Excel กันเลย โดยการกด Alt+F11 จะปรากฎ VB EDITOR ครับให้ไปที่เมนู Insert แล้วเลือก Module ในหน้าต่าง Editor จะปรากฎส่วนที่เขียนฟังก์ชันให้เขียนโค้ดลงไปดังนี้ครับ

ภาพที่ 1 Excel User Function

ตอนนี้เราก็ได้ฟังก์ชัน TextToTime ไว้ใช้งานแล้วครับ มาลองใช้งานใน Excelกันเลยครับดังแสดงในภาพที่ 2

ภาพที่ 2 ผลการคำนวณใน Excel ผ่านการเรียกใช้ User Function

เอาหล่ะครับ ที่นี้หากเราต้องการนำฟังก์ชันนี้ไปใช้งานในไฟล์ Excel อื่นๆต้องทำอย่างไร ตามมาเลยครับ เริ่มต้นให้ท่านลบข้อมูลใน worksheet ทั้งหมดก่อนครับ จากนั้นทำการบันทึกไฟล์ Excel นี้ ในตัวอย่างนี้ผมขอตั้งชื่อไฟล์เป็น MyFunction โดยเลือกประเภทของไฟล์เป็น Microsoft Office Excel Add-In ครับ เอาหล่ะครับตอนนี้เรามีไฟล์ Excel ประเภท Add-In ที่เราสร้างขึ้นมาแล้ว เรามาดูกันครับว่าจะเรียกใช้ได้อย่างไร

เริ่มจากสร้างไฟล์ Excel ใหม่ขึ้นมาไฟล์หนึ่งครับ หากต้องการเรียกใช้ฟังก์ชัน TextToTime ซึ่งได้สร้างเตรียมไว้ ในมีไฟล์ Excel ประเภท Add-In ให้ไปที่ เมนู เครื่องมือ เลือก Add-Ins… จะปรากฎหน้าต่าง Add-In ท่านจะเห็นว่ายังไม่มี Add-In ที่เราสร้างไว้ปรากฏอยู่ในรายการ ให้คลิกปุ่ม เรียกดู… จะปรากฏหน้าต่างค้นหาไฟล์ Excel Add-In ให้เราเลือกไฟล์ excel Add-Ins ที่ได้สร้างขึ้น และคลิกปุ่มตกลง จะกลับมาที่หน้าต่าง Add-In ในรายการ Add-In จะปรากฏรายการ Add-In ชื่อ MyFunction ให้คลิกเลือก และคลิกปุ่มตกลงเพื่อออกจากหน้าต่าง Add-In ตอนนีไฟล์ Excel ของเราก้สามารถเรียกใช้งานฟังก์ที่อยู่ในไฟล์ Add-In ชื่อ MyFunction ได้แล้ว แน่นอนว่าตอนนี้มีเพียงแค่ฟังก์ชัน TextToTime เพียงฟังก์ชันเดียว

จากที่ได้อธิบายมาท่านผู้อ่านสามารถเพิ่มเติม User Function ได้อีกมากมายในไฟล์ Excel Add-In เท่านี้ก็ทำให้การใช้งาน Excel ของเราก็ยืดหยุ่นมากขึ้นแล้วครับแล้วพบกันในบทความถัดไปนะครับ สวัสดีครับ

VBA Excel ตอน เขียน vba แก้ปัญหาสูตร Datedif (จบ)

มาถึงตอนจบของ VBA Excel Series ของการแก้ปัญหา Datedif กันแล้วนะครับ ก่อนไปว่ากันในเนื้อหาของบทความตอนจบ ขอเล่านอกเรื่องซักหน่อยครับ วันที่เขียนบทความนี้ (29/8/2010) มีโอกาสไปดูหนังไทยเรื่องหนึ่ง เงา ครับ ไม่อยากบอกว่าหนังไทยเรื่องนี้เป็นหนังไทยที่ทำให้ผมผิดหวังอีกครั้งหนึ่ง ด้วยเนื้อหาที่พยายามจะป้อนให้ผู้ชมในเวลา 90 นาที กับเรื่องสั้นจำนวนสี่เรื่องที่ผมเองมองว่ามากเกินไป ทำให้ผมรู้สึกว่าถูกบีบบังคับให้ดูเรื่องย่อจริงๆ เห้อ ว่าแล้วขอไว้อาลัยกับหนังลักษณะนี้เลยครับ เทียบกับ 5 แพร่ง 4 แพร่งซึ่งมีเรื่องย่อยจำนวนพอกัน เทียบไม่ได้เลย ขอบ่นกันนิดหนึ่งครับ มาเข้าเรื่อง VBA Excel ของเรากันดีกว่าครับ จากที่ได้แสดงแนวการคิดและการใช้สูตรในชุด Date ของ Excel เข้ามาประยุกต์ใช้เพื่อหาค่า Year Diff , Mont Diff และ Day Diff ซึ่งจะพบว่ามีความยุ่งยากพอสมควรในการใช้งานกับ Excel ในบทความนี้เรามาเขียนฟังก์ชันสำหรับการคำนวณดังกล่าวด้วยเทคนิค VBA ของ Excel กันครับ มาดูกันเลยครับ เริ่มจาก กด Alt + F11 เพื่อเข้าสู่หน้าต่างของ Microsoft Visual Basic ในหน้าต่าง Editor ให้สร้างฟังก์ชันชื่อ MyDateDiff โดยมีรูปแบบดังนี้

Function MyDateDiff(Start_Date as Date,End_Date As Date , form as String) As integer

End Function

โดยมีคัวแปรที่ถูกส่งผ่านดังนี้

Start_Date คือวันเริ่มต้น เป็นตัวแปรชนิด Date

End_Date คือวันสิ้นสุด เป็นตัวแปรชนิด Date

form เป็น รูปแบบที่ต้องการให้ฟังก์ชันคำนวณ โดยในการสร้างฟังก์ชันนี้ผมขอยกตัวอย่างการคำนวณ Year Diff (ผลต่างจำนวนปี), Month Diff(จำนวนเดือนหลังคิดจำนวนปีแล้ว และ Day Diff (จำนวนวันหลังคิดผลของปีและเดือนแล้ว) โดยใช้รูปแบบเป็น “Y” , “MY” , “DY”

เขียน VBA Code ในฟังก์ชัน MyDateDiff ตามวิธีการของบทความที่ผ่านมาดังนี้

Option Explicit

Function MyDateDiff(Start_Date As Date, End_Date As Date, format As String) As Integer

Dim YDiff As Integer

Dim MDiff As Integer

Dim DDiff As Integer

Dim temp As Date

format = UCase(format) 'converted to uppercase

'switch Start_Date & End_Date

If Start_Date > End_Date Then

temp = Start_Date

Start_Date = End_Date

End_Date = temp

End If

' Year Diff

YDiff = Year(End_Date) - Year(Start_Date)

If DateSerial(Year(End_Date), Month(Start_Date), Day(Start_Date)) > End_Date Then

YDiff = YDiff - 1

End If

If format = "Y" Then

MyDateDiff = YDiff

End If

'Month Diff

If Month(End_Date) > Month(Start_Date) Then

If Day(End_Date) >= Day(Start_Date) Then

MDiff = Month(End_Date) - Month(Start_Date)

Else

MDiff = Month(End_Date) - Month(Start_Date) - 1

End If

Else

If Day(End_Date) >= Day(Start_Date) Then

MDiff = Month(End_Date) - Month(Start_Date) + 12

If MDiff = 12 Then

MDiff = 0

End If

Else

MDiff = Month(End_Date) - Month(Start_Date) + 11

End If

End If

If format = "MY" Then

MyDateDiff = MDiff

End If

'Day Diff

If Day(End_Date) >= Day(Start_Date) Then

DDiff = Day(End_Date) - Day(Start_Date)

Else

DDiff = Day(DateSerial(Year(End_Date), Month(End_Date), 0)) - Day(Start_Date) + Day(End_Date)

End If

If format = "DY" Then

MyDateDiff = DDiff

End If

End Function

จากฟังก์ชัน MyDateDiff ที่เขียนขึ้นใน VBA ท่านผู้อ่านสามารถเรียกใช้ได้ใน Worksheets ของ Excel โดยสามารถพิมพ์สูตรนี้ได้ในเซลล์ดังแสดงในภาพที่ 1

ภาพที่ 1 ผลการคำนวณของ Excel จากการใช้ฟังก์ชัน MyDateDiff

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

จากการนำเสนอในบทความนี้นอกจากกการคำนวณเกี่ยวกับวันที่แล้วยังทำให้เราเห็นว่าเราสามารถสร้างฟังก์ชันการคำนวณขึ้นมาเอง (User define function) ด้วย VBA และสามารถเรียกใช้ฟังก์ชันนี้ผ่าน Excel ได้อย่างง่าย แต่ในขณะเดียวกันหากเราต้องการนำฟังก์ชันนี้ไปใช้กับไฟล์ Excel อื่นจะทำอย่างไร เดี๋ยวผมจะนำเสนอในบทความต่อไปหล่ะกันนะครับ เกี๋ยวกับการสร้างไฟล์ Add-In ใน Excel สวัสดีครับ