[VBA] การคำนวณหาเวลาการเข้ามาทำงานของพนักงาน (Time Attendance)
http://www.g2gnet.com/webboard/images/vba/ImportTextFile.pngโค้ดนี้นอกจากจะเป็นการนำเข้าข้อมูล (Import) จาก Text File ให้มาปรากฏอยู่ในเวิร์คชีตของ MS Excel ได้แล้วแอดมินยังใช้ข้อมูลการเข้ามาทำงานของพนักงานจากเครื่องอ่านลายนิ้วมือ (Finger Scan) พร้อมกับแทรกฟังค์ชั่นของการคำนวณหาค่าความต่างของเวลาเอาไว้ให้ด้วย เพื่อจะได้รู้ว่าพนักงานคนนั้นมาเร็ว มาช้ากว่าเวลาเข้างานปกติ 08:00 ไปกี่นาที สาระสำคัญของบทความนี้ที่อยากจะให้เห็นและเข้าใจมากที่สุดก็คือ ประโยชน์จากการใช้งาน Visual Basic for Application หรือ VBA สำหรับงานเล็กๆน้อยๆภายในสำนักงานด้วยครับ ...
http://www.g2gnet.com/webboard/images/vba/ImportTextFileVBA.png
Visual Basic for Application หรือ VBA ลักษณะโค้ดจะเหมือนกับ Visual Basic 6 แต่ไม่ใช่ทั้งหมด
http://www.g2gnet.com/webboard/images/vba/ImportTextFileData.png
ตัวอย่างข้อมูลจาก Text File
http://www.g2gnet.com/webboard/images/vba/ImportTextFileSample.png
การ Browse เพื่อเลือกไฟล์ จะอาศัย Common Dialog Control เหมือนกับใน VB6 เข้ามาช่วย
http://www.g2gnet.com/webboard/images/vba/ImportTextFileDesign.png
Design Time ...
มาดูโค้ดในส่วนที่สำคัญ ... การเลือกเท็กซ์ไฟล์ซึ่งเป็นข้อมูลเข้ามา
' / -----------------------------------------------------------------
' / เลือกไฟล์ที่ต้องการ
Sub OpenDialogFile()
' / -----------------------------------------------------------------
On Error GoTo HandleError
' ตั้งค่าการเปิดไฟล์ด้วย MS Common Dialog Control
With cdl
' กำหนดเฉพาะให้เลือกเฉพาะนามสกุล txt
.Filter = "Text files (*.txt)|*.txt"
.CancelError = True
.DialogTitle = "เลือกไฟล์เอกสาร (Text File) ที่ต้องการ"
' กำหนดโฟลเดอร์เริ่มต้นในการเลือกไฟล์
.InitDir = ThisWorkbook.Path
' แสดง Dialog
.ShowOpen
End With
' ส่งค่าชื่อไฟล์+ตำแหน่งไฟล์ให้กับ FName
FName = cdl.Filename
ExitSub:
Exit Sub
' Trap Error ที่เราอาจจะไม่คาดคิด
HandleError:
Select Case Err.Number
' กด Escape เพื่อยกเลิกการเลือกไฟล์
Case cdlCancel
' Cancelled!
Resume ExitSub
Case Else
MsgBox "Error: " & Err.Description & "(" & Err.Number & ")"
End Select
End Sub
โค้ดในส่วนของการโหลดข้อมูลจาก Text File เข้าสู่ WorkSheet ของ Excel
' / -----------------------------------------------------------------
' / นำข้อมูลจาก Text File เข้ามายัง MS Excel
Sub ImportData()
' / -----------------------------------------------------------------
' หากไม่มีการเลือกไฟล์ใดๆเข้ามาเลย ก็ออกจากโปรแกรมย่อย
If FName = "" Then Exit Sub
' ปิดไฟล์เดิมก่อน
Close #1
Open FName For Input As #1
' ตัวแปรรับค่าข้อมูล Text File เข้ามาทีละบรรทัด
Dim strData As String
' แยกข้อมูลออกจากกันด้วย Array
Dim iArr() As String
' นับจำนวนแถว
Dim i As Long
i = 1
' อ่านไฟล์เข้ามา และทำการอ่านทีละบรรทัด ทำจนกว่าจะหมดข้อมูล (EOF - End Of File)
' การใช้ลูป Do While หรือ Do Until จะได้ไม่ต้องนับจำนวนแถวเข้ามาก่อน
Do While (Not EOF(1))
' อ่านเข้ามาทีละบรรทัด
Line Input #1, strData
' แยกข้อมูลออกจากกันด้วยเครื่องหมายช่องว่าง - Space
iArr = Split(strData, " ")
' เนื่องจาก i เริ่มจาก 1 แต่เวลานำข้อมูลเข้าไปเก็บจะเริ่มต้นจากแถวที่ 3
' ดังนั้นต้องให้ i + 2 = 3 แต่ i ไม่ได้เปลี่ยนค่าน่ะครับ
Cells(i + 2, 1) = i
' แสดงรหัสพนักงาน
Cells(i + 2, 2) = iArr(0)
' แสดงวันที่
Cells(i + 2, 3) = iArr(1)
' แสดงเวลา
Cells(i + 2, 4) = iArr(2)
' คำนวณเวลาความต่างว่ามาช้ามาเร็วกี่นาที โดยเทียบกับเวลา 8.00 น.
Cells(i + 2, 5) = CalTime("08:00:00", Format(iArr(2), "HH:MM:SS"))
' หากมาสายเกิน 8.00 น. ให้แสดงตัวอักษรสีแดง
If Cells(i + 2, 5) > 0 Then
Cells(i + 2, 5).Font.Color = RGB(255, 0, 0)
' หากปกติก็ให้เป็นอักษรสีดำ
Else
Cells(i + 2, 5).Font.Color = RGB(0, 0, 0)
End If
' เรียกแถวใหม่
i = i + 1
Loop
' ปิดไฟล์
Close #1
End Subการแยกข้อมูลออกจากกันในแต่ละหลัก จะใช้เครื่องหมายช่องว่าง (Space) หรือ ASCII Code = 32 (ฐาน 10)
ฟังค์ชั่นในการคำนวณหาความแตกต่างกันของเวลา ... คัดลอกมาจาก Visual Basic 6 ...
' / -----------------------------------------------------------------
' / ฟังค์ชั่นคำนวณหาค่าความต่างเวลาเป็นนาที
Public Function CalTime(StartTime As Date, EndTime As Date) As Integer
' / -----------------------------------------------------------------
Dim cHH As Integer
Dim cMM As Integer
' / -----------------------------------------------------------------
' หลักการของการหาค่าความแตกต่างของเวลา
' ใช้การหาค่าความแตกต่างในระดับจำนวนของวินาทีทั้งหมดออกมาก่อน
' แล้วค่อยคิดตัดออกจากจำนวนชั่วโมง นาที และ วินาที ที่ต่างกัน
' 1 ชั่วโมง เท่ากับ 3600 วินาที
' 1 นาที เท่ากับ 60 วินาที
Dim SecInMinute As Integer
Dim SecInHour As Integer
SecInMinute = 60 ' จำนวน 1 นาทีเท่ากับ 60 วินาที
SecInHour = 3600 ' มาจากจำนวน 60 นาทีใน 1 ชั่วโมง คูณเข้ากับ 60 วินาทีในแต่ละนาที ดังนั้น 1 ชั่วโมง = 3600 วินาที
' / -----------------------------------------------------------------
' หาจำนวนชั่วโมง (Hour) ... หารตัดเศษ ( \ ) ทิ้งไปเลย ... ทำงานได้เร็วกว่าการหารเอาเศษ ( / )
' การกำหนดชนิดตัวแปรแบบเลขจำนวนเต็ม (Integer หรือ Long) มันไม่มีเลขทศนิยมอยู่แล้วครับ
' หาความแตกต่างของจำนวนชั่วโมง โดยนับเป็นวินาที จากนั้นให้หารตัดเศษด้วย (จำนวนนาที x จำนวนวินาที)
' นั่นคือ ค่าที่ได้ \ (60 นาที x 60 วินาที) ... 1 ชั่วโมงก็จะเท่ากับ 3600 วินาที
cHH = DateDiff("s", StartTime, EndTime) \ SecInHour
' หาจำนวนนาที (Minute) ...ไล่เครื่องหมายวงเล็บให้ถูกด้วยน่ะครับ
' การใส่วงเล็บ จะมีความสำคัญที่สูงกว่าเครื่องหมาย + - * / ... อย่าลืมเด็ดขาด
' ความแตกต่างของจำนวนนาที ค่าที่ได้ให้ลบออกจากจำนวนวินาที
' จำนวนวินาทีที่ได้ ให้ลบออกจากเวลาจำนวนชั่วโมงที่ต่างกันก่อน (ชั่วโมง x วินาที)
' แล้วค่อยหารตัดเศษด้วย 60 ... เพราะ 1 นาที = 60 วินาที
cMM = (DateDiff("s", StartTime, EndTime) - (cHH * SecInHour)) \ SecInMinute
' คืนค่ากลับให้ฟังค์ชั่น โดยเอาจำนวนชั่วโมงคูณ 60 นาที รวมเข้ากับนาทีที่ไม่เกิน 1 ชั่วโมง
CalTime = (cHH * 60) + cMM
End Function
Conclusion: การศึกษาเรียนรู้ด้วยภาษา BASIC ภาษาเดียว แต่ขอให้เชี่ยวชาญเถิดจะเกิดผล ...
ดาวน์โหลดไฟล์และโค้ดต้นฉบับแบบเต็ม VBA บน Excel ได้ที่นี่
หน้า:
[1]