Trang này hướng dẫn bạn cách tạo một máy tính khoản vay đơn giản trong Excel VBA. Trang tính chứa các điều khiển ActiveX sau: hai thanh cuộn và hai nút tùy chọn.

Loan Calculator in Excel VBA

Lưu ý: hướng dẫn bên dưới không dạy bạn cách định dạng trang tính. Chúng tôi giả định rằng bạn biết cách thay đổi loại phông chữ, chèn hàng và cột, thêm đường viền, thay đổi màu nền, v.v.

Thực hiện các bước sau để tạo máy tính khoản vay:

  1. Thêm hai điều khiển thanh cuộn. Nhấp vào Chèn từ tab Nhà phát triển và sau đó nhấp vào Thanh cuộn trong phần Điều khiển ActiveX.

Create Scroll Bars

  1. Thêm hai nút tùy chọn. Nhấp vào Chèn từ tab Nhà phát triển và sau đó nhấp vào Nút Tùy chọn trong phần Điều khiển ActiveX.

Create Option Buttons

Thay đổi các thuộc tính sau của các điều khiển thanh cuộn (đảm bảo Chế độ thiết kế được chọn).

  1. Nhấp chuột phải vào điều khiển thanh cuộn đầu tiên, sau đó nhấp vào Thuộc tính. Đặt Min thành 0, Max thành 20, SmallChange thành 0 và LargeChange thành 2.

Giải thích: khi bạn nhấp vào mũi tên, giá trị 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ị thanh cuộn sẽ lên hoặc xuống theo LargeChange.

Tạo sự kiện thay đổi trang tính. Mã được thêm vào Sự kiện Thay đổi Trang tính sẽ được Excel VBA thực thi khi bạn thay đổi một ô trên trang tính.

  1. Mở Trình soạn thảo Visual Basic.

  2. Nhấp đúp vào Trang tính1 (Sheet1) trong Trình khám phá dự án.

  3. Chọn Trang tính từ danh sách thả xuống bên trái và chọn Thay đổi từ danh sách thả xuống bên phải.

Worksheet Change Event in Excel VBA

  1. Sự kiện Thay đổi Trang tính lắng nghe tất cả các thay đổi trên Trang tính1. Chúng tôi chỉ muốn Excel VBA chạy tính toán phụ nếu có gì đó thay đổi trong ô D4.

Để đạt được điều này, hãy thêm dòng mã sau vào Sự kiện Thay đổi Trang tính (thêm về phần Tính toán sau này).

If Target.Address = "$D$4" Then Application.Run "Calculate"

  1. Lấy phần trăm phù hợp trong ô F6 (thay đổi định dạng của ô F6 thành phần trăm). Bấm chuột phải vào điều khiển thanh cuộn đầu tiên, sau đó bấm vào Mã Chế độ xem. Thêm các dòng mã sau:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100

Application.Run "Calculate"

End Sub

  1. Bấm chuột phải vào điều khiển thanh cuộn thứ hai, rồi bấm vào Mã Chế độ xem. Thêm dòng mã sau:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

  1. Nhấp chuột phải vào nút điều khiển tùy chọn đầu tiên, sau đó nhấp vào Mã Chế độ xem. Thêm dòng mã sau:

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then Range("C12").Value = "Monthly Payment"

Application.Run "Calculate"

End Sub

  1. Nhấp chuột phải vào nút điều khiển tùy chọn thứ hai, sau đó nhấp vào Mã Chế độ xem. Thêm dòng mã sau:

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then Range("C12").Value = "Yearly Payment"

Application.Run "Calculate"

End Sub

  1. Đến lúc tạo sub. Bạn có thể xem qua chương Chức năng và phụ của chúng tôi để tìm hiểu thêm về đăng ký. Nếu bạn đang vội, chỉ cần đặt con có tên là Tính toán vào một mô-đun (Trong Trình soạn thảo Visual Basic, bấm Chèn, Mô-đun).

Sub Calculate()

Dim loan As Long, rate As Double, nper As Integer

loan = Range("D4").Value

rate = Range("F6").Value

nper = Range("F8").Value

If Sheet1.OptionButton1.Value = True Then

rate = rate / 12

nper = nper  12

End If

Range("D12").Value = -1  WorksheetFunction.Pmt(rate, nper, loan)

End Sub

Giải thích: sub lấy các tham số phù hợp với hàm Pmt của bảng tính. Hàm PMT trong Excel tính toán các khoản thanh toán cho một 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.OptionButton1.Value = True), Excel VBA sử dụng tỷ lệ / 12 cho tỷ lệ và nper * 12 cho nper (tổng số lần thanh toán). Kết quả là một số âm, 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.

Loan Calculator Result