[VBA] การคำนวณหาเวลาการเข้ามาทำงานของพนักงาน (Time Attendance)

thongkorn

โค้ดนี้นอกจากจะเป็นการนำเข้าข้อมูล (Import) จาก Text File ให้มาปรากฏอยู่ในเวิร์คชีตของ MS Excel ได้แล้ว  แอดมินยังใช้ข้อมูลการเข้ามาทำงานของพนักงานจากเครื่องอ่านลายนิ้วมือ (Finger Scan) พร้อมกับแทรกฟังค์ชั่นของการคำนวณหาค่าความต่างของเวลาเอาไว้ให้ด้วย เพื่อจะได้รู้ว่าพนักงานคนนั้นมาเร็ว มาช้ากว่าเวลาเข้างานปกติ 08:00 ไปกี่นาที สาระสำคัญของบทความนี้ที่อยากจะให้เห็นและเข้าใจมากที่สุดก็คือ ประโยชน์จากการใช้งาน Visual Basic for Application หรือ VBA สำหรับงานเล็กๆน้อยๆภายในสำนักงานด้วยครับ ...



Visual Basic for Application หรือ VBA ลักษณะโค้ดจะเหมือนกับ Visual Basic 6 แต่ไม่ใช่ทั้งหมด


ตัวอย่างข้อมูลจาก Text File


การ Browse เพื่อเลือกไฟล์ จะอาศัย Common Dialog Control เหมือนกับใน VB6 เข้ามาช่วย


Design Time ...

มาดูโค้ดในส่วนที่สำคัญ ... การเลือกเท็กซ์ไฟล์ซึ่งเป็นข้อมูลเข้ามา

1. ' / -----------------------------------------------------------------
   
2. ' / เลือกไฟล์ที่ต้องการ
   
3. Sub OpenDialogFile()
   
4. ' / -----------------------------------------------------------------
   
5.     On Error GoTo HandleError
   
6.    
   
7.     ' ตั้งค่าการเปิดไฟล์ด้วย MS Common Dialog Control
   
8.     With cdl
   
9.         ' กำหนดเฉพาะให้เลือกเฉพาะนามสกุล txt
   
10.         .Filter = "Text files (*.txt)|*.txt"
    
11.         .CancelError = True
    
12.         .DialogTitle = "เลือกไฟล์เอกสาร (Text File) ที่ต้องการ"
    
13.         ' กำหนดโฟลเดอร์เริ่มต้นในการเลือกไฟล์
    
14.         .InitDir = ThisWorkbook.Path
    
15.         ' แสดง Dialog
    
16.         .ShowOpen
    
17.     End With
    
18.     ' ส่งค่าชื่อไฟล์+ตำแหน่งไฟล์ให้กับ FName
    
19.     FName = cdl.Filename
    
20. 
    
21. ExitSub:
    
22.     Exit Sub
    
23. 
    
24. ' Trap Error ที่เราอาจจะไม่คาดคิด
    
25. HandleError:
    
26.     Select Case Err.Number
    
27.         ' กด Escape เพื่อยกเลิกการเลือกไฟล์
    
28.         Case cdlCancel
    
29.             ' Cancelled!
    
30.             Resume ExitSub
    
31.         Case Else
    
32.             MsgBox "Error: " & Err.Description & "(" & Err.Number & ")"
    
33.     End Select
    
34. End Sub

คัดลอกไปที่คลิปบอร์ด

โค้ดในส่วนของการโหลดข้อมูลจาก Text File เข้าสู่ WorkSheet ของ Excel

1. ' / -----------------------------------------------------------------
   
2. ' / นำข้อมูลจาก Text File เข้ามายัง MS Excel
   
3. Sub ImportData()
   
4. ' / -----------------------------------------------------------------
   
5.     ' หากไม่มีการเลือกไฟล์ใดๆเข้ามาเลย ก็ออกจากโปรแกรมย่อย
   
6.     If FName = "" Then Exit Sub
   
7.    
   
8.     ' ปิดไฟล์เดิมก่อน
   
9.     Close #1
   
10.     Open FName For Input As #1
    
11.     ' ตัวแปรรับค่าข้อมูล Text File เข้ามาทีละบรรทัด
    
12.     Dim strData As String
    
13.    
    
14.     ' แยกข้อมูลออกจากกันด้วย Array
    
15.     Dim iArr() As String
    
16.    
    
17.     ' นับจำนวนแถว
    
18.     Dim i As Long
    
19.     i = 1
    
20.     ' อ่านไฟล์เข้ามา และทำการอ่านทีละบรรทัด ทำจนกว่าจะหมดข้อมูล (EOF - End Of File)
    
21.     ' การใช้ลูป Do While หรือ Do Until จะได้ไม่ต้องนับจำนวนแถวเข้ามาก่อน
    
22.     Do While (Not EOF(1))
    
23.         ' อ่านเข้ามาทีละบรรทัด
    
24.         Line Input #1, strData
    
25.         ' แยกข้อมูลออกจากกันด้วยเครื่องหมายช่องว่าง - Space
    
26.         iArr = Split(strData, " ")
    
27.         ' เนื่องจาก i เริ่มจาก 1 แต่เวลานำข้อมูลเข้าไปเก็บจะเริ่มต้นจากแถวที่ 3
    
28.         ' ดังนั้นต้องให้ i + 2 = 3 แต่ i ไม่ได้เปลี่ยนค่าน่ะครับ
    
29.         Cells(i + 2, 1) = i
    
30.         ' แสดงรหัสพนักงาน
    
