Bài viết này hướng dẫn các bạn cách tạo một chương trình tính toán khoản vay đơn giản bằng VBA trong Excel. Trang tính excel chứa các điều khiển ActiveX sau: hai thanh cuộn (ScrollBar) và hai nút tùy chọn (OptionButton).
Thực hiện các bước sau để tạo bảng tính toán khoản vay:
Thêm hai điều khiển thanh cuộn (ScrollBar). Nhấn vào tab Developer (Nhà phát triển), Bấm vào Insert (Chèn) và sau đó bấm vào Thanh cuộn (ScrollBar) trong phần Điều khiển ActiveX.
Thay đổi các thuộc tính sau của điều khiển thanh cuộn (đảm bảo đã chọn Chế độ thiết kế).
Nhấp chuột phải vào điều khiển thanh cuộn đầu tiên (ScrollBar), sau đó nhấp vào Thuộc tính (Properties). Đặt Min thành 0, Max thành 20, SmallChange thành 0 và LargeChange thành 2. Đổi thuộc tính Name của điều khiển thanh cuộn thành ScrBar1.
Nhấp chuột phải vào điều khiển thanh cuộn thứ hai (ScrollBar), sau đó nhấp vào Thuộc tính (Properties). Đặt Min thành 5, Max thành 30, SmallChange thành 1, LargeChange thành 5 và LinkedCell thành G10. Đổi thuộc tính Name của điều khiển thanh cuộn thành ScrBar2.
Ta có thể giải thích như sau: Khi bạn bấm vào mũi tên, giá trị của thanh cuộn sẽ tăng hoặc giảm theo SmallChange. Khi bạn nhấp vào giữa thanh trượt và mũi tên, giá trị của thanh cuộn sẽ tăng hoặc giảm theo LargeChange.
Thêm hai nút tùy chọn (OptionButton). Nhấn vào tab Developer (Nhà phát triển), Bấm vào Insert (Chèn) và sau đó bấm vào Nút Tùy chọn (OptionButton) trong phần Điều khiển ActiveX.
Thay đổi các thuộc tính Name của điều khiển nút tùy chọn (OptionButton). Nhấp chuột phải vào điều khiển nút tùy chọn (OptionButton), sau đó nhấp vào Thuộc tính (Properties). Đổi thuộc tính Name của điều khiển nút tùy chọn (OptionButton) lần lượt thành Opt1 và Opt2.
Tiếp theo bạn hãy định dạng bảng tính. Bạn cần thay đổi kiểu phông chữ, chèn hàng và cột, thêm đường viền, thay đổi màu nền, v.v.
Kết quả ta có Bảng tính khoản vay đơn giản bằng VBA trong Excel như hình ảnh dưới đây:
Tiếp theo bạn cần tạo một sự kiện thay đổi trang tính. Mã được thêm vào Sự kiện thay đổi trang tính sẽ được thực thi bởi Excel VBA khi bạn thay đổi một ô trên trang tính.
Mở Trình soạn thảo Visual Basic. Nhấp đúp vào Trang tính 1 (Sheet1) trong Project Explorer. Chọn Worksheet từ danh sách thả xuống bên trái và chọn Change từ danh sách thả xuống bên phải.
Sự kiện Worksheet Change xảy ra khi có sự thay đổi trên Sheet1. Ở đây, Chúng ta chỉ muốn Excel VBA chạy thủ tục “Tinh_Khoan_Vay” nếu có gì đó thay đổi trong ô F6. Để đạt được điều này, hãy thêm đoạn mã sau vào Sự kiện thay đổi trang tính (Worksheet_Change):
Private Sub Worksheet_Change(Byval Target as Range)
If Target.Address = "$F$6" Then
Call Tinh_Khoan_Vay
End if
End Sub
Tiếp theo, Bạn hãy lấy đúng tỷ lệ phần trăm trong ô G8 (thay đổi định dạng của ô G8 thành phần trăm). Nhấp chuột phải vào điều khiển thanh cuộn đầu tiên (ScrollBar), sau đó nhấp vào View code (Xem mã). Thêm đoạn mã sau:
Private Sub ScrBar1_Change()
Range("G8").Value = ScrBar1.Value / 100
Call Tinh_Khoan_Vay
End Sub
Nhấp chuột phải vào điều khiển thanh cuộn thứ hai (ScrollBar), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:
Private Sub ScrBar2_Change()
Call Tinh_Khoan_Vay
End Sub
Nhấp chuột phải vào điều khiển nút tùy chọn đầu tiên (OptionButton), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:
Private Sub Opt1_Click()
If Opt1.Value = True Then
Range("E14").Value = "Thanh toan hang thang"
End if
Call Tinh_Khoan_Vay
End Sub
Nhấp chuột phải vào điều khiển nút tùy chọn thứ hai (OptionButton), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:
Private Sub Opt2_Click()
If Opt2.Value = True Then
Range("E14").Value = "Thanh toan hang nam"
End if
Call Tinh_Khoan_Vay
End Sub
Cuối cùng, bạn cần thêm một thủ tục để tính khoản vay, bạn có thể đặt thủ tục có tên là “Tinh_Khoan_Vay” vào một mô-đun. Trong cửa sổ soạn thảo mã Visual Basic Editor, bạn nhấp vào Insert (Chèn), rồi nhấp vào Module (Mô-đun). Sau đó thêm đoạn mã sau:
Sub Tinh_Khoan_Vay()
Dim khoan_vay As Long, lai_suat As Double, tg_vay As Integer
khoan_vay = Range("F6").Value
lai_suat = Range("G8").Value
tg_vay = Range("G10").Value
If Sheet1.Opt1.Value = True Then
lai_suat = lai_suat / 12
tg_vay = tg_vay * 12
End If
Range("F14").Value = -1 * WorksheetFunction.Pmt(lai_suat, tg_vay, khoan_vay)
End Sub
Giải tích mã:
Thủ tục nhận đúng tham số cho hàm trang tính Pmt. Hàm PMT trong Excel tính toán các khoản thanh toán cho khoản vay dựa trên các khoản thanh toán không đổi và lãi suất không đổi. Nếu bạn thực hiện thanh toán hàng tháng (Sheet1.Opt1.Value = True), Excel VBA sử dụng lãi suất/ 12 cho lãi suất và tg_vay *12 cho tg_vay (tổng số lần thanh toán). Kết quả là một số âm, bởi vì các khoản thanh toán được coi là một khoản ghi nợ. Nhân kết quả với -1 sẽ cho kết quả dương.
Bây giờ bạn hãy lưu lại các đoạn mã trên, rồi quay trở lại trang tính. Bạn nhập một số tiền vào ô F6 sẽ kích hoạt sự kiện Worksheet_Change(), bạn điều chỉnh lãi suất, thời gian vay bằng cách rê kéo hoặc nhấn mũi tên từ điều khiển thanh cuộn ScrollBar. Sau đó bạn lựa chọn hình thức thanh toán bằng cách nhấn vào một trong hai nút tùy chỉnh OptionButton. Bây giờ kết quả sẽ hiển thị như hình dưới đây:
