Sáng kiến kinh nghiệm Ứng dụng Visual Basic Applications (VBA) trong Excel trong công tác lập bảng kê thanh toán, theo dõi chi phí phục vụ GPMB (2%)

ỦY BAN NHÂN DÂN TỈNH BÌNH ĐỊNH
BAN GIẢI PHÓNG MẶT BẰNG 
BÁO CÁO
KẾT QUẢ THỰC HIỆN SÁNG KIẾN KINH NGHIỆM NĂM 2021
 Tên sáng kiến: Ứng dụng Visual Basic Applications (VBA) trong Excel trong công tác lập bảng kê thanh toán, theo dõi chi phí phục vụ GPMB (2%)
Chủ trì thực hiện: Đặng Sơn Bá
Chức vụ: Nhân viên phòng Hành chính – Tổng hợp
Đơn vị công tác: Ban Giải phóng mặt bằng tỉnh
Bình Định, ngày . tháng . năm 2021

CỘNG HÒA XÃ HỘI CHỦ NGHIÃ VIỆT NAM
Độc lập – Tự do – Hạnh phúc
	 Bình Định, ngày .... tháng . năm 2021
BÁO CÁO
Kết quả thực hiện sáng kiến năm 2021
Tên sáng kiến: Ứng dụng Visual Basic Applications (VBA) trong Excel trong công tác lập bảng kê thanh toán, theo dõi chi phí phục vụ GPMB (2%)
Đặt vấn đề
Thời gian gần đây lĩnh vực Tin học đã có nhiều tiến bộ vượt bậc, tác động sâu xa đến mọi lĩnh vực hoạt động của con người. Trong đó có cả lĩnh vực Kế toán, việc ứng dụng công nghệ thông tin (CNTT) đã trở nên phổ biến trong hầu hết các cơ quan, đơn vị, đặc biệt là việc áp dụng phần mềm Microsoft Excel là thông dụng nhất trong công tác thanh toán chi phí phục vụ công tác GPMB (2%). 
Công tác theo dõi lập dự toán và thanh toán chi phí 2% của các dự án không khó tuy nhiên số lượng chứng từ nhiều; mục chi trùng lắp. Một dự án có rất nhiều phương án bồi thường, vì vậy việc theo dõi trên 30 dự án, việc thanh toán chi phí theo từng quyết định phê duyệt khoảng hơn 100 dự toán, nhằm thanh toán kịp thời, nhanh chóng và chính xác số liệu đồng thời theo dõi lũy kế từng dự toán của các quyết định của dự án không bị vượt dự toán hoặc sai sót số liệu của các mục chi trong dự toán. Do đó, để việc lập bảng kê thanh toán 2% “tự động hóa” hơn, “chuyên nghiệp” hơn tôi đã kết hợp với công cụ nhúng trong Excel là VBA.
VBA là viết tắt của Visual Basic for Application, là ứng dụng lập trình cơ bản trong Microsoft Office. Trong Microsoft Excel , VBA giúp tự động hóa các công việc và cung cấp cho người dùng những hàm tùy biến. Chức năng ghi lại những đoạn Macro có thể tạo ra những đoạn mã VBA cho những tính chất lặp lại của người sử dụng, cho phép những thao tác thông dụng được tự động hóa, cho phép tạo ra bảng biểu và điều kiện bên trong bảng tính để trực tiếp giao tiếp với người sử dụng.
Trong quá trình làm việc trên Microsoft Excel, đặc biệt là trong công tác lập bảng kê thanh toán, theo dõi chi phí 2% các quyết định của từng dự án thường lặp đi lặp lại các thao tác bằng thủ công nhiều và tốn nhiều thời gian.
 Từ thực tế trên, với những kiến thức được học và tìm hiểu thêm trên mạng internet, tôi đã viết đề tài “Ứng dụng Visual Basic Applications trong Excel trong công tác lập bảng kê thanh toán, theo dõi chi phí phục vụ GPMB (2%)”.
2. Mục tiêu
- Tiết kiệm thời gian trong việc lập bảng thanh toán chi phí 2%.
- Theo dõi mục chi dự toán chi phí 2% các quyết định của dự án.
	- Báo cáo số liệu kịp thời, nhanh chóng.
3. Cách thức thực hiện
3.1 Cách mở VBA đối với các phiên bản Excel 2010
Bắt đầu, bạn chọn File và chọn tính năng Options như sau:
Khi cửa sổ Excel Options hiển thị, chọn mục Customize Ribbon, sau đó tích vào mục Developer như hình vẽ dưới:
 	Nhấn lệnh OK, sau đó sẽ thấy tab Developer trên thanh công cụ, nhấn vào đó sẽ thấy các tùy chọn thích hợp để viết Macro hay các yêu cầu khác trong VBA.
3.2 Sử dụng mã Macro trong Excel
Di chuyển đến developer tab và nhấp chọn “Visual Basic”.
Phía bên trái trên “Project Window”, nhấp chuột phải vào tên và chèn vào một module mới.
Dán mã code vào trong module đó và đóng lại.
Bây giờ, di chuyển đến developer tab và nhấp chuột vào Macro.
Macro sẽ hiển thị một cửa sổ danh sách các macro à chọn macro cần chạy à nhấn Run.
3.3 Cách thực hiện
Áp dụng thực hiện cho tất cả các quyết định của dự án.
Ví dụ: Thanh toán tiền làm thêm giờ T6/2021 (05 dự án): 
Chi tiết lập bảng kê thanh toán chi phí 2% Quyết định 2294/QĐ- UBND ngày 09/6/2021 của UBND tỉnh Bình Định dự án: Khu tái định cư - dân cư Hiệp Vinh 1A tại Khu Công nghiệp - Đô thị Becamex A (Phân khu 7) tại xã Canh Vinh, huyện Vân Canh, Khu kinh tế Nhơn Hội.
	Mẫu bảng kê thanh toán Quyết định 2294/QĐ- UBND:
Bước 1: Tự động định dạng phông chữ, kẻ khung
Viết đoạn lệnh sau:
Sub khung_bk()
 'dinh dang font
 Selection.Font.Bold = False
 Selection.Font.Italic = False
 'tao khung
 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
 Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 With Selection.Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlThin
 End With
 With Selection.Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlThin
 End With
 With Selection.Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlThin
 End With
 With Selection.Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlThin
 End With
 With Selection.Borders(xlInsideVertical)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlThin
 End With
 With Selection.Borders(xlInsideHorizontal)
 .LineStyle = xlContinuous
 .ColorIndex = 0
 .TintAndShade = 0
 .Weight = xlHairline
 End With
End Sub
Diễn giải:
Nhập nội dung cần thanh toán vào bảng kê, sau đó chọn vùng dữ liệu cần chỉnh à bấm chạy đoạn code trên. Kết quả tự động:
Chuyển về Font chữ: Times New Roman (khi bị lỗi định dạng).
Kẻ khung vùng dữ liệu đã chọn.
Bước 2: Tự động ẩn hàng không chứa số liệu, thiết lập vùng in và in
Viết đoạn lệnh sau:
Sub Vung_in2()
 Dim LR As Integer
 Dim i As Integer
 LR = Range("K" & Rows.Count).End(xlUp).Row
 For i = 15 To LR - 4
 'font mau trang: RGB(255, 255, 255)
 If Range("K" & i).Font.Color = RGB(255, 255, 255) Or _
 Range("K" & i).Value = 0 Then
 Rows(i).EntireRow.Hidden = True
 End If
 Next i
 Sheets(2).PageSetup.PrintArea = _
 Sheets(2).Range("Print_Area").Address
 ActiveWindow.SelectedSheets.PrintOut copies:=2
 ActiveWorkbook.Save
End Sub
Diễn giải:
Trước khi in, ẩn những hàng mà tại cột [Thành tiền] chứa ô có giá trị bằng 0, ta viết đoạn lệnh thỏa mãn điều kiện: nếu những giá trị tại cột [Thành tiền] là trống hoặc có giá trị mà Font chữ màu trắng thì ẩn những hàng đó. Đồng thời thiết lập vùng in và in à Bấm chạy đoạn code trên. Kết quả tự động:
Ẩn những hàng tại mục chi [1]; [1.1]; [1.2] khi cột [Thành tiền] không chứa số liệu.
Chọn vùng muốn in và in thành 2 bộ hồ sơ.
. Bước 3: Chèn cột theo dõi lũy kế chứng từ đã thanh toán
Viết đoạn lệnh sau:
Sub THEM_COT()
 Dim i As Integer
 i = Cells(14, Columns.Count).End(xlToLeft).Column
 Dim LR As Integer
 LR = Range("K" & Rows.Count).End(xlUp).Row
 If i > 21 Then
 Cells(1, i - 5).Select
 Selection.EntireColumn.Hidden = True
 End If
 Range("K14:K" & LR - 3).Copy
 Cells(14, i - 2).Select
 Selection.Insert Shift:=xlToRight
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "=+TODAY()"
 ActiveCell.NumberFormat = "dd/mm/yy"
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Selection.Font.Bold = False
 Columns(i).EntireColumn.AutoFit
 Columns(i + 1).EntireColumn.AutoFit
 Columns(i - 1).EntireColumn.AutoFit
 Columns(i - 2).EntireColumn.AutoFit
 Range("a13").Select
End Sub
Diễn giải:
Sau khi lập hồ sơ thanh toán xong, ta chuyển dữ liệu từ cột [Thành tiền] sang cột theo dõi lũy kế thanh toán, đồng thời nhập ngày thanh toán.
Bấm chạy đoạn code trên. Kết quả tự động:
Chuyển dữ liệu từ cột [Thành tiền] đến trước ô [2021] sau mỗi lần thanh toán.
	- 	Sử dụng hàm TODAY() để thể hiện ngày thanh toán.
Bước 4: Tự động tạo mới bảng kê thanh toán
Viết đoạn lệnh sau:
Sub xoa_in_dam_va_boi_trang()
 Dim LR As Integer
 Dim i As Integer
 LR = Range("K" & Rows.Count).End(xlUp).Row
 For i = 15 To LR - 4
 If Range("K" & i).Font.Bold = False Then
 With Range("K" & i).Font
 .ThemeColor = xlThemeColorDark1 'mau trang
 .TintAndShade = 0
 End With
 ElseIf Range("K" & i).Font.Italic = True Then
 Range("K" & i).ClearContents
 End If
 Next i
 'xoa muc in dam khong co cong thuc
 For i = 15 To LR - 4
 If Range("K" & i).Font.Bold = True And Range("K" & i).Value > 0 Then
 Range("K" & i).ClearContents 
 End If
 Next i
End Sub
Diễn giải:
Sau khi hoàn thành việc chuyển dữ liệu từ cột [Thành tiền] sang cột theo dõi lũy kế thanh toán, tiến hành xóa dữ liệu các mục chi, đồng thời chuyển dữ liệu của nội dung thanh toán về Font chữ màu trắng. ta viết đoạn lệnh thỏa mãn 2 điều kiện: 
- 	Điều kiện 1: nếu những ô tại cột [Thành tiền] không phải Font chữ in đậm thì ô đó chuyển sang Font chữ màu trắng. Ngược lại nếu ô đó là Font chữ in đậm và in nghiêng thì xóa dữ liệu ô đó.
- 	Điều kiện 2: nếu những ô tại cột [Thành tiền] là Font chữ in đậm và giá trị lớn hơn 0 thì xóa dữ liệu ô đó.
Bấm chạy đoạn code trên. Kết quả tự động:
Xóa giá trị tại mục [2]; [2.2].
Giá trị tại nội dung thanh toán chuyển sang Font chữ màu trắng.
Bước 5: Tự động mở ẩn các mục chi và ẩn chứng từ thanh toán; lưu và đóng file
Viết đoạn lệnh sau:
Sub Luu_Thoat()
 Dim LR As Integer
 Dim i As Integer
 LR = Range("K" & Rows.Count).End(xlUp).Row
 If Range("m1").Value "" Then
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Else
 'an hang
 For i = 15 To LR - 4
 If Range("g" & i).Font.Bold = True Then
 Rows(i).Select
 Selection.EntireRow.Hidden = False
 Else
 Rows(i).Select
 Selection.EntireRow.Hidden = True
 End If
 Next i
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 End If
End Sub
Diễn giải:
Để mở ẩn các mục chi và ẩn các chứng từ đã thanh toán để chuẩn bị cho lần thanh toán tiếp theo, ta viết đoạn lệnh thỏa mãn điều kiện: nếu những ô tại cột [Nội dung chi] là Font chữ in đậm thì mở ẩn hàng chứa ô đó, ngược lại không phải Font chữ in đậm thì ẩn.
Bấm chạy đoạn code trên. Kết quả tự động:
Mở ẩn các mục chi [1]; [1.1]; [1.2]; [2.1]
Ẩn các chứng từ đã thanh toán.
Lưu và đóng file
4. Kết quả thực hiện
Từ việc ứng dụng sáng kiến trên đã đạt được một số hiệu quả nhất định:
- Tiết kiệm thời gian: thời gian là thủ công của việc lập bảng kê thanh toán của 05 dự án cần 30 phút; khi áp dụng ứng dụng VBA chỉ cần 10 phút.
- Số liệu lũy kế thanh toán và số còn lại của các mục chi trong dự toán luôn chính xác.
- Báo cáo số liệu kịp thời, chính xác theo yêu cầu của lãnh đạo;
- Đối chiếu số liệu với kế toán dự án chính xác, không bị sai sót;
Đề nghị Hội đồng xét sáng kiến Ban Giải phóng mặt bằng tỉnh xem xét nghiệm thu và công nhận./.

Người viết báo cáo
Đặng Sơn Bá