31.         Cells(i + 2, 2) = iArr(0)
    
32.         ' แสดงวันที่
    
33.         Cells(i + 2, 3) = iArr(1)
    
34.         ' แสดงเวลา
    
35.         Cells(i + 2, 4) = iArr(2)
    
36.         ' คำนวณเวลาความต่างว่ามาช้ามาเร็วกี่นาที โดยเทียบกับเวลา 8.00 น.
    
37.         Cells(i + 2, 5) = CalTime("08:00:00", Format(iArr(2), "HH:MM:SS"))
    
38.         ' หากมาสายเกิน 8.00 น. ให้แสดงตัวอักษรสีแดง
    
39.         If Cells(i + 2, 5) > 0 Then
    
40.             Cells(i + 2, 5).Font.Color = RGB(255, 0, 0)
    
41.         ' หากปกติก็ให้เป็นอักษรสีดำ
    
42.         Else
    
43.             Cells(i + 2, 5).Font.Color = RGB(0, 0, 0)
    
44.         End If
    
45.         ' เรียกแถวใหม่
    
46.         i = i + 1
    
47.     Loop
    
48.     ' ปิดไฟล์
    
49.     Close #1
    
50. End Sub

คัดลอกไปที่คลิปบอร์ด

การแยกข้อมูลออกจากกันในแต่ละหลัก จะใช้เครื่องหมายช่องว่าง (Space) หรือ ASCII Code = 32 (ฐาน 10)

ฟังค์ชั่นในการคำนวณหาความแตกต่างกันของเวลา ... คัดลอกมาจาก Visual Basic 6 ...

1. ' / -----------------------------------------------------------------
   
2. ' / ฟังค์ชั่นคำนวณหาค่าความต่างเวลาเป็นนาที
   
3. Public Function CalTime(StartTime As Date, EndTime As Date) As Integer
   
4. ' / -----------------------------------------------------------------
   
5. 
   
6.     Dim cHH As Integer
   
7.     Dim cMM As Integer
   
8.    
   
9.     ' / -----------------------------------------------------------------
   
10.     ' หลักการของการหาค่าความแตกต่างของเวลา
    
11.     ' ใช้การหาค่าความแตกต่างในระดับจำนวนของวินาทีทั้งหมดออกมาก่อน
    
12.     ' แล้วค่อยคิดตัดออกจากจำนวนชั่วโมง นาที และ วินาที ที่ต่างกัน
    
13.     ' 1 ชั่วโมง เท่ากับ 3600 วินาที
    
14.     ' 1 นาที เท่ากับ 60 วินาที
    
15.     Dim SecInMinute As Integer
    
16.     Dim SecInHour As Integer
    
17.     SecInMinute = 60    ' จำนวน 1 นาทีเท่ากับ 60 วินาที
    
18.     SecInHour = 3600    ' มาจากจำนวน 60 นาทีใน 1 ชั่วโมง คูณเข้ากับ 60 วินาทีในแต่ละนาที ดังนั้น 1 ชั่วโมง = 3600 วินาที
    
19.     ' / -----------------------------------------------------------------
    
20.    
    
21.     ' หาจำนวนชั่วโมง (Hour) ... หารตัดเศษ ( \ ) ทิ้งไปเลย ... ทำงานได้เร็วกว่าการหารเอาเศษ ( / )
    
22.     ' การกำหนดชนิดตัวแปรแบบเลขจำนวนเต็ม (Integer หรือ Long) มันไม่มีเลขทศนิยมอยู่แล้วครับ
    
23.     ' หาความแตกต่างของจำนวนชั่วโมง โดยนับเป็นวินาที จากนั้นให้หารตัดเศษด้วย (จำนวนนาที x จำนวนวินาที)
    
24.     ' นั่นคือ ค่าที่ได้ \ (60 นาที x 60 วินาที) ... 1 ชั่วโมงก็จะเท่ากับ 3600 วินาที
    
25.     cHH = DateDiff("s", StartTime, EndTime) \ SecInHour
    
26.    
    
27.     ' หาจำนวนนาที (Minute) ...  ไล่เครื่องหมายวงเล็บให้ถูกด้วยน่ะครับ
    
28.     ' การใส่วงเล็บ จะมีความสำคัญที่สูงกว่าเครื่องหมาย + - * / ... อย่าลืมเด็ดขาด
    
29.     ' ความแตกต่างของจำนวนนาที ค่าที่ได้ให้ลบออกจากจำนวนวินาที
    
30.     ' จำนวนวินาทีที่ได้ ให้ลบออกจากเวลาจำนวนชั่วโมงที่ต่างกันก่อน (ชั่วโมง x วินาที)
    
31.     ' แล้วค่อยหารตัดเศษด้วย 60 ... เพราะ 1 นาที = 60 วินาที
    
32.     cMM = (DateDiff("s", StartTime, EndTime) - (cHH * SecInHour)) \ SecInMinute
    
33.    
    
34.     ' คืนค่ากลับให้ฟังค์ชั่น โดยเอาจำนวนชั่วโมงคูณ 60 นาที รวมเข้ากับนาทีที่ไม่เกิน 1 ชั่วโมง
    
35.     CalTime = (cHH * 60) + cMM
    
36. End Function

คัดลอกไปที่คลิปบอร์ด

Conclusion: การศึกษาเรียนรู้ด้วยภาษา BASIC ภาษาเดียว แต่ขอให้เชี่ยวชาญเถิดจะเกิดผล ...


ดาวน์โหลดไฟล์และโค้ดต้นฉบับแบบเต็ม VBA บน Excel ได้ที่นี